SQL subqueries: scalar values, IN, EXISTS and derived tables

A subquery is useful when the answer to one question becomes the input to another. I might first need an average price, then a list of products priced above it. Or I might identify customers with orders before selecting their details. SQL lets me express those dependencies within one statement.

I start with the shape of the required result: one value, a collection of values, a table or an existence check. That helps me choose the right construct and anticipate mistakes. The examples below use synthetic data in PostgreSQL 18. Amounts are arbitrary units, and the small results can be derived by reading the tables.

What a subquery is

A subquery is a SELECT used inside another query. It can supply a comparison value, a collection for a membership test or a source in FROM. Parentheses mark its boundaries, but nesting alone does not establish what kind of result the surrounding expression expects.

I therefore define the contract first. The expression price > (...) expects one value. The expression customer_id IN (...) compares an identifier with a collection. FROM needs a table result. EXISTS checks whether a row exists. These constructs have different rules for empty results and NULL.

I do not teach a rule that the database always executes the inside first. That can sometimes explain a logical dependency, but does not define a physical execution plan. PostgreSQL may transform a query or finish checking early. For EXISTS in particular, the documentation warns against assuming the subquery runs to completion.

Data for the first example

The products source contains four products. Three have known prices, while the fourth has NULL. Here NULL represents missing price information.

This statement constructs the data and selects products priced above the average. WITH names a source built with VALUES. The name exists within this statement only. For later examples, keep the appropriate WITH section and replace the final query.

WITH products(product_id, product_name, price) AS (
    VALUES
        (1, 'Notebook', 10),
        (2, 'Mug', 20),
        (3, 'Lamp', 30),
        (4, 'Poster', NULL)
)
SELECT product_id, product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY product_id;

The VALUES documentation describes supplying a small table without storing it on disk. This makes the whole example visible. There are no additional products, hidden filters or prices outside the displayed list that you need to guess.

Scalar subqueries supply one value

The average of ten, twenty and thirty is twenty. AVG ignores NULL, so the poster does not increase the denominator. Only the lamp priced at thirty passes the condition. The mug equals the average, while the operator requires strictly greater values. The aggregate documentation defines AVG’s treatment of missing values.

A subquery used as a scalar value must return one column and at most one row. One row supplies its value. No rows produce NULL. More than one row causes an error even if all returned values look identical. These rules are specified in the scalar subquery documentation.

Our AVG without GROUP BY returns one aggregate row. If no known prices exist, its value is NULL. Comparing a price with NULL does not produce true, so WHERE admits nothing. An empty output means no product passed this condition; it does not establish an average of zero.

Why a plain SELECT can fail

Consider price > (SELECT price FROM products). There are four source rows, so the subquery violates the single-value contract. The database will not pick one price for you. You must decide whether the intended reference is an average, maximum, minimum or a particular product.

I do not repair that by adding an arbitrary LIMIT 1. It caps the number of rows without defining the right value. Even an ORDER BY must match the question. The highest price and the price of the most recently added product are different choices.

When selecting a record by order, provide a stable criterion and a tie-breaker. If two updates share a timestamp, that timestamp alone may be insufficient. Choosing another key belongs to the result definition. The LIMIT documentation explains why an unspecified subset is unreliable.

A second dataset: customers and orders

The remaining examples use a separate dataset. The customers source contains Ada, Ben and Celina with identifiers one, two and three. The orders source contains four orders. The last has an unknown customer, deliberately included to demonstrate a NOT IN trap.

These are the sources for the following queries. The final SELECT merely displays orders; replace it with the examples in subsequent sections.

WITH customers(customer_id, customer_name) AS (
    VALUES (1, 'Ada'), (2, 'Ben'), (3, 'Celina')
), orders(order_id, customer_id, amount) AS (
    VALUES
        (101, 1, 100),
        (102, 1, 50),
        (103, 2, 80),
        (104, NULL, 20)
)
SELECT order_id, customer_id, amount
FROM orders
ORDER BY order_id;

Each customer appears once. Each order also appears once, but a customer can have several orders. That asymmetry matters when comparing a subquery with a join. An order count and a count of people placing orders answer different questions.

