SQL Server Subqueries

A subquery is a SELECT nested inside another statement. T-SQL supports them in the SELECT list, FROM clause, and WHERE/HAVING predicates, nested up to 32 levels. The mechanics are standard SQL, but T-SQL has its own sharp edges: error 512 on scalar subqueries that misbehave at runtime, no row-value comparisons, a mandatory alias on derived tables, and APPLY as the idiomatic rewrite for correlated logic that other databases handle with lateral joins.

Sample Schema

CREATE TABLE customers (
    customer_id int         PRIMARY KEY,
    name        varchar(50) NOT NULL,
    region      varchar(10) NOT NULL
);
 
CREATE TABLE orders (
    order_id    int          PRIMARY KEY,
    customer_id int          NULL,      -- NULL = guest checkout
    amount      decimal(10,2) NOT NULL,
    order_date  date         NOT NULL
);
 
INSERT INTO customers VALUES
    (1, 'Alda',  'north'), (2, 'Bram', 'south'), (3, 'Chiara', 'south');
 
INSERT INTO orders VALUES
    (101, 1,    250.00, '2026-01-05'),
    (102, 1,    120.00, '2026-01-20'),
    (103, 2,    400.00, '2026-02-02'),
    (104, NULL,  80.00, '2026-02-10');

Scalar Subqueries and Error 512

A scalar subquery must return one row, one column. Used anywhere an expression fits:

SELECT name,
       (SELECT SUM(amount) FROM orders o
        WHERE o.customer_id = c.customer_id) AS total_spent
FROM customers c;

The trap: SQL Server validates the "one row" rule at runtime, not at parse time. A subquery that happens to return one row today compiles and works -- until the data changes:

Msg 512, Level 16, State 1
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

This is the classic production failure: code that passed every test breaks months later when a second matching row appears. If a subquery could return multiple rows, force the cardinality explicitly -- SELECT MAX(...), SELECT TOP (1) ... ORDER BY ... -- or restructure as a join. Note that TOP without ORDER BY picks an arbitrary row; if you use TOP (1), say which row you mean.

A scalar subquery that returns zero rows yields NULL, silently. Alda with no orders gets total_spent = NULL, not 0. Wrap with ISNULL(...) when downstream code expects a number.

IN: Multi-Row Subqueries

IN matches a column against the subquery's result set:

SELECT name FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE amount > 200);

Fine as far as it goes. The problem is its negation.

The NOT IN NULL Trap

This query returns zero rows, even though Chiara has no orders:

SELECT name FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

The subquery returns {1, 2, NULL} (order 104 is a guest checkout). customer_id NOT IN (1, 2, NULL) expands to customer_id <> 1 AND customer_id <> 2 AND customer_id <> NULL, and <> NULL evaluates to UNKNOWN under three-valued logic. One NULL in the list poisons the entire predicate for every row.

Fixes, best first:

-- 1. NOT EXISTS: NULL-safe, and the optimizer handles it well
SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);
 
-- 2. Filter the NULLs out explicitly
WHERE customer_id NOT IN (SELECT customer_id FROM orders
                          WHERE customer_id IS NOT NULL);

NOT EXISTS is the right default for anti-joins in T-SQL. It's immune to the NULL problem and typically compiles to the same anti-semi-join plan as the fastest alternatives. Our joins guide compares the three anti-join patterns in more depth.

EXISTS vs IN

EXISTS tests whether the subquery returns any row:

SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o
              WHERE o.customer_id = c.customer_id AND o.amount > 200);

What's inside the SELECT of an EXISTS subquery is irrelevant -- SELECT 1, SELECT *, SELECT 1/0 all behave identically because the projection is never evaluated. The engine only probes for row existence.

For positive membership tests, IN and EXISTS almost always produce the same plan in modern SQL Server -- pick whichever reads better. The decision that matters:

That last row is a genuine dialect gap: PostgreSQL accepts WHERE (region, amount) IN (SELECT ...); T-SQL does not. Correlated EXISTS is the T-SQL equivalent.

ANY, ALL, SOME

Comparison operators can be quantified over a subquery:

-- customers whose every order is above 100
SELECT name FROM customers c
WHERE 100 < ALL (SELECT amount FROM orders o WHERE o.customer_id = c.customer_id);

= ANY is exactly IN; <> ALL is exactly NOT IN (including the NULL trap). SOME is an ANSI synonym for ANY. These read poorly and most teams avoid them -- ALL against an empty set is TRUE, which surprises people (a customer with zero orders passes the filter above). Aggregate rewrites (MIN(amount) > 100) are usually clearer.

Derived Tables (Subqueries in FROM)

A subquery in FROM acts as an inline view. T-SQL requires an alias -- omitting it is a hard syntax error, not a style issue:

SELECT region, AVG(order_count) AS avg_orders_per_customer
FROM (
    SELECT c.customer_id, c.region, COUNT(o.order_id) AS order_count
    FROM customers c
    LEFT JOIN orders o ON o.customer_id = c.customer_id
    GROUP BY c.customer_id, c.region
) AS per_customer          -- alias is mandatory
GROUP BY region;

The standard use case: aggregate in two stages, or filter on a window function (ROW_NUMBER can't appear in WHERE, so you wrap it in a derived table -- see window functions). For anything referenced more than once or deeply nested, a CTE is the same thing with better readability; our CTE guide covers when each fits. Neither is materialized -- SQL Server inlines both into the outer query, so there's no performance difference between a derived table and an equivalent CTE.

One more restriction: ORDER BY is not allowed inside a derived table unless accompanied by TOP or OFFSET/FETCH. Rows in a derived table have no order anyway; sort at the outermost query.

Correlated Subqueries and the Per-Row Cost

A correlated subquery references columns from the outer query, so conceptually it runs once per outer row:

-- each customer's most recent order date
SELECT name,
       (SELECT MAX(order_date) FROM orders o
        WHERE o.customer_id = c.customer_id) AS last_order
FROM customers c;

The optimizer often decorrelates these into joins, but not always -- and a correlated scalar subquery in the SELECT list can only return one column. Needing two or more values from the same correlated lookup is where people write three near-identical subqueries. The T-SQL answer is OUTER APPLY:

SELECT c.name, latest.order_date, latest.amount
FROM customers c
OUTER APPLY (
    SELECT TOP (1) o.order_date, o.amount
    FROM orders o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.order_date DESC
) AS latest;

One correlated evaluation, as many columns as you want, and TOP (1) ... ORDER BY inside APPLY is the canonical top-N-per-group pattern. If you find yourself stacking correlated scalar subqueries, APPLY is almost always the cleaner rewrite.

Debugging Subquery Performance

Two things to check in the execution plan (Ctrl+M in SSMS for the actual plan):

• Spools and nested loops with high rewind counts under a correlated subquery mean the optimizer didn't decorrelate and is re-running the inner query per row. Rewriting as a JOIN, APPLY, or windowed aggregate usually fixes it.
• Row estimates of 1 feeding a scalar subquery operator are normal; estimates of 1 on a derived table that actually returns millions of rows are not -- statistics on the base tables are the first thing to update.

Common Mistakes

Writing correlated subqueries and their APPLY rewrites by hand gets tedious; Mako's AI autocomplete is schema-aware, so it can draft the decorrelated version against your actual tables.

Mako connects to SQL Server, PostgreSQL, MySQL, and 6 other databases with AI-powered autocomplete. Try it free at mako.ai.