The essentials

Quick reference

One focused task per row. Jump to the related section for complete, working examples.

UseSyntaxExamples
Return one valueSELECT name, ( SELECT max(total) FROM orders ) AS largest FROM customers;View examples
Match any returned valueWHERE customer_id IN ( SELECT customer_id FROM vip_customers )View examples
Test whether a row existsWHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.id )View examples
Test whether no row existsWHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.id )View examples
Reference the outer rowSELECT c.id, ( SELECT count(*) FROM orders o WHERE o.customer_id = c.id ) FROM customers c;View examples
Query an inline tableFROM ( SELECT customer_id, sum(total) AS spent FROM orders GROUP BY customer_id ) AS totalsView examples
Reference earlier FROM itemsCROSS JOIN LATERAL ( SELECT * FROM orders WHERE customer_id = c.id ORDER BY created_at DESC LIMIT 1 ) AS latestView examples
Name a query componentWITH active AS ( SELECT * FROM users WHERE active ) SELECT * FROM active;View examples
Chain CTEsWITH base AS (...), summarized AS ( SELECT ... FROM base ) SELECT * FROM summarized;View examples
Force one materializationWITH report AS MATERIALIZED ( SELECT expensive_fn(id) AS value FROM items ) SELECT * FROM report;View examples
Permit parent optimizationWITH filtered AS NOT MATERIALIZED ( SELECT * FROM events ) SELECT * FROM filtered WHERE kind = 'error';View examples
Start a recursive queryWITH RECURSIVE numbers(n) AS ( VALUES (1) UNION ALL SELECT n + 1 FROM numbers WHERE n < 5 ) SELECT * FROM numbers;View examples
Deduplicate recursive resultsseed_query UNION recursive_queryView examples
Consume changed rowsWITH moved AS ( DELETE FROM queue WHERE done RETURNING * ) INSERT INTO archive SELECT * FROM moved;View examples
Update from a CTEWITH changes AS (...) UPDATE products p SET price = c.price FROM changes c WHERE p.id = c.id;View examples

Subqueries produce values, rows, or tables inside a larger statement; common table expressions give named query components to one statement. Choose the form that makes cardinality and intent clear, use EXISTS for existence tests, and understand when PostgreSQL may fold or materialize a CTE.

Step by step

Detailed examples

01

Know the row and column shape a context expects

A scalar subquery must produce one column and at most one row; no row becomes null, while multiple rows are an error. Row and table subqueries have different comparison rules. Make cardinality intentional instead of adding LIMIT 1 without a deterministic ORDER BY or a business reason.

Compare each order with one aggregate value
WITH orders (order_id, total) AS (VALUES (1, 20), (2, 50), (3, 30))
SELECT order_id, total,
       (SELECT AVG(total) FROM orders) AS average_total
FROM orders
ORDER BY order_id;
Output
order_id | total | average_total
---------+-------+--------------
1        | 20    | 33.3333
2        | 50    | 33.3333
3        | 30    | 33.3333
Back to quick reference ↑
02

Prefer EXISTS when only presence matters

EXISTS ignores the selected column values and is true if any row is returned, so SELECT 1 documents intent. NOT EXISTS is null-safe for anti-joins. NOT IN can evaluate to unknown when the right-hand result contains null, causing rows to disappear unexpectedly; exclude nulls explicitly or use NOT EXISTS.

Find customers without orders
SELECT c.customer_id, c.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;
Back to quick reference ↑
03

Correlate only where the outer-row relationship is clear

A correlated subquery can reference columns from an outer query level. PostgreSQL may transform some forms into joins, but conceptually it depends on the current outer row. Aggregates per outer row are concise, while large repeated work may be clearer and faster as a grouped derived table joined once.

Count orders for every customer
SELECT c.customer_id, c.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;
Back to quick reference ↑
04

Use LATERAL for per-row table results

