Subqueries and Set Operators: Correlated Subqueries, EXISTS, the NOT IN Trap and UNION/INTERSECT/EXCEPT

Version note: The SQL (Structured Query Language) examples in this post were run on SQL Server 2025 (17.0, CU10 (Cumulative Update 10)) against the series' Bookstore database; PostgreSQL comparisons were tried on PostgreSQL 18.6.

Guess First

The catalog team wants to clean up categories that contain no books. The task looks easy: find the category_id values the books use, and return the categories that are not in that list.

SELECT category_id, name
FROM dbo.categories
WHERE category_id NOT IN (SELECT category_id FROM dbo.books);

How many rows do you expect? For reference, here is the book count of every category:

category_id name            books
----------- --------------- -----
          1 Fiction             0
          2 Classics           29
          3 Science Fiction     9
          4 Dystopian           4
          5 Fantasy             6
          6 Mystery             6
          7 Poetry              0
          8 Non-Fiction         0
          9 Science             5
         10 Computing           0
         11 Programming         9
         12 Algorithms          2
         13 Children            5

Four categories are empty: Fiction, Poetry, Non-Fiction and Computing (the parent categories keep their books in child categories, and Poetry really is empty). The query's answer, however, is:

category_id name
----------- ----

Zero rows. No error, no warning, just an empty result. A report built on this query would say "nothing to clean up" and nobody would notice. We solve why in the middle of this post, next to EXISTS. First let us set up the kinds of subqueries one by one.

Scalar Subquery: It Must Return One Value

A subquery is a SELECT written in parentheses and used inside another query. The simplest is the scalar subquery: it returns one row and one column, and it can go anywhere a value can. Books more expensive than twice the average price:

SELECT title, list_price
FROM dbo.books
WHERE list_price > (SELECT AVG(list_price) * 2 FROM dbo.books)
ORDER BY list_price DESC;

title                                             list_price
------------------------------------------------- ----------
Design Patterns                                      1450.00
The Art of Computer Programming                      1450.00
Structure and Interpretation of Computer Programs    1250.00
The Algorithm Design Manual                          1150.00
Code Complete                                        1150.00
The Pragmatic Programmer                             1150.00
Refactoring                                          1150.00

The average is 541.55; twice that is 1083.10. The subquery is computed once and its result is used like a constant. The contract of a scalar subquery is strict: if it returns more than one row, the query fails. Let us try to fetch each book's author with a scalar subquery:

SELECT title,
       (SELECT author_id FROM dbo.book_authors AS ba WHERE ba.book_id = b.book_id) AS author_id
FROM dbo.books AS b
WHERE b.book_id BETWEEN 39 AND 45
ORDER BY b.book_id;

title                      author_id
-------------------------- ---------
The Hobbit                        28
The Fellowship of the Ring        28
A Wizard of Earthsea              20
Msg 512, Level 16, State 1, Server b8be943222fa, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Notice that three rows came back before the error. SQL Server stopped as soon as it reached the first book with two authors (Good Omens) while producing rows. On the application side that means reading part of the result and getting an exception in the middle. If every book in your test data has a single author, this error hides until production. When you write a scalar subquery, ask yourself: "What prevents this query from returning more than one row?" If the answer is not a primary key or a UNIQUE constraint, either use an aggregate (MAX, MIN) or switch to a join.

IN, ANY and ALL

When a subquery returns a list, use it with IN. Customers who gave a one-star rating:

SELECT customer_id, first_name, last_name
FROM dbo.customers
WHERE customer_id IN (SELECT customer_id FROM dbo.reviews WHERE rating = 1);

customer_id first_name last_name
----------- ---------- ---------
          7 Barbara    Liskov
         19 Niklaus    Wirth
         21 Annie      Easley
         30 Jean       Bartik

IN means the same as = ANY: true if the value equals any element of the list. The standard generalises this to every comparison operator: > ANY means "greater than at least one", > ALL means "greater than all". Books more expensive than every book in the Classics category (2):

