EXISTS vs NOT EXISTS - Correlated Nested Query

 

--COR- RELATED NESTED QUERY:

--RAJANI schema

set lines 100

 

select * from emp;

 

       EID ENAME                          ADRRESS                      

---------- ------------------------------ ------------------------------

         1 Ravi                           Chandigarh                   

         2 Varun                          Delhi                         

         3 Nitin                          Pune                         

         4 Ravin                          Bangalore                    

         5 Ammy                           Chandigarh   

 

select * from project;

 

 

       EID PID        PNAME                          LOCATION           

---------- ---------- ------------------------------ --------------------

         1 P1         IOT                            Bangalore          

         5 P2         BIG DATA                       Delhi              

         3 P3         Retail                         Mumbai             

         4 P4         Android                        Hyderabad   

 

COR- RELATED NESTED QUERY:

In Subqueries we need a keyword like in/not in / Any /All operators is used to link between outer and inner query.

 

But in Corrreplated nested query we use  EXISTS / NOT EXISTS keyword to join between Outer and inner queries .

 

 

Q. Find the details of employees who is  working / assigned ( Use Exists) at least any one of the project. 

  

EXISTS Example:

 

select * from emp where

         exists ( select eid from project

                  where emp.eid =project.eid)

 

 

       EID ENAME                          ADRRESS                      

---------- ------------------------------ ------------------------------

         1 Ravi                           Chandigarh                   

         5 Ammy                           Chandigarh                   

         3 Nitin                          Pune                         

         4 Ravin                          Bangalore                    

 

 Steps :

1.  Outer query will execute first .

2.  It will take eid 1,2,3,4,5 from emp table one by one and compare with eid of project table for availability.

3.  Only eid 1,3,4,5 will match/available  in project table.

4. Hence for eid 1,3,4,5 the exists condition will become TRUE and rows will be retrived from emp table.

5. While comparing for eid= 2 from emp table (outer table)  with project table (inner table),

   there is no matching eid presentin project table ,

   so the inner query will return false for eid = 2 from project table.

5. Logically exists keyword will be replaced ( return) 'false' in this case for eid =2.

4. Hence except eid =2, all other records from emp table will be retrieved.

 

NOT EXISTS Example:

 

Q: Find the details of employees who is not working / not assigned ( Use NOT Exists)  any one of the project.  

 

select * from emp where

         not exists ( select eid from project

                  where emp.eid =project.eid)

        EID ENAME                          ADRRESS                      

---------- ------------------------------ ------------------------------

         2 Varun                          Delhi                        

  

 Steps :

1.  Outer query will execute first .

2.  It will take eid 1,2,3,4,5 from emp table one by one and compare with eid of project table for non-availability.

3.  eid 1,3,4,5 will match/available  in project table. So not exists condition become false .

4.  Hence for eid 1,3,4,5 the not exists condition will become FALSE and hence no rows will be retrived from emp table.

5.  While comparing for eid= 2 from emp table (outer table)  with project table (inner table),

   there is no matching  eid presentin project table, so the inner query will return TRUE  for eid = 2 from project table.

6. Logically not exists keyword will be replaced ( return)  'TRUE' in this case for eid =2.

7. Hence only  eid =2  record  from emp table will be retrieved..