A subquery is said to be a correlated subquery if the inner query depends on the outer query for any of its values. Example 6.70 shows a correlated subquery.
Get the last names of all employees who work on project p3:
USE sample;
SELECT emp_lname
FROM employee
WHERE 'p3' IN
(SELECT project_no
FROM works_on
WHERE works_on.emp_no = employee.emp_no);
Code language: PHP (php)
The result is
The inner query in Example 6.70 must be logically evaluated many times because it contains the emp_no column, which belongs to the employee table in the outer query, and the value of the emp_no column changes every time the Database Engine examines a different row of the employee table in the outer query.
Let’s walk through how the system might process the query in Example 6.70. First, the system retrieves the first row of the employee table (for the outer query) and compares the employee number of that column (25348) with values of the works_on.emp_no column in the inner query.
Since the only project_no for this employee is p2, the inner query returns the value p2. The single value in the set is not equal to the constant value p3 in the outer query, so the outer query’s condition (WHERE ‘p3’ IN …) is not met and no rows are returned by the outer query for this employee.
Then, the system retrieves the next row of the employee table and repeats the comparison of employee numbers in both tables. The second employee has two rows in the works_on table with project_no values of p1 and p3, so the result set of the inner query is (p1,p3).
One of the elements in the result set is equal to the constant value p3, so the condition is evaluated to TRUE and the corresponding value of the emp_lname column in the second row (Jones) is displayed. The same process is applied to all rows of the employee table, and the final result set with three rows is retrieved.
More examples of correlated subqueries are shown in the next section.