SELECT title, list_price
FROM dbo.books
WHERE list_price > ALL (SELECT b2.list_price FROM dbo.books AS b2 WHERE b2.category_id = 2);

title                                             list_price
------------------------------------------------- ----------
The C Programming Language                            950.00
The Pragmatic Programmer                             1150.00
Clean Code                                            890.00
Refactoring                                          1150.00
Design Patterns                                      1450.00
Code Complete                                        1150.00
The Mythical Man-Month                                890.00
Domain-Driven Design                                  950.00
Structure and Interpretation of Computer Programs    1250.00
The Art of Computer Programming                      1450.00
The Algorithm Design Manual                          1150.00

ALL has a trap: if the subquery returns nothing, the condition is true for every row. Mathematically correct, since there is no element that could say "you are not greater than me", but surprising in a report. With Poetry (7), which has no books:

SELECT COUNT(*) AS books
FROM dbo.books
WHERE list_price > ALL (SELECT b2.list_price FROM dbo.books AS b2 WHERE b2.category_id = 7);

books
-----
   77

All 77 books came out "more expensive than every book in Poetry". Writing > ALL as > (SELECT MAX(...)) often states the intent more clearly; in that case the empty set makes MAX return NULL and the condition is true for no row. You decide which one you want, but knowing the difference.

Correlated Subquery: A Query That Looks at the Outer Row

The subqueries so far were independent of the outer query. A correlated subquery references a column of the outer query; logically it is re-evaluated for every row of the outer query. Books 30% more expensive than the average of their own category:

SELECT b.title, b.category_id, b.list_price
FROM dbo.books AS b
WHERE b.list_price > (SELECT AVG(b2.list_price) * 1.3
                      FROM dbo.books AS b2
                      WHERE b2.category_id = b.category_id)
ORDER BY b.category_id, b.list_price DESC;

title                      category_id list_price
-------------------------- ----------- ----------
Emma                                 2     680.50
To the Lighthouse                    2     660.90
Great Expectations                   2     650.00
Tutunamayanlar                       2     620.90
Madame Bovary                        2     580.50
Don Quixote                          2     580.00
The Fellowship of the Ring           5     600.00
The Selfish Gene                     9     600.00
Design Patterns                     11    1450.00
The Little Prince                   13     670.90

The line b2.category_id = b.category_id makes the connection: for Emma the subquery computes only the Classics average, for Design Patterns only Programming's. The two books with a NULL category are not in the list, because b2.category_id = NULL is never true and the subquery returns a NULL average.

"Runs again for every row" describes the logical meaning, not the physical implementation. The optimizer often turns correlated subqueries into a join or a one-off aggregate. We will measure that in Production Notes.

Derived Table: A Subquery in FROM

A subquery used inside FROM is called a derived table. Its result behaves like a table, so an alias is mandatory. The most common use is filtering the result of an aggregate or combining it with something else. Former customers who placed no orders in 2026:

SELECT t.customer_id, t.orders, t.last_order
FROM (
    SELECT customer_id, COUNT(*) AS orders, MAX(ordered_at) AS last_order
    FROM dbo.orders
    GROUP BY customer_id
) AS t
WHERE t.last_order < '2026-01-01'
ORDER BY t.last_order;

customer_id orders last_order
----------- ------ -------------------
          8      4 2025-09-09 21:42:16
         28      6 2025-12-19 20:30:04
         10      3 2025-12-23 20:06:03

You could also write this query with HAVING MAX(ordered_at) < '2026-01-01'. The strength of a derived table is that you name the columns of an intermediate result and use them freely in the outer query. In the next post we see a more readable form of the same idea, the CTE (Common Table Expression).

EXISTS and the NOT IN Trap

EXISTS checks whether the subquery returns at least one row; it does not care what it returns, which is why writing SELECT 1 is customary. Its value is only TRUE or FALSE, never UNKNOWN. Customers who never placed an order:

