Subqueries and EXISTS

Many questions are easiest to express as 'rows where something about other rows is true'.

A subquery is a SELECT in parentheses used inside another statement. Where it appears decides what it must return.

A scalar subquery returns one value and can go wherever a value can:

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

A subquery after IN returns a single column, used as a list: WHERE id IN (SELECT customer_id FROM orders WHERE status = 'pending').

A derived table is a subquery in FROM, treated like a temporary table. It is how you filter on window results or aggregate an aggregate. Most databases require an alias for it; PostgreSQL made the alias optional in version 16.

EXISTS (subquery) is true when the subquery returns at least one row. It usually refers to the outer row, which makes it a correlated subquery:

SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Conceptually it runs once per outer row, though the planner usually turns it into a join (a semi-join). What you select inside does not matter; SELECT 1 is the convention.

Prefer NOT EXISTS to NOT IN for "rows with no match". NOT IN returns nothing at all if the subquery yields a single NULL, whereas NOT EXISTS behaves as you expect and plans as an efficient anti-join.

Subqueries versus joins: if you need columns from the other table, join. If you only need to know whether related rows exist, EXISTS states the intent and never duplicates outer rows, which a join to a one-to-many table would.

Example

-- Scalar subquery
SELECT name, price FROM products
WHERE price > (SELECT AVG(price) FROM products);    -- avg = 66.87
--  Monitor | 189.00

-- Semi-join: customers with at least one shipped order (no duplicates)
SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o
              WHERE o.customer_id = c.id AND o.status = 'shipped');
--  Ana
--  Ben

-- Anti-join: customers with no orders, NULL-safe
SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
--  Dev

-- Derived table: average spend per ordering customer
SELECT ROUND(AVG(spent), 2) AS avg_spent
FROM (SELECT customer_id, SUM(total) AS spent
      FROM orders GROUP BY customer_id) AS per_customer;
--  184.83

Remember

Scalar subqueries return one value; IN subqueries return one column
A subquery in FROM is a derived table
EXISTS tests for related rows without duplicating outer rows
Use NOT EXISTS, not NOT IN, for 'no matching rows'

Watch out for

A scalar subquery that can return more than one row, which fails at runtime only when the data changes.

Check yourself

Why might a join to orders return Ana twice while WHERE EXISTS (...) returns her once?
What does a correlated subquery refer to?

Official docs: www.postgresql.org/docs/current/functions-subquery.html

Example

Remember

Watch out for

Check yourself

Builds on

Unlocks