A normal FROM subquery is independent of sibling items. LATERAL lets it reference columns from preceding FROM items, which is useful for top-N-per-parent queries and set-returning functions. LEFT JOIN LATERAL preserves an outer row when the lateral result is empty; CROSS JOIN LATERAL does not.

Latest order for each customer
SELECT c.customer_id, c.name, latest.order_id, latest.created_at
FROM customers AS c
LEFT JOIN LATERAL (
  SELECT o.order_id, o.created_at
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
  ORDER BY o.created_at DESC, o.order_id DESC
  LIMIT 1
) AS latest ON true
ORDER BY c.customer_id;
Back to quick reference ↑
05

Name statement-scoped transformations

WITH can make a multi-stage statement readable and lets later CTEs reference earlier ones. The names exist only for that statement and do not create persistent views. Avoid splitting a simple query into many layers merely for style; each name should clarify a meaningful data shape or reused calculation.

Filter, aggregate, then report
WITH paid_orders AS (
  SELECT customer_id, total
  FROM orders
  WHERE status = 'paid'
), customer_totals AS (
  SELECT customer_id, SUM(total) AS spent
  FROM paid_orders
  GROUP BY customer_id
)
SELECT customer_id, spent
FROM customer_totals
WHERE spent >= 1000
ORDER BY spent DESC;
Back to quick reference ↑
06

Understand folding and materialization

PostgreSQL normally folds a side-effect-free CTE referenced once into the parent query, but commonly materializes one referenced multiple times. MATERIALIZED can preserve one evaluation or an optimization boundary; NOT MATERIALIZED can expose restrictions to joint optimization at the risk of repeated work. Measure both choices rather than treating either keyword as universally faster.

Allow an outer filter to reach the base table
WITH filtered AS NOT MATERIALIZED (
  SELECT event_id, kind, occurred_at
  FROM events
)
SELECT event_id, occurred_at
FROM filtered
WHERE kind = 'error'
ORDER BY occurred_at DESC
LIMIT 100;
Back to quick reference ↑
07

Provide a seed, progress rule, and stopping condition

A recursive CTE starts with its non-recursive term, then repeatedly evaluates the recursive term against the current working rows. UNION ALL retains duplicates and is usually faster; UNION can help suppress repeated states. Hierarchies may still need an explicit visited path or CYCLE clause to prevent loops and a depth guard for operational safety.

Generate a bounded sequence
WITH RECURSIVE numbers(n) AS (
  VALUES (1)
  UNION ALL
  SELECT n + 1
  FROM numbers
  WHERE n < 5
)
SELECT n FROM numbers ORDER BY n;
Output
n
-
1
2
3
4
5
Back to quick reference ↑
08

Connect changes with RETURNING output

A data-modifying statement in WITH executes to completion exactly once and shares the parent statement's snapshot. RETURNING is the communication channel; the target table itself is not the CTE result. Sub-statements run concurrently in an unpredictable order, so do not make two of them modify the same row.

Move completed jobs into an archive
WITH moved AS (
  DELETE FROM job_queue
  WHERE completed_at < CURRENT_DATE - INTERVAL '30 days'
  RETURNING job_id, payload, completed_at
)
INSERT INTO job_archive (job_id, payload, completed_at)
SELECT job_id, payload, completed_at
FROM moved;
Back to quick reference ↑

Keep exploring

More SQL cheat sheets

Sources and further reading

References

Authoritative documentation used to verify and expand this cheat sheet.

  1. PostgreSQL Global Development GroupPostgreSQL: WITH Queriespostgresql.org
  2. PostgreSQL Global Development GroupPostgreSQL: Subquery Expressionspostgresql.org
  3. PostgreSQL Global Development GroupPostgreSQL: Table Expressionspostgresql.org
  4. PostgreSQL Global Development GroupPostgreSQL: SELECTpostgresql.org

Help us improve

Found a typo or missing example?

Tell us what would make this cheat sheet clearer, more complete, or more useful.

Share feedback