SELECT c.customer_id, c.first_name, c.last_name
FROM dbo.customers AS c
WHERE NOT EXISTS (SELECT 1 FROM dbo.orders AS o WHERE o.customer_id = c.customer_id);

customer_id first_name last_name
----------- ---------- ---------
         37 Mert       Kaya
         38 Zeynep     Demir
         39 Can        Sahin
         40 Deniz      Celik

This is an anti-join: "give me the rows without a partner". In post 09 of this series we did the same job with LEFT JOIN ... IS NULL. Now back to the opening query. NOT IN is really a series of <> comparisons joined with AND:

category_id NOT IN (2, 3, 4, NULL)
/* is equivalent to: */
category_id <> 2 AND category_id <> 3 AND category_id <> 4 AND category_id <> NULL

The last term, category_id <> NULL, is always UNKNOWN. TRUE AND UNKNOWN is UNKNOWN, FALSE AND UNKNOWN is FALSE. So every row yields either FALSE or UNKNOWN, and WHERE only lets TRUE rows through. Two books have a NULL category_id, so the subquery's list contained a NULL, and that wiped out the whole result. It is the most expensive consequence of the three-valued logic we saw in post 06 of this series.

There are two fixes. The first is NOT EXISTS; NULL does not affect it, because it does not look for equality, it looks for existence:

SELECT c.category_id, c.name
FROM dbo.categories AS c
WHERE NOT EXISTS (SELECT 1 FROM dbo.books AS b WHERE b.category_id = c.category_id);

category_id name
----------- -----------
          1 Fiction
          7 Poetry
          8 Non-Fiction
         10 Computing

The second keeps NOT IN but removes NULLs from the subquery:

SELECT category_id, name
FROM dbo.categories
WHERE category_id NOT IN (SELECT category_id FROM dbo.books WHERE category_id IS NOT NULL);

category_id name
----------- -----------
          1 Fiction
          7 Poetry
          8 Non-Fiction
         10 Computing

I prefer the first: NOT EXISTS does not break when a column that holds no NULLs today becomes nullable tomorrow. Use NOT IN only with a constant list (NOT IN ('Cancelled', 'Pending')) or a column you are sure is NOT NULL. This behaviour is the standard itself; the same NOT IN + NULL test on PostgreSQL 18 also returned zero rows.

On the positive side there is no result difference between IN and EXISTS; NULL causes no problem here, because once one of the comparisons joined with OR is TRUE, the result is TRUE. Which one you write is a matter of readability; the optimizer can turn both into the same semi join.

Set Operators: UNION, INTERSECT and EXCEPT

A subquery puts one query inside another. Set operators combine the results of two queries on top of each other rather than side by side. The rules: both sides must have the same number of columns, the types must be compatible, column names come from the first query, and ORDER BY is written only at the very end, for the whole result.

UNION combines two results and removes duplicates; UNION ALL does not:

SELECT city FROM dbo.customers WHERE country_code = 'TR'
UNION
SELECT city FROM dbo.customers WHERE country_code = 'TR';

city
--------
NULL
Ankara
Antalya
Bursa
Istanbul
Izmir

We combined the same query with itself and got 6 rows: deduplication removed both the repeats and the identical rows from the two sides. Notice that it also deduplicated NULL as a single value; set operators, like GROUP BY, treat NULLs as "not distinct". The difference in row count:

SELECT
    (SELECT COUNT(*) FROM (SELECT city FROM dbo.customers UNION ALL SELECT city FROM dbo.customers) AS x) AS union_all_rows,
    (SELECT COUNT(*) FROM (SELECT city FROM dbo.customers UNION SELECT city FROM dbo.customers) AS x) AS union_rows;

union_all_rows union_rows
-------------- ----------
            80         11

Deduplication is not free: SQL Server adds a sort or hash step for it. If you know the two sides cannot overlap (different years, different countries), always write UNION ALL.

