CORRELATED SUBQUERIES

CORRELATED SUBQUERIES

STEPS PERFORMED BY THE CORRELATED SUBQUERY:

   1.Select a Row from the Outer Query
   2.Determine the value of the correlated column(s)
   3.For each record of the outer query, the inner query is executed
   4.The result of the inner query is then fed to the outer query and evaluated.  If it satisfies the criteria, the row is returned for output
   5.The next record of the outer query is selected and steps 2 through 4 are repeated until all the records of the outer query are evaluated

*********************************************************

EXISTS AND NOT EXISTS OPERATORS:

These operators are both used for correlated subqueries.  The exists operator tests if the subquery returns at least one row.  The exists operator returns either TRUE or FALSE, never unknown.  Because the EXISTS operator tests only if a row exists, the columns shown in the SELECT list are irrelevant.  Typically you use a text literal such as '1' or 'X'.

E.g.  Display the names of instructors assigned to at least one section:

                SELECT instructor_id, last_name, first_name, zip

                FROM instructor i

                WHERE EXISTS

                            (SELECT 'X'

                              FROM section

                               WHERE i.instructor_id = instructor_id)



NOT EXISTS: This test is the opposite of EXISTS.  It tests if a matching row cannot be found, i.e. the set of rows that match is empty.

Another correlated subquery:

Determine the employee who receives the highest salary in each department-

SELECT e.deptno, e.lname, e.sal

FROM EMP e

WHERE sal = (SELECT MAX(e2.sal)

                                   FROM EMP e2

                                    WHERE e.deptno=e2.deptno);