IN tests membership

SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders)
ORDER BY customer_id;

The result contains Ada and Ben. Ada’s identifier appears twice in orders, but IN does not duplicate her customer row. It tests membership. In this example, the left expression is a single identifier, so the subquery must return one column.

The NULL in the collection does not prevent confirmed matches for Ada and Ben. For Celina, there is no equal identifier and there is a missing value. The condition is therefore unknown, and WHERE rejects it. The IN documentation describes these rules.

I do not add DISTINCT solely out of concern that repeated identifiers will multiply customers here. IN does not construct a pair for each occurrence. DISTINCT has other uses, but it does not change which known customer identifiers belong to this collection.

NOT IN and NULL: why Celina disappears

SELECT customer_id, customer_name
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders)
ORDER BY customer_id;

This query returns no customers. Ada and Ben have known matches, so NOT IN is false for them. For Celina, the NULL in the subquery makes the result unknown. WHERE accepts only true. Celina disappears despite having no order with her identifier.

You can filter missing keys inside the subquery with WHERE customer_id IS NOT NULL. For our non-null customer identifiers, that leaves Celina. You still need to understand NULL on the outer side, however. NOT IN should not be presented as a generally missing-value-proof construction.

The NOT IN documentation states the rule explicitly. When asking for the absence of a relationship, I usually prefer NOT EXISTS with an explicit correlation condition. It makes the intended relationship easier to read.

EXISTS and NOT EXISTS check for a matching row

SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
)
ORDER BY c.customer_id;

This returns Celina. For each customer, I ask whether an order with an equal identifier exists. The NULL in order 104 matches none of the three known identifiers. It does not block finding Celina in the way that NOT IN did.

EXISTS without NOT would return Ada and Ben. The existence of a row matters, rather than the value of the one in SELECT. In these simple cases, even SELECT NULL would still indicate existence if the condition found a row. The one is a readable convention, rather than a match counter.

This is a correlated subquery because it refers to c.customer_id from the outer query. Such a reference is valid and documented. There is no general ban on using outer columns in subqueries. You do need to understand the scope of names and the particular construct containing the subquery.

A correlated count in SELECT

SELECT
    c.customer_id,
    c.customer_name,
    (
        SELECT COUNT(*)
        FROM orders AS o
        WHERE o.customer_id = c.customer_id
    ) AS order_count
FROM customers AS c
ORDER BY c.customer_id;

Ada receives two, Ben one and Celina zero. COUNT without GROUP BY returns a single row even when its matching input is empty. That differs from a plain SELECT returning no rows, whose scalar value would be NULL.

Compare this with COUNT after LEFT JOIN. Here COUNT(*) works inside the order source filtered to one customer. It does not count an empty row supplied by an outer join. The same function syntax can therefore require a different interpretation depending on the rows it receives.

I read aliases carefully. The condition o.customer_id = c.customer_id connects two levels. A condition referring only to orders on both sides would answer another question. Explicit prefixes help prevent accidental loss of correlation when a fragment is copied or edited.

A subquery in FROM creates another level

I want customers with at least two orders and their total amount. First I construct a customer summary, then I filter its result.

SELECT s.customer_id, s.order_count, s.total_amount
FROM (
    SELECT
        customer_id,
        COUNT(*) AS order_count,
        SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
) AS s
WHERE s.order_count >= 2
ORDER BY s.customer_id;

The result contains one row: customer one, two orders and a total of 150. The subquery also produces a group for customer two and a NULL group for order 104, but each has count one. The outer condition removes them.

The alias s names the derived table. Its order_count and total_amount columns are available at the outer level. Arbitrary hidden inner columns that the subquery did not return are not available there. The FROM subquery documentation describes this source form.

The same count criterion can be expressed with HAVING at the grouping level. I use a subquery here to expose two separate stages and explain alias availability. More nesting does not automatically make a query better. Each additional level should have a clear purpose.

Average order amount needs the right grain

A subquery can look convincing while calculating a different metric from the one intended. If an order contains several items, first produce one row per order. Only then does averaging order amounts describe an average purchase. Averaging item prices answers another question.