INTERSECT returns rows present on both sides, EXCEPT those on the first side but not the second. The number of customers who ordered in the first half of 2025 and in the first half of 2026:

SELECT COUNT(*) AS loyal_customers
FROM (
    SELECT customer_id FROM dbo.orders WHERE ordered_at >= '2025-01-01' AND ordered_at < '2025-07-01'
    INTERSECT
    SELECT customer_id FROM dbo.orders WHERE ordered_at >= '2026-01-01' AND ordered_at < '2026-07-01'
) AS both_halves;

loyal_customers
---------------
             24

And those who ordered in the first half of 2025 but did not come back in the first half of 2026, the lost customers:

SELECT customer_id FROM dbo.orders WHERE ordered_at >= '2025-01-01' AND ordered_at < '2025-07-01'
EXCEPT
SELECT customer_id FROM dbo.orders WHERE ordered_at >= '2026-01-01' AND ordered_at < '2026-07-01'
ORDER BY customer_id;

customer_id
-----------
          8
         10
         28

INTERSECT and EXCEPT treat NULLs as equal; = in joins and WHERE does not:

SELECT NULL AS x INTERSECT SELECT NULL;

   x
----
NULL

The errors you get when the two sides do not fit are instructive too. When the column count differs:

SELECT customer_id, email FROM dbo.customers
UNION
SELECT customer_id FROM dbo.orders;

Msg 205, Level 16, State 1, Server b8be943222fa, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

When the count matches but the types do not, SQL Server tries a conversion based on data type precedence; int has precedence over varchar, so it tries to turn e-mail addresses into numbers and falls over on the first row:

SELECT email FROM dbo.customers
UNION ALL
SELECT customer_id FROM dbo.orders;

Msg 245, Level 16, State 1, Server b8be943222fa, Line 1
Conversion failed when converting the varchar value 'ada.lovelace@example.com' to data type int.

The standard also defines INTERSECT ALL and EXCEPT ALL, which work by counting duplicates. PostgreSQL supports both; SQL Server does not:

SELECT customer_id FROM dbo.orders
EXCEPT ALL
SELECT customer_id FROM dbo.reviews;

Msg 324, Level 15, State 2, Server b8be943222fa, Line 3
The 'ALL' version of the EXCEPT operator is not supported.

One last note: when you use several set operators in one query, SQL Server evaluates INTERSECT first, then UNION and EXCEPT left to right. Do not make the reader rely on that rule; use parentheses. In MySQL, INTERSECT and EXCEPT only arrived with 8.0.31; if you target an older MySQL, these queries do not run at all.

Production Notes

"Correlated subqueries are slow, always turn them into joins" is common advice. Let us measure. I wrote the same question, the last order date of customers in the Netherlands, in two ways and looked at the pages read with SET STATISTICS IO ON:

SET STATISTICS IO ON;
SELECT c.customer_id,
       (SELECT MAX(o.ordered_at) FROM dbo.orders AS o WHERE o.customer_id = c.customer_id) AS last_order
FROM dbo.customers AS c
WHERE c.country_code = 'NL';
SET STATISTICS IO OFF;

customer_id last_order
----------- -------------------
         19 2026-05-27 19:39:55
         39 NULL
Table 'orders'. Scan count 2, logical reads 8, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'customers'. Scan count 1, logical reads 3, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SET STATISTICS IO ON;
SELECT c.customer_id, MAX(o.ordered_at) AS last_order
FROM dbo.customers AS c
LEFT JOIN dbo.orders AS o ON o.customer_id = c.customer_id
WHERE c.country_code = 'NL'
GROUP BY c.customer_id;
SET STATISTICS IO OFF;

customer_id last_order
----------- -------------------
         19 2026-05-27 19:39:55
         39 NULL
Table 'orders'. Scan count 2, logical reads 8, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'customers'. Scan count 1, logical reads 3, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

