Subqueries in Practice

Anatomy of a Subquery

A subquery is a query nested inside another query. The inner query executes first, and its result feeds into the outer query. Subqueries solve a specific class of problem: you need a value or set of values that can only be computed from the data itself, not from a constant you can hardcode.

Consider a simple question: which employees earn more than the company average? You cannot write WHERE salary > 85000 because the average is not a fixed number — it changes every time the data changes. You need the database to compute the average first, then compare each row against it. That is exactly what a subquery does.

SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

The parenthesized (SELECT AVG(salary) FROM employees) is a scalar subquery — it returns exactly one row and one column. The outer query treats that single value as if it had been typed literally. The database executes the inner query once, caches the result, and uses it for every row evaluation.

Subqueries can appear in three positions, and the position determines what shape of result they must return:

• SELECT clause: must return a single scalar value per row (or a correlated scalar).
• WHERE clause: used for filtering — can return a single value, a column of values, or a table depending on the operator.
• FROM clause: must return a table (any number of rows and columns), which the outer query treats as a derived table.

-- Subquery in FROM (derived table)
SELECT dept, avg_salary
FROM (
    SELECT department AS dept, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_avgs
WHERE avg_salary > 75000;

The FROM-clause subquery is sometimes called an inline view or derived table. It must have an alias (dept_avgs above) because the outer query needs a name to reference it by. This is not optional in most databases — omitting the alias raises a syntax error.

Understanding which position to use is the first skill. The second is understanding when a subquery is the right tool versus a JOIN. As a rule of thumb: if you need to filter rows based on an aggregate or a condition that spans tables, a subquery is natural. If you need columns from both tables in the output, a JOIN is usually clearer. The next chapters develop this judgment with concrete patterns.

Filtering with IN and NOT IN

The most common use of a subquery in a WHERE clause is membership testing: does this row's value appear in a set computed from another table? The IN operator answers that question directly.

Suppose you want all orders placed by customers in New York. You do not store the city on the orders table — it lives on the customers table. A subquery bridges the gap:

SELECT order_id, order_date, total
FROM orders
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE state = 'NY'
);

The inner query returns a column of customer IDs. The outer query checks each order's customer_id against that column. This is a non-correlated subquery: the inner query does not reference anything from the outer query, so the database can execute it once and reuse the result.

NOT IN is the complement — it returns rows whose value does not appear in the subquery result:

SELECT product_name, price
FROM products
WHERE category_id NOT IN (
    SELECT category_id
    FROM categories
    WHERE discontinued = TRUE
);

This finds all products whose category is not discontinued. The logic reads almost like English, which is why IN and NOT IN are the most widely used subquery patterns in production SQL.

The NULL trap. NOT IN has a notorious edge case. If the subquery returns even one NULL value, NOT IN returns zero rows — not because no rows match, but because the three-valued logic of SQL makes every comparison UNKNOWN. Consider:

-- Dangerous: if any category_id is NULL, this returns nothing
SELECT product_name
FROM products
WHERE category_id NOT IN (
    SELECT category_id FROM categories WHERE discontinued = TRUE
);

If the categories table has a row where category_id is NULL, the NOT IN comparison for every product becomes category_id <> NULL, which is UNKNOWN, and UNKNOWN rows are filtered out. The fix is to exclude NULLs from the subquery:

WHERE category_id NOT IN (
    SELECT category_id FROM categories
    WHERE discontinued = TRUE AND category_id IS NOT NULL
)

Or use NOT EXISTS instead, which handles NULLs correctly by design. The practical rule: never use NOT IN unless you are certain the subquery column has no NULLs. In production, that certainty is rare, so experienced analysts reach for NOT EXISTS as the default.

EXISTS and Correlated Subqueries

EXISTS tests whether a subquery returns any rows. It does not care about the values — only about presence. This makes it the right tool for questions like "find customers who have placed at least one order" or "find products that have never been ordered."

-- Customers with at least one order
SELECT c.customer_id, c.first_name, c.last_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

Notice the critical difference from the IN pattern: the inner query references c.customer_id, a column from the outer query. This makes it a correlated subquery — the inner query cannot be executed once and cached. It must be re-evaluated for each row of the outer query, using that row's customer_id as input.

The SELECT 1 inside EXISTS is a convention, not a requirement. Since EXISTS only checks whether any row is returned, the column list is irrelevant. You could write SELECT * and get the same result. SELECT 1 signals intent: "I only care that a row exists, not what it contains."

NOT EXISTS is the anti-join pattern — find rows in one table that have no matching row in another:

-- Products that have never been ordered
SELECT p.product_id, p.product_name
FROM products p
WHERE NOT EXISTS (
    SELECT 1
    FROM order_items oi
    WHERE oi.product_id = p.product_id
);

This is the NULL-safe alternative to NOT IN. Because NOT EXISTS checks row-by-row presence rather than comparing values, a NULL in the join column does not poison the result.

Performance characteristics. Correlated subqueries have a reputation for being slow, and for good reason: a naive implementation re-runs the inner query for every outer row. Modern query optimizers (PostgreSQL, SQL Server, MySQL 8+) can often rewrite EXISTS and IN subqueries into semi-joins internally, making them as fast as an explicit JOIN. But this rewrite is not guaranteed. When performance matters, test both forms:

-- EXISTS form
SELECT c.customer_id
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

-- Equivalent JOIN form
SELECT DISTINCT c.customer_id
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;

The DISTINCT is necessary in the JOIN form because a customer with multiple orders would appear multiple times. The EXISTS form does not have this problem — it returns each customer once, regardless of how many orders they have. This is a subtle but important difference that makes EXISTS the cleaner choice for pure existence checks.

Scalar Subqueries and Comparisons

A scalar subquery returns exactly one value — one row, one column. It can be used anywhere a literal value can appear: in SELECT lists, in WHERE comparisons, in HAVING clauses, even in ORDER BY.

The classic use case is comparison against an aggregate. Find all products priced above the average:

SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

The inner query computes a single number. The outer query compares every product's price against it. Because the subquery is non-correlated (it does not reference the outer query), the database executes it once.

Scalar subqueries can also be correlated, though this is less common. A correlated scalar subquery computes a different value for each outer row:

-- Employees who earn more than their department average
SELECT e.first_name, e.last_name, e.department, e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department = e.department
);

Here the inner query references e.department, so it must be re-run for each employee, computing the average of that employee's department. The result is a per-row comparison against a context-dependent threshold.

The single-value contract. A scalar subquery must return at most one row. If it returns more than one, the database raises an error:

-- ERROR: more than one row returned by a subquery used as an expression
SELECT first_name
FROM employees
WHERE salary > (SELECT salary FROM employees WHERE department = 'Engineering');

If you are not certain the subquery returns one row, add a LIMIT 1 (or TOP 1 in SQL Server) or an aggregate function to guarantee the contract:

-- Safe: aggregate guarantees one row
SELECT first_name
FROM employees
WHERE salary > (SELECT MAX(salary) FROM employees WHERE department = 'Engineering');

Scalar subqueries in the SELECT list are useful for adding a computed context column without a JOIN:

SELECT
    product_name,
    price,
    price - (SELECT AVG(price) FROM products) AS diff_from_avg
FROM products;

Each row shows how far its price deviates from the global average. This pattern is common in exploratory analysis, though in production you would often compute the average in a CTE or window function instead, to avoid re-executing the subquery per row.