Our small order source already has one row per order, so I can calculate the average separately for known customers. This fragment explicitly excludes the order with an unknown customer.

SELECT customer_id, AVG(amount) AS average_order_amount
FROM orders
WHERE customer_id IS NOT NULL
GROUP BY customer_id
ORDER BY customer_id;

Ada has amounts one hundred and fifty, so her average is seventy-five. Ben has one order worth eighty, so his average is eighty. Celina is absent because she has no orders. To retain her, attach the summary to the customer list.

A subsequent average of these two customer averages would give each customer equal weight. Averaging all orders instead gives Ada two events and Ben one. Both metrics can be useful, but they need different names. Another level of nesting does not decide which interpretation you intended.

I therefore record what each stage’s row means. It might start as an item, become an order and later become a customer. Confusing those levels can produce a valid number for a badly named metric. A small dataset with different order counts per customer quickly reveals that distinction.

An empty result differs from an aggregate row

Compare two scalar subqueries for Celina. A plain SELECT of amounts filtered to customer three finds no rows and supplies NULL as a scalar. SELECT COUNT(*) with the same filter still returns one aggregate row containing zero.

Adding GROUP BY customer identifier to that COUNT means no group exists for a customer without orders. The zero row disappears. COUNT has not contradicted itself; the result construction has changed. In a correlated count, check whether grouping is needed at all.

Likewise, MAX or AVG without grouping can return one row containing NULL. EXISTS checks rows, so that single aggregate row is enough to make it true. For an existence condition, I usually query the filtered records directly rather than introduce aggregation without a deliberate reason.

This distinction is easy to miss when simplifying a query. If you replace a count-based condition with EXISTS, verify that the inner query produces rows only when a genuine match exists. A customer without orders is an especially useful case for reviewing that change.

Name scope and LATERAL

Referring to an outer column inside EXISTS or a scalar SELECT is ordinary correlation. A different case arises when a FROM subquery wants to use a preceding source within the same FROM list. PostgreSQL provides LATERAL for those references. The LATERAL documentation distinguishes the situations.

For now, avoid mixing those rules. You do not need to add LATERAL to every subquery or assume every name is visible everywhere. Establish which level introduces an alias, which columns it returns and where you intend to reference them.

Good names also help with longer statements. The alias s is sufficient for a short demonstration, but customer_totals conveys more meaning in a larger report. Shortening code should not force the next person to reconstruct the purpose of every fragment.

Subquery, JOIN or CTE

When you need columns from each matching record, INNER JOIN is a natural choice. When you only need an existence check, EXISTS expresses that without constructing every pair. A derived table or CTE can provide a summary before attaching another collection.

A CTE introduced with WITH lets you name a stage earlier in the statement. In PostgreSQL, WITH alone does not universally promise materialization or a speed improvement. Details depend on the query, the number of references and options described in the WITH documentation.

I do not choose between these forms using a rule that JOIN always beats a subquery. First I compare result meaning, especially duplicates and NULL. Only then is it useful to inspect plans with EXPLAIN. Different outputs do not make a fair performance comparison.

Order the final result explicitly

Sorting inside a subquery does not provide a general guarantee about the final output order. If the recipient needs customers ordered by identifier, put ORDER BY at the outermost level. I do that explicitly in the examples. It makes the predicted result easier to compare with the presentation and avoids treating an incidental plan order as a reporting contract.

Exercise: three questions for the same source

First list customers with at least one order. The expected people are Ada and Ben. Then list customers without orders. Celina should remain even when the source contains an order with an unknown customer. Finally, add an order count to all three customers.

Before writing each version, name the shape you need: existence, absence or a single count per customer. Explain why Ada’s two orders should not create two customer-list rows. If you choose a JOIN, explain how you control that multiplicity.

Also consider an empty order source. EXISTS admits nobody, NOT EXISTS admits all three customers and the correlated COUNT gives each zero. These cases help you understand the constructs rather than memorize fragments. Continue with LEFT JOIN and the logical order of SQL clauses.