Correlated subquery

      Inner query depends on outer query, outer query depends on inner query. Means subquery is executed repeatedly, once for each row that might be selected by the outer query.

Example:

           Employee table

           Department table

           1.

SELECT * FROM EMPLOYEE E

WHERE EXISTS

(

      SELECT 1

      FROM DEPT D

      WHERE D.DEPTID = E.DEPT_ID 

)

    In the above example inner query depends on outer query, outer query depends inner query. Above query displays the all employee details if the 'department id' is matched with 'department table department id'.

           2.

SELECT * FROM EMPLOYEE E

WHERE NOT EXISTS

(

      SELECT 1

      FROM DEPT D

      WHERE D.DEPTID = E.DEPT_ID 

)

    Above query displays all employee details if the department id is not matched with 'department table department id'.