SQLCheat Sheet

SQL Subqueries Cheat Sheet

Quick-reference for subquery patterns: scalar, IN, EXISTS, correlated, derived tables, and LATERAL. Each section includes copy-ready snippets with inline output comments.

Scalar Subqueries

A scalar subquery returns exactly one value (one row, one column). Use it anywhere a single value is expected.

Scalar in SELECT

-- Order count per customer (computed per row)
SELECT
  c.name,
  (SELECT COUNT(*)
   FROM orders o
   WHERE o.customer_id = c.id) AS order_count
FROM customers c;

Scalar in WHERE

-- Products priced above average
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

Error: multiple rows returned

-- WRONG: subquery returns multiple rows for scalar context
SELECT name
FROM products
WHERE price = (SELECT price FROM products WHERE category = 'sale');
-- ERROR if more than one sale product exists!
-- Fix: use IN, or add LIMIT 1, or use MIN/MAX

Scalar subqueries must return exactly 0 or 1 row. Multiple rows cause a runtime error.

IN / NOT IN Subqueries

Filter rows by set membership. IN matches any value; NOT IN excludes all values.

IN subquery

-- Orders from US customers
SELECT * FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE country = 'US'
);

NOT IN (beware of NULLs!)

-- DANGEROUS: if subquery returns any NULL, result is empty
SELECT * FROM customers
WHERE id NOT IN (
  SELECT customer_id FROM orders
);
-- If orders.customer_id has a NULL, this returns 0 rows!

NOT IN + NULL = zero rows. Use NOT EXISTS instead for safe anti-joins.

Safe NOT IN (filter NULLs)

-- Filter out NULLs explicitly
SELECT * FROM customers
WHERE id NOT IN (
  SELECT customer_id FROM orders
  WHERE customer_id IS NOT NULL
);

EXISTS / NOT EXISTS

Check whether at least one matching row exists. NULL-safe and can short-circuit at the first match.

EXISTS (customers with orders)

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

SELECT 1 inside EXISTS is conventional — EXISTS ignores what you select.

NOT EXISTS (anti-join, NULL-safe)

-- Customers who have never ordered
SELECT c.*
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.id
);

EXISTS with additional filters

-- Customers with at least one order over $1000
SELECT c.name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.id
    AND o.amount > 1000
);

Correlated vs Uncorrelated Subqueries

An uncorrelated subquery runs once. A correlated subquery references the outer query and may run once per row.

Uncorrelated (runs once)

-- Subquery is independent of the outer query
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);
-- The AVG is computed once, then compared to every row

Correlated (runs per row)

-- Subquery references c.id from the outer query
SELECT c.name,
  (SELECT MAX(o.amount)
   FROM orders o
   WHERE o.customer_id = c.id) AS max_order
FROM customers c;
-- Subquery evaluates for EACH customer

Rewrite correlated as JOIN

-- Usually faster: single pass with GROUP BY
SELECT c.name, MAX(o.amount) AS max_order
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

Correlated subqueries can be slow for large tables. Rewrite as JOIN + GROUP BY when possible.

Derived Tables (Subquery in FROM)

A subquery in FROM creates a temporary table (derived table) that you can query like any other table.

Filter aggregated results

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

Derived tables must have an alias (the "t" after the closing parenthesis).

Pre-aggregate before joining

-- Prevent row multiplication by aggregating first
SELECT c.name, ot.total_revenue
FROM customers c
JOIN (
  SELECT customer_id, SUM(amount) AS total_revenue
  FROM orders
  GROUP BY customer_id
) ot ON ot.customer_id = c.id;

Subqueries in SELECT

A scalar subquery in SELECT computes a value for each row of the outer query.

Inline lookup

SELECT
  o.id,
  o.amount,
  (SELECT c.name
   FROM customers c
   WHERE c.id = o.customer_id) AS customer_name
FROM orders o;

When to rewrite as JOIN

-- Multiple scalar subqueries on the same table = inefficient
-- SLOW:
SELECT o.id,
  (SELECT c.name FROM customers c WHERE c.id = o.customer_id),
  (SELECT c.email FROM customers c WHERE c.id = o.customer_id)
FROM orders o;

-- FAST: single JOIN
SELECT o.id, c.name, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id;

If you need multiple columns from the same table, always use a JOIN instead of multiple scalar subqueries.

ALL and ANY Operators

Compare a value against all or any values from a subquery. IN is shorthand for = ANY; NOT IN is shorthand for <> ALL.

> ALL (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: price > (SELECT MAX(price) ... )

< ANY (less than at least one value)

-- Products cheaper than at least one premium item
SELECT * FROM products
WHERE price < ANY (
  SELECT price FROM products
  WHERE category = 'premium'
);
-- Equivalent: price < (SELECT MAX(price) ... )

Equivalences with IN

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

-- These are identical (with the same NULL trap):
WHERE id NOT IN (1, 2, 3)
WHERE id <> ALL (ARRAY[1, 2, 3])

LATERAL Joins

LATERAL lets a subquery in FROM reference columns from preceding tables. Use it for "top-N per row" patterns.

Most recent order per customer

-- PostgreSQL / MySQL 8.0.14+
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
  ORDER BY o.created_at DESC
  LIMIT 1
) last_order ON true;

ON true is required syntax — the filtering happens inside the LATERAL subquery.

SQL Server equivalent: CROSS APPLY / OUTER APPLY

-- OUTER APPLY = LEFT JOIN LATERAL
SELECT c.id, c.name, lo.id AS order_id
FROM customers c
OUTER APPLY (
  SELECT TOP 1 o.*
  FROM orders o
  WHERE o.customer_id = c.id
  ORDER BY o.created_at DESC
) lo;

Top 3 per customer

SELECT c.name, recent.*
FROM customers c
LEFT JOIN LATERAL (
  SELECT o.id, o.amount, o.created_at
  FROM orders o
  WHERE o.customer_id = c.id
  ORDER BY o.created_at DESC
  LIMIT 3
) recent ON true;

When to Use Subquery vs JOIN vs CTE

Guidelines for choosing the right tool based on readability, performance, and reuse.

Decision guide

-- Use IN / EXISTS when:
--   Checking set membership or existence
--   Anti-join needed (NOT EXISTS is NULL-safe)

-- Use JOIN when:
--   You need columns from both tables
--   Multiple scalar subqueries on same table
--   Performance matters (optimizer handles joins well)

-- Use CTE when:
--   Multiple steps / complex logic
--   Same derived data referenced more than once
--   Recursive queries needed

Same query, three styles

-- Subquery style
SELECT * FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE country = 'US'
);

-- JOIN style
SELECT o.*
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'US';

-- CTE style
WITH us_customers AS (
  SELECT id FROM customers WHERE country = 'US'
)
SELECT o.*
FROM orders o
JOIN us_customers uc ON uc.id = o.customer_id;

Modern optimizers often produce the same execution plan for all three. Choose based on readability.

Learn SQL in Depth

SQL Filtering Practice →SQL Joins Practice →SQL CTEs Practice →

Warm-up1 / 2

Can you write this from memory?

Select all products where the price is greater than the average price of all products.

Start Practicing SQL

Free daily exercises with spaced repetition. No credit card required.

← Back to SQL Syntax Practice