A nested query, usually called a subquery, places one SELECT statement inside another SQL statement. The inner query produces a result that the outer query can filter, compare, or join, letting one statement answer a task that might otherwise require multiple queries. Think of the inner result as a temporary, table-like set of rows and columns. section 11.0 section 11.1
Find orders after a customer’s order
To find orders placed after Margaret Montoya’s order, first identify her order date in the inner query. Then use that returned date in the outer WHERE condition. Parentheses mark the inner SELECT. A filtering subquery must return one column; use this shape when the comparison expects one value. section 11.1
SELECT
OrderID,
CustomerID,
OrderDate
FROM orderheader
WHERE OrderDate > (
SELECT
oh.OrderDate
FROM orderheader oh
INNER JOIN customer c
ON oh.CustomerID = c.CustomerID
WHERE c.FirstName = 'Margaret'
AND c.LastName = 'Montoya'
);
The outer query returns orders after the date supplied by the nested query, replacing a hardcoded date with a calculated value. section 11.1
Match the operator to the number and kind of values returned by the nested query:
- Use IN for rows whose value appears in a multirow result. Use = ANY when a comparison operator such as >, >=, <, or <= is needed. section 11.2.1
- Use NOT IN to exclude returned values. Do not use <> ANY for this job: with multiple returned values, it can match every row. Use <> ALL when a comparative operator is required. section 11.2.2
- Use EXISTS or NOT EXISTS when the question is whether a matching row exists. Link the inner and outer queries in the inner WHERE clause; the value selected inside EXISTS is irrelevant. section 11.2.3
A nested query can also appear in FROM, where its result is joined like a table and needs an alias. This placement can return multiple columns. A subquery can appear in SELECT as well, but that form is rarely the best choice. Because subqueries add work for the database system, use them thoughtfully; EXISTS and NOT EXISTS are presented as better-performing choices for larger data sets. section 11.3.1 section 11.3.2 section 11.2.3
Practice checks
Try finding titles in Joe Pagenaud’s only order with IN, orders after all of Cora Daly’s orders with ALL, and the longest title name by comparing LENGTH against a nested MAX result. section 11.4 section 11.5 section 14.6
