Subqueries — Scalar, IN, EXISTS, Correlated

TL;DR

A subquery is a SELECT nested inside another statement. There are four shapes you’ll meet:

• Scalar subquery — returns one row, one column. Used wherever a value is expected.
• IN / NOT IN subquery — returns one column, any number of rows. Membership test.
• EXISTS / NOT EXISTS subquery — returns rows or doesn’t. Existence test.
• Derived table (subquery in FROM) — returns a multi-column result treated as a virtual table.

A correlated subquery is any of the above where the inner query references columns from the outer query. They can be elegant; they can also turn an O(n) query into O(n²) because the inner query reruns per outer row. The fix is usually a JOIN, a window function, or a CTE.

Sample data

CREATE TABLE users (id int PRIMARY KEY, name text, country text);
CREATE TABLE orders (id int PRIMARY KEY, user_id int, total numeric, placed date);

INSERT INTO users VALUES
  (1, 'Alice',  'US'), (2, 'Bob', 'US'),
  (3, 'Carol',  'UK'), (4, 'Dinesh', 'IN');
INSERT INTO orders VALUES
  (100, 1, 19.99, '2026-04-01'), (101, 1, 42.00, '2026-04-15'),
  (102, 2,  7.50, '2026-04-20'), (103, 3, 99.00, '2026-04-22');

1. Scalar subquery

Returns exactly one value (one row, one column). Usable wherever an expression is allowed.

SELECT name,
       (SELECT AVG(total) FROM orders) AS overall_avg
FROM   users;

+--------+-------------+
| name   | overall_avg |
+--------+-------------+
| Alice  |    42.12    |
| Bob    |    42.12    |
| Carol  |    42.12    |
| Dinesh |    42.12    |
+--------+-------------+

The same value appears for every row — the inner query runs once (uncorrelated). If the subquery returns more than one row at runtime, you get an error.

2. IN subquery

Tests membership in a one-column result set.

-- Users who have placed at least one order
SELECT name FROM users
WHERE  id IN (SELECT user_id FROM orders);

Equivalent to a JOIN followed by DISTINCT, but IN is usually clearer when you don’t need columns from orders.

-- Users who have NEVER ordered
SELECT name FROM users
WHERE  id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);

The NOT IN NULL trap. x NOT IN (a, b, NULL) is never true because x = NULL is unknown. So if the subquery returns even a single NULL, your NOT IN returns zero rows. Always guard with WHERE col IS NOT NULL inside the subquery, or use NOT EXISTS instead.

3. EXISTS subquery

Tests whether a subquery returns at least one row. The subquery’s columns don’t matter — convention is SELECT 1.

SELECT name FROM users u
WHERE  EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

The inner query references u.id — that makes it correlated. For each user, the planner checks whether any matching order exists. With an index on orders.user_id, this is O(n log m).

NOT EXISTS is the safe sibling of NOT IN — it doesn’t have the NULL trap:

-- Users who have NEVER ordered (NULL-safe)
SELECT name FROM users u
WHERE  NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

This always works, regardless of NULLs. Default to EXISTS over IN unless you’re certain there are no NULLs.

4. Derived table — subquery in FROM

SELECT u.country, AVG(t.total_spent) AS avg_user_spend
FROM   users u
JOIN   (SELECT user_id, SUM(total) AS total_spent
        FROM   orders
        GROUP  BY user_id) t
       ON t.user_id = u.id
GROUP  BY u.country;

The inner SELECT is a virtual table aliased as t. This is the standard way to “aggregate first, then join” — avoiding the many-to-one-side-multiplication problem when joining a base table to its own aggregations.

CTEs (SQL 106) usually read better for the same job; derived tables predate CTEs and still work everywhere.

Correlated subqueries — the cost

A correlated subquery references the outer row, so it logically runs once per outer row.

-- For each user, attach their total spend
SELECT u.name,
       (SELECT COALESCE(SUM(total), 0)
        FROM   orders o
        WHERE  o.user_id = u.id) AS total_spend
FROM   users u;

For 4 users, this runs the inner query 4 times — fine. For 1M users without an index on orders.user_id, it’s 1M sequential scans. You will notice. The same query as a JOIN aggregates once:

SELECT u.name, COALESCE(SUM(o.total), 0) AS total_spend
FROM   users u
LEFT   JOIN orders o ON o.user_id = u.id
GROUP  BY u.id, u.name;

A modern planner (Postgres 13+, Snowflake, BigQuery) can sometimes “decorrelate” the subquery and rewrite it as a JOIN. Don’t rely on it — write the JOIN.

Subquery vs JOIN vs CTE — when to use which

The biggest single mistake: writing a correlated subquery in SELECT to “attach a value per row” when a JOIN (or window function) would do it once across all rows. Symptom: query is fast in dev (small data), slow in prod.

ANY, ALL, SOME

Underused but powerful comparison-with-subquery operators.

-- Orders with total > every user's average order
SELECT * FROM orders
WHERE  total > ALL (SELECT AVG(total) FROM orders GROUP BY user_id);

-- Orders with total > any user's average
SELECT * FROM orders
WHERE  total > ANY (SELECT AVG(total) FROM orders GROUP BY user_id);

x = ANY (subquery) is identical to x IN (subquery). x <> ALL (sq) is identical to x NOT IN (sq) — but, like NOT IN, has the NULL trap.

Common pitfalls

• NOT IN with NULLs returns nothing. Use NOT EXISTS.
• Correlated subquery in SELECT over many rows. O(n²) waiting to bite. Rewrite as JOIN or window function.
• Returning more than one row from a scalar subquery. Runtime error (more than one row returned by a subquery used as an expression). Add LIMIT 1 only if you’re sure which row you want.
• Forgetting that EXISTS doesn’t care about the subquery’s columns. SELECT * vs SELECT 1 — both equivalent for EXISTS. Don’t optimize the inner SELECT list; the planner ignores it.
• Filtering inside a subquery vs outside. JOIN (SELECT … WHERE x>5) AS t filters inside the derived table; JOIN tbl AS t … WHERE t.x > 5 may or may not push down. For Postgres, planner usually pushes; for some engines, write it inside.

Production patterns for ML

1. Anti-join for “users who didn’t do X yet”. When building a target audience for a model:

-- Users who signed up >= 30 days ago and have NEVER converted
SELECT u.id
FROM   users u
WHERE  u.signed_up_at <= CURRENT_DATE - INTERVAL '30 days'
  AND  NOT EXISTS (
        SELECT 1 FROM conversions c WHERE c.user_id = u.id
       );

This is the canonical “not yet converted” cohort filter. Always use NOT EXISTS, never NOT IN.

2. Lateral subquery for “top N per group”. Sometimes a window function isn’t ideal — Postgres LATERAL lets you correlate cleanly:

SELECT u.id,
       o.id     AS last_order_id,
       o.total  AS last_order_total
FROM   users u
LEFT   JOIN LATERAL (
    SELECT id, total
    FROM   orders
    WHERE  user_id = u.id
    ORDER  BY placed DESC
    LIMIT  1
) o ON true;

For each user, fetch their most recent order. This compiles to an index-supported nested-loop join with one lookup per user — cleaner than the equivalent window-function-then-filter pattern when the per-group fetch is small.

Resources

• PostgreSQL — subqueries — postgresql.org/docs
• PostgreSQL — LATERAL subqueries — postgresql.org/docs
• Mode Analytics — subqueries — mode.com/sql-tutorial
• Use The Index, Luke — use-the-index-luke.com — when subqueries get slow, indexing the correlated column is usually the fix.