All previous examples in this chapter contain comparisons of column values with an expression, constant, or set of constants. Additionally, the Transact-SQL language offers the ability to compare column values with the result of another SELECT statement. Such a construct, where one or more SELECT statements are nested in the WHERE clause of another SELECT statement, is called a subquery. The first SELECT statement of a subquery is called the outer query—in contrast to the inner query, which denotes the SELECT statement(s) used in a comparison. The inner query will be evaluated first, and the outer query receives the values of the inner query.
There are two types of subqueries:
- Self-contained
- Correlated
In a self-contained subquery, the inner query is logically evaluated exactly once. A correlated subquery differs from a self-contained one in that its value depends upon a variable from the outer query. Therefore, the inner query of a correlated subquery is logically evaluated each time the system retrieves a new row from the outer query. This section shows examples of self-contained subqueries. The correlated subquery will be discussed later in the chapter, together with the join operation.
A self-contained subquery can be used with the following operators:
- Comparison operators
- IN operator
- ANY or ALL operator
1. Subqueries and Comparison Operators
Example 6.51 shows the self-contained subquery that is used with the operator =.
Example 6.51
Get the first and last names of employees who work in the Research department:
USE sample;
SELECT emp_fname, emp_lname
FROM employee
WHERE dept_no =
(SELECT dept_no
FROM department
WHERE dept_name = ‘Research’);
The result is
The inner query of Example 6.51 is logically evaluated first. That query returns the number of the research department (d1). Thus, after the evaluation of the inner query, the subquery in Example 6.51 can be represented with the following equivalent query:
USE sample
SELECT emp_fname, emp_lname
FROM employee
WHERE dept_no = ‘d1’;
A subquery can be used with other comparison operators, too. Any comparison operator can be used, provided that the inner query returns exactly one row. This is obvious because comparison between particular column values of the outer query and a set of values (as a result of the inner query) is not possible. The following section shows how you can handle the case in which the result of an inner query contains a set of values.
2. Subqueries and the IN Operator
The IN operator allows the specification of a set of expressions (or constants) that are subsequently used for the query search. This operator can be applied to a subquery for the same reason—that is, when the result of an inner query contains a set of values.
Example 6.52 shows the use of the IN operator in a subquery.
Example 6.52
Get full details of all employees whose department is located in Dallas:
USE sample;
SELECT *
FROM employee
WHERE dept_no IN
(SELECT dept_no
FROM department
WHERE location = ‘Dallas’);
The result is
Each inner query may contain further queries. This type of subquery is called a subquery with multiple levels of nesting. The maximum number of inner queries in a subquery depends on the amount of memory the Database Engine has for each SELECT statement. In the case of subqueries with multiple levels of nesting, the system first evaluates the innermost query and returns the result to the query on the next nesting level, and so on. Finally, the outermost query evaluates the final outcome.
Example 6.53 shows the query with multiple levels of nesting.
Example 6.53
Get the last names of all employees who work on the project Apollo:
USE sample;
SELECT emp_lname
FROM employee
WHERE emp_no IN
(SELECT emp_no
FROM works_on
WHERE project_no IN
(SELECT project_no
FROM project
WHERE project_name = ‘Apollo’));
The result is
The innermost query in Example 6.53 evaluates to the project_no value p1. The middle inner query compares this value with all values of the project_no column in the works_on table. The result of this query is the set of employee numbers: (10102, 29346, 9031, 28559). Finally, the outermost query displays the corresponding last names for the selected employee numbers.
3. Subqueries and ANY and ALL Operators
The operators ANY and ALL are always used in combination with one of the comparison operators. The general syntax of both operators is
column_name operator [ANY | ALL] query
where operator stands for a comparison operator and query is an inner query.
The ANY operator evaluates to TRUE if the result of the corresponding inner query contains at least one row that satisfies the comparison. The keyword SOME is the synonym for ANY. Example 6.54 shows the use of the ANY operator.
Example 6.54
Get the employee numbers, project numbers, and job names for employees who have not spent the most time on one of the projects:
USE sample;
SELECT DISTINCT emp_no, project_no, job
FROM works_on
WHERE enter_date > ANY
(SELECT enter_date
FROM works_on);
The result is
Each value of the enter_date column in Example 6.54 is compared with all values of this column. For all dates of the column, except the oldest one, the comparison is evaluated to TRUE at least once. The row with the oldest date does not belong to the result because the comparison does not evaluate to TRUE in any case. In other words, the expression “enter_date > ANY (SELECT enter_date FROM works_on)” is true if there are any (one or more) rows in the works_on table with a value of the enter_date column less than the value of enter_date for the current row. This will be true for all but the earliest value of the enter_date column.
The ALL operator evaluates to TRUE if the evaluation of the table column in the inner query returns all values of that column.
NOTE Do not use ANY and ALL operators! Every query using ANY or ALL can be better formulated with the EXISTS function, which is explained later in this chapter (see the section “Subqueries and the EXISTS Function”). Additionally, the semantic meaning of the ANY operator can be easily confused with the semantic meaning of the ALL operator, and vice versa.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.