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.
Example 6.70
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);
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 pi 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.
1. Subqueries and the EXISTS Function
The EXISTS function takes an inner query as an argument and returns TRUE if the inner query returns one or more rows, and returns FALSE if it returns zero rows. This function will be explained using examples, starting with Example 6.71.
Example 6.71
Get the last names of all employees who work on project pi:
USE sample;
SELECT emp_lname
FROM employee
WHERE EXISTS
(SELECT *
FROM works_on
WHERE employee.emp_no = works_on.emp_no AND project_no = ‘p1’);
The result is
The inner query of the EXISTS function almost always depends on a variable from an outer query. Therefore, the EXISTS function usually specifies a correlated subquery.
Let’s walk through how the Database Engine might process the query in Example 6.71. First, the outer query considers the first row of the employee table (Smith). Next, the EXISTS function is evaluated to determine whether there are any rows in the works_on table whose employee number matches the one from the current row in the outer query, and whose project_no is p1. Because Mr. Smith does not work on the project p1, the result of the inner query is an empty set and the EXISTS function is evaluated to FALSE. Therefore, the employee named Smith does not belong to the final result set. Using this process, all rows of the employee table are tested, and the result set is displayed.
Example 6.72 shows the use of the NOT EXISTS function.
Example 6.72
Get the last names of all employees who work for departments not located in Seattle:
USE sample;
SELECT emp_lname
FROM employee
WHERE NOT EXISTS
(SELECT *
FROM department
WHERE employee.dept_no = department.dept_no
AND location = ‘Seattle’);
The result is
The SELECT list of an outer query involving the EXISTS function is not required to be of the form SELECT * as in the previous examples. The form SELECT column_list, where column_list is one or more columns of the table, is an alternate form. Both forms are equivalent, because the EXISTS function tests only the existence (i.e., nonexistence) of rows in the result set. For this reason, the use of SELECT * in this case is safe.
2. Should You Use Joins or Subqueries?
Almost all SELECT statements that join tables and use the join operator can be rewritten as subqueries, and vice versa. Writing the SELECT statement using the join operator is often easier to read and understand and can also help the Database Engine to find a more efficient strategy for retrieving the appropriate data. However, there are a few problems that can be easier solved using subqueries, and there are others that can be easier solved using joins.
2.1. Subquery Advantages
Subqueries are advantageous over joins when you have to calculate an aggregate value on-the-fly and use it in the outer query for comparison. Example 6.73 shows this.
Example 6.73
Get the employee numbers and enter dates of all employees with enter dates equal to the earliest date:
USE sample;
SELECT emp_no, enter_date
FROM works_on
WHERE enter_date = (SELECT min(enter_date)
FROM works_on);
This problem cannot be solved easily with a join, because you would have to write the aggregate function in the WHERE clause, which is not allowed. (You can solve the problem using two separate queries in relation to the works_on table.)
2.2. Join Advantages
Joins are advantageous over subqueries if the SELECT list in a query contains columns from more than one table. Example 6.74 shows this.
Example 6.74
Get the employee numbers, last names, and jobs for all employees who entered their projects on October 15, 2017:
USE sample;
SELECT employee.emp_no, emp_lname, job
FROM employee, works_on
WHERE employee.emp_no = works_on.emp_no
AND enter_date = ‘10.15.2017’;
The SELECT list of the query in Example 6.74 contains columns emp_no and emp_lname from the employee table and the job column from the works_on table. For this reason, the equivalent solution with the subquery would display an error, because subqueries can display information only from the outer table.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.