The logical reads are identical: 8 for orders, 3 for customers. The reason is visible in the plans. When I took the plans of both queries with SET SHOWPLAN_TEXT ON, the correlated version consisted of these operators:

  |--Compute Scalar(DEFINE:([Expr1004]=[Expr1002]))
       |--Nested Loops(Left Outer Join, OUTER REFERENCES:([c].[customer_id]))
            |--Clustered Index Scan(OBJECT:([sqlt_11].[dbo].[customers].[PK_customers] AS [c]), WHERE:([sqlt_11].[dbo].[customers].[country_code] as [c].[country_code]='NL'))
            |--Stream Aggregate(DEFINE:([Expr1002]=MAX([sqlt_11].[dbo].[orders].[ordered_at] as [o].[ordered_at])))
                 |--Clustered Index Scan(OBJECT:([sqlt_11].[dbo].[orders].[PK_orders] AS [o]), WHERE:([sqlt_11].[dbo].[orders].[customer_id] as [o].[customer_id]=[sqlt_11].[dbo].[customers].[customer_id] as [c].[customer_id]))

The plan of the join version is the same except for the top Compute Scalar: the optimizer reduced both forms to the same logical tree. The optimizer looks at what the query asks for, not how it is written; that is the practical side of the declarative language from post 01 of this series. There are of course cases where a correlated subquery really is expensive (in this plan orders is scanned for every customer, and as the table grows an index on customer_id becomes a must), but you reach that verdict by looking at the plan, not at the syntax. Reading plans belongs to the SQL Server Derinlemesine (SQL Server in Depth) series.

The difference between NOT IN and NOT EXISTS is not only correctness; the plans differ too. The plan of the opening NOT IN query has a branch the NOT EXISTS plan lacks: a Row Count Spool that scans books for category_id IS NULL, plus a second Left Anti Semi Join. SQL Server has to check separately, on every execution, whether the list contains a NULL. Making the column NOT NULL or writing NOT EXISTS solves both problems at once.

Watch out for long IN lists coming from the application. When hand-written code or an ORM (Object-Relational Mapping) sends a collection as IN (@p0, @p1, ...), every different list length is a different query text, and so a separate plan. How Entity Framework Core (EF Core) translates such lists has changed from version to version; the what's-new posts of the EF Core series show the generated SQL. The SQL Server-side alternatives are a table-valued parameter or sending the list as JSON (JavaScript Object Notation) in a single parameter.

Common Pitfalls

Using NOT IN with a subquery that returns a nullable column. A single NULL empties the whole result; write NOT EXISTS.

Assuming a scalar subquery returns one row because the test data does. The first multi-author book brings error 512; a key or an aggregate must guarantee uniqueness.

Forgetting that > ALL with an empty subquery lets everything through.

Writing UNION for results you know cannot repeat. You pay for an unnecessary deduplication step; use UNION ALL.

Trying to put a separate ORDER BY on each side of a set operator. Ordering applies only to the combined result.

Assuming a correlated subquery is slow because of how it is written and turning it into a less readable join. Measure first: look at the logical reads and the plan.

A Small Experiment

★☆☆ Write the books that never received a review in three different ways: NOT EXISTS, LEFT JOIN ... IS NULL and EXCEPT. Verify that all three return the same result (19 books).

★★☆ For each customer, return their total order count next to the average orders per customer in their own country. Write it first with a correlated subquery, then with a derived table, and compare the logical reads with SET STATISTICS IO ON.

★★★ Create a temporary table named #reviewers holding the customer_id values from reviews plus one NULL, and run the "customers who never wrote a review" query against it with NOT IN. After you see the empty result, write the same query with NOT EXISTS and compare the two plans with SET SHOWPLAN_TEXT ON.

As derived tables nest, a query starts to read inside out, and at some point it becomes impossible to follow. In the next post we cover CTEs, which let us write the same logic top to bottom, views as a lasting abstraction, and recursive queries that walk the category tree.