SQL Subqueries Practice

This is the most common subquery bug in production:

-- Suppose orders.customer_id contains NULL values
SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);
-- Returns 0 rows! Even for customers who never ordered.

Why? NOT IN (1, 2, NULL) evaluates as id <> 1 AND id <> 2 AND id <> NULL. The id <> NULL yields UNKNOWN, making the entire expression UNKNOWN. WHERE only includes rows where the condition is TRUE.

Solution: Use NOT EXISTS

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

NOT EXISTS returns TRUE when the subquery finds no rows—NULL values in the subquery don't affect the result. You can also express this as a LEFT JOIN anti-pattern.

See our filtering guide for a comprehensive explanation of three-valued logic and NULL handling. For a quick-reference card of subquery syntax, see the SQL subqueries cheat sheet.

The short answer

Modern query optimizers often transform IN and EXISTS into the same execution plan (semijoin). Choose based on clarity:

The detailed answer

-- These are often optimized identically:

-- IN version
SELECT * FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000);

-- EXISTS version
SELECT * FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id AND o.total > 1000
);

When EXISTS is better:

• Anti-joins: NOT EXISTS is NULL-safe, NOT IN is not
• Early termination: EXISTS can stop at first match
• Correlated conditions: EXISTS naturally expresses "for this row, check if..."

When IN is better:

• Simple set membership: WHERE status IN ('active', 'pending')
• Readability for non-correlated checks

The equivalences

PostgreSQL documents these equivalences:

• x IN (subquery) = x = ANY (subquery)
• x NOT IN (subquery) = x <> ALL (subquery)

Understanding this helps explain the NULL trap: <> ALL means "not equal to every value," and comparing to NULL yields UNKNOWN.

A correlated subquery references the outer query, potentially executing once per row:

-- This can be slow: subquery may run for each customer
SELECT c.id, c.name,
  (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;

Rewrite as JOIN + GROUP BY

-- Usually faster: single pass through orders
SELECT c.id, c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

Why LEFT JOIN? To keep customers with zero orders (they'll show NULL for o.id).

Why COUNT(o.id) not COUNT(*)? COUNT(*) counts NULL rows (returns 1 for customers with no orders), COUNT(o.id) correctly returns 0.

When correlated subqueries are fine

• Small outer result set
• Subquery is indexed and fast
• You need a scalar value that's awkward to express as a join
• The optimizer rewrites it anyway (check EXPLAIN)

LATERAL is a powerful PostgreSQL feature (also in MySQL 8.0.14+, SQL Server as CROSS/OUTER APPLY).

The problem LATERAL solves

"For each customer, get their most recent order."

Without LATERAL, you need window functions:

SELECT c.id, c.name, o.id AS order_id, o.created_at
FROM customers c
LEFT JOIN (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
  FROM orders
) o ON o.customer_id = c.id AND o.rn = 1;

The LATERAL solution

SELECT c.id, c.name, last_order.id AS order_id, last_order.created_at
FROM customers c
LEFT JOIN LATERAL (
  SELECT o.*
  FROM orders o
  WHERE o.customer_id = c.id  -- References c from outer query!
  ORDER BY o.created_at DESC
  LIMIT 1
) last_order ON true;

How it works: The LATERAL keyword allows the subquery to reference columns from preceding FROM items. The subquery runs for each row of customers.

LEFT JOIN LATERAL ... ON true: The ON true is required syntax but always matches—the filtering happens inside the LATERAL subquery.

LATERAL use cases

• Top-N per group (most recent order per customer)
• Expand arrays/JSON into rows
• Complex per-row calculations with multiple columns
• Calling set-returning functions with per-row arguments

A subquery in FROM creates a derived table (also called an inline view):

SELECT country, avg_spend
FROM (
  SELECT country, AVG(total_spend) AS avg_spend
  FROM customers
  GROUP BY country
) t
WHERE avg_spend > 1000;

When to use derived tables

• Filter aggregated results (alternative to HAVING)
• Pre-aggregate before joining (prevent row explosion)
• Compute intermediate results for further processing

Derived tables vs CTEs

-- Derived table
SELECT * FROM (SELECT ... ) t WHERE ...;

-- CTE (equivalent)
WITH t AS (SELECT ...)
SELECT * FROM t WHERE ...;

CTEs are better when:

• You need to reference the result multiple times
• The query is complex and benefits from named steps
• You need recursion (WITH RECURSIVE)

Derived tables are fine when:

• Single use, simple transformation
• You want to keep related logic together

Most developers never use ANY/ALL directly, but understanding them clarifies IN/NOT IN behavior:

-- These are equivalent:
WHERE id IN (1, 2, 3)
WHERE id = ANY (ARRAY[1, 2, 3])

-- These are equivalent:
WHERE id NOT IN (1, 2, 3)
WHERE id <> ALL (ARRAY[1, 2, 3])

ALL for "greater than every value"

-- Products more expensive than ALL clearance items
SELECT * FROM products
WHERE price > ALL (
  SELECT price FROM products WHERE category = 'clearance'
);

-- Equivalent to:
WHERE price > (SELECT MAX(price) FROM products WHERE category = 'clearance')

ANY for "matches at least one"

-- Products cheaper than ANY premium item
SELECT * FROM products
WHERE price < ANY (
  SELECT price FROM products WHERE category = 'premium'
);

-- Equivalent to:
WHERE price < (SELECT MAX(price) FROM products WHERE category = 'premium')

1. Zero rows from NOT IN? Check if the subquery returns any NULL values. Use NOT EXISTS instead.

2. Error: "subquery returns more than one row"? You used a scalar subquery where multiple rows match. Add LIMIT 1 or use IN/EXISTS instead.

3. Slow query? Check if a correlated subquery is running per-row. Consider rewriting as JOIN + GROUP BY.

4. Wrong counts? Scalar subquery counting may differ from JOIN + COUNT due to NULL handling. Use COUNT(column) not COUNT(*) with LEFT JOIN.

5. LATERAL not working? Check database support: PostgreSQL has LATERAL, MySQL 8.0.14+ has LATERAL, SQL Server uses CROSS APPLY / OUTER APPLY.

• PostgreSQL: Subquery Expressions
• PostgreSQL: LATERAL Subqueries
• PostgreSQL: Row and Array Comparisons (ANY/ALL)
• MySQL: Optimizing Subqueries with Semijoin
• MySQL: LATERAL Derived Tables
• SQL Server: APPLY (CROSS APPLY / OUTER APPLY)