143 commands · 9 cheat sheets · SQL
Querying and Joins master quick reference
Browse 143 commands from 9 focused cheat sheets on page 1 of 1. Each example opens its matching detailed section.
Querying and Joins · 19 commands
PostgreSQL Aggregates and Grouping Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Count rows | SELECT COUNT(*) FROM orders; | View examples |
| Count non-null values | SELECT COUNT(shipped_at) FROM orders; | View examples |
| Count distinct values | SELECT COUNT(DISTINCT customer_id) FROM orders; | View examples |
| Sum and average values | SELECT SUM(amount), AVG(amount) FROM payments; | View examples |
| Find range endpoints | SELECT MIN(created_at), MAX(created_at) FROM events; | View examples |
| Group equal values | SELECT status, COUNT(*) FROM orders GROUP BY status; | View examples |
| Group by several expressions | SELECT region, status, SUM(amount)
FROM orders
GROUP BY region, status; | View examples |
| Filter detail rows | SELECT team, SUM(score)
FROM results
WHERE season = 2026
GROUP BY team; | View examples |
| Filter summary groups | SELECT team, SUM(score)
FROM results
GROUP BY team
HAVING SUM(score) >= 100; | View examples |
| Filter one aggregate | COUNT(*) FILTER (WHERE status = 'paid') AS paid_count | View examples |
| Sum selected values | SUM(amount) FILTER (WHERE status = 'paid') AS paid_total | View examples |
| Join text in order | STRING_AGG(name, ', ' ORDER BY name) AS names | View examples |
| Collect values in order | ARRAY_AGG(id ORDER BY created_at, id) AS ids | View examples |
| Require every condition | BOOL_AND(approved) AS all_approved | View examples |
| Choose several summaries | GROUP BY GROUPING SETS ((region, product), (region), ()) | View examples |
| Build hierarchical subtotals | GROUP BY ROLLUP (region, product) | View examples |
| Build all subtotal combinations | GROUP BY CUBE (region, channel) | View examples |
| Identify subtotal columns | GROUPING(region, product) AS grouping_mask | View examples |
| Default an empty total | COALESCE(SUM(amount), 0) AS total | View examples |
Querying and Joins · 15 commands
PostgreSQL CTEs and Subqueries Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Return one value | SELECT name, (
SELECT max(total)
FROM orders
) AS largest
FROM customers; | View examples |
| Match any returned value | WHERE customer_id IN (
SELECT customer_id
FROM vip_customers
) | View examples |
| Test whether a row exists | WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
) | View examples |
| Test whether no row exists | WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
) | View examples |
| Reference the outer row | SELECT c.id, (
SELECT count(*)
FROM orders o
WHERE o.customer_id = c.id
)
FROM customers c; | View examples |
| Query an inline table | FROM (
SELECT customer_id, sum(total) AS spent
FROM orders
GROUP BY customer_id
) AS totals | View examples |
| Reference earlier FROM items | CROSS JOIN LATERAL (
SELECT *
FROM orders
WHERE customer_id = c.id
ORDER BY created_at DESC
LIMIT 1
) AS latest | View examples |
| Name a query component | WITH active AS (
SELECT *
FROM users
WHERE active
)
SELECT *
FROM active; | View examples |
| Chain CTEs | WITH base AS (...), summarized AS (
SELECT ...
FROM base
)
SELECT *
FROM summarized; | View examples |
| Force one materialization | WITH report AS MATERIALIZED (
SELECT expensive_fn(id) AS value
FROM items
)
SELECT *
FROM report; | View examples |
| Permit parent optimization | WITH filtered AS NOT MATERIALIZED (
SELECT *
FROM events
)
SELECT *
FROM filtered
WHERE kind = 'error'; | View examples |
| Start a recursive query | WITH RECURSIVE numbers(n) AS (
VALUES (1) UNION ALL
SELECT n + 1
FROM numbers
WHERE n < 5
)
SELECT *
FROM numbers; | View examples |
| Deduplicate recursive results | seed_query UNION recursive_query | View examples |
| Consume changed rows | WITH moved AS (
DELETE FROM queue
WHERE done
RETURNING *
)
INSERT INTO archive
SELECT *
FROM moved; | View examples |
| Update from a CTE | WITH changes AS (...)
UPDATE products p
SET price = c.price
FROM changes c
WHERE p.id = c.id; | View examples |
Querying and Joins · 13 commands
PostgreSQL Cursors and Batched Fetching Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Declare forward-only cursor | DECLARE export_rows NO SCROLL CURSOR FOR
SELECT id, created_at
FROM app.events
ORDER BY id; | View examples |
| Fetch a bounded batch | FETCH FORWARD 500 FROM export_rows; | View examples |
| Close a cursor | CLOSE export_rows; | View examples |
| Inspect session cursors | SELECT name, is_holdable, is_scrollable, creation_time
FROM pg_cursors; | View examples |
| Declare scrolling cursor | DECLARE audit_scan SCROLL CURSOR FOR
SELECT event_id, occurred_at
FROM app.audit_events
ORDER BY event_id; | View examples |
| Fetch one absolute row | FETCH ABSOLUTE 1000 FROM audit_scan; | View examples |
| Move without returning rows | MOVE FORWARD 500 FROM audit_scan; | View examples |
| Keep a cursor after commit | DECLARE export_hold NO SCROLL CURSOR
WITH HOLD FOR
SELECT id, payload
FROM app.events
ORDER BY id; | View examples |
| Lock rows as fetched | DECLARE jobs NO SCROLL CURSOR FOR
SELECT job_id
FROM app.jobs
WHERE state = 'ready'
ORDER BY job_id FOR
UPDATE SKIP LOCKED; | View examples |
| Update current cursor row | UPDATE app.jobs
SET state = 'claimed'
WHERE CURRENT OF jobs; | View examples |
| Return a refcursor | OPEN c FOR
SELECT id
FROM app.events
ORDER BY id; RETURN c; | View examples |
| Fetch a resumable keyset batch | SELECT id, payload
FROM app.events
WHERE id > 50000
ORDER BY id
LIMIT 500; | View examples |
| Bound cursor transaction time | SET LOCAL idle_in_transaction_session_timeout = '60s'; | View examples |
Querying and Joins · 16 commands
PostgreSQL LATERAL Joins and Set-Returning Functions Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Join a correlated subquery | SELECT c.id, x.id
FROM app.customers c
CROSS JOIN LATERAL (
SELECT id
FROM app.orders
WHERE customer_id = c.id
) x; | View examples |
| Return top one per row | SELECT c.id, x.id
FROM app.customers c
CROSS JOIN LATERAL (
SELECT id
FROM app.orders
WHERE customer_id = c.id
ORDER BY created_at DESC
LIMIT 1
) x; | View examples |
| Make lateral explicit | SELECT t.id, f.value
FROM app.things t
CROSS JOIN LATERAL app.expand(t.payload) AS f(value); | View examples |
| Preserve unmatched outer rows | SELECT c.id, x.id
FROM app.customers c
LEFT JOIN LATERAL (
SELECT id
FROM app.orders
WHERE customer_id = c.id
LIMIT 1
) x
ON true; | View examples |
| Filter after optional expansion | SELECT c.id, x.id
FROM app.customers c
LEFT JOIN LATERAL app.lookup(c.id) x
ON true
WHERE x.id IS NULL; | View examples |
| Generate integers | SELECT n FROM generate_series(1, 10, 1) AS g(n); | View examples |
| Generate daily timestamps | SELECT ts
FROM generate_series(timestamp '2026-08-01', timestamp '2026-08-07', interval '1 day') AS g(ts); | View examples |
| Expand an array | SELECT value FROM unnest(ARRAY[10,20,30]) AS u(value); | View examples |
| Add ordinality | SELECT value, position
FROM unnest(ARRAY[10,20])
WITH ORDINALITY AS u(value, position); | View examples |
| Zip arrays in FROM | SELECT *
FROM unnest(ARRAY[1,2], ARRAY['a','b']) AS u(id, label); | View examples |
| Expand a JSON array | SELECT value
FROM jsonb_array_elements('[1,2,3]'::jsonb) AS j(value); | View examples |
| Project JSON objects | SELECT *
FROM jsonb_to_recordset('[{"id":1}]'::jsonb) AS x(id integer); | View examples |
| Declare estimated SRF rows | ALTER FUNCTION app.recent_orders(bigint, integer) ROWS 10; | View examples |
| Call a table function | SELECT * FROM app.recent_orders(42, 5); | View examples |
| Inspect without execution | EXPLAIN
SELECT *
FROM app.customers c
CROSS JOIN LATERAL app.recent_orders(c.customer_id, 3) r; | View examples |
| Measure lateral loops | EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM app.customers c
CROSS JOIN LATERAL app.recent_orders(c.customer_id, 3) r; | View examples |
Querying and Joins · 11 commands
PostgreSQL NULLs and Conditional Expressions Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Test for null | WHERE deleted_at IS NULL | View examples |
| Test for a value | WHERE email IS NOT NULL | View examples |
| Null-safe inequality | old_value IS DISTINCT FROM new_value | View examples |
| Null-safe equality | left_value IS NOT DISTINCT FROM right_value | View examples |
| First non-null value | COALESCE(display_name, username, 'Anonymous') | View examples |
| Convert equality to null | NULLIF(denominator, 0) | View examples |
| Searched CASE | CASE WHEN score >= 90 THEN 'A' WHEN score >= 80 THEN 'B' ELSE 'C' END | View examples |
| Simple CASE | CASE status WHEN 'open' THEN 1 WHEN 'closed' THEN 0 ELSE NULL END | View examples |
| Count non-null values | COUNT(completed_at) | View examples |
| Count all rows | COUNT(*) | View examples |
| Place nulls last | ORDER BY priority DESC NULLS LAST | View examples |
Querying and Joins · 16 commands
PostgreSQL Recursive Queries and Graph Traversal Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Generate a bounded sequence | WITH RECURSIVE t(n) AS (
VALUES (1) UNION ALL
SELECT n + 1
FROM t
WHERE n < 10
)
SELECT n
FROM t; | View examples |
| Walk children | WITH RECURSIVE t(id) AS (
VALUES (42::bigint) UNION ALL
SELECT e.to_id
FROM app.edges e
JOIN t
ON e.from_id=t.id
)
SELECT *
FROM t; | View examples |
| Remove duplicate rows | WITH RECURSIVE t(id) AS (
VALUES (1::bigint) UNION
SELECT to_id
FROM app.edges
JOIN t
ON from_id=t.id
)
SELECT id
FROM t; | View examples |
| Cap traversal depth | WITH RECURSIVE t(n,d) AS (
VALUES (1,0) UNION ALL
SELECT n+1,d+1
FROM t
WHERE d<20
)
SELECT *
FROM t; | View examples |
| Set a statement budget | SET LOCAL statement_timeout = '10s'; | View examples |
| Append to a path | SELECT ARRAY[1,2] || 3; | View examples |
| Test path membership | SELECT 3 = ANY (ARRAY[1,2,3]); | View examples |
| Declare cycle detection | WITH RECURSIVE t(id) AS (
VALUES (1::bigint) UNION ALL
SELECT to_id
FROM app.edges
JOIN t
ON from_id=t.id
) CYCLE id
SET cyc
USING path
SELECT *
FROM t; | View examples |
| Filter cycle-closing rows | SELECT id, path FROM walk WHERE NOT is_cycle; | View examples |
| Create depth-first order | WITH RECURSIVE t(id) AS (
VALUES (1::bigint) UNION ALL
SELECT to_id
FROM app.edges
JOIN t
ON from_id=t.id
) SEARCH DEPTH FIRST BY id
SET ord
SELECT *
FROM t; | View examples |
| Create breadth-first order | WITH RECURSIVE t(id) AS (
VALUES (1::bigint) UNION ALL
SELECT to_id
FROM app.edges
JOIN t
ON from_id=t.id
) SEARCH BREADTH FIRST BY id
SET ord
SELECT *
FROM t; | View examples |
| Order final output | SELECT id FROM tree ORDER BY bfs_order, id; | View examples |
| Count reachable vertices | SELECT count(*) FROM reachable; | View examples |
| Aggregate by depth | SELECT depth, count(*)
FROM walk
GROUP BY depth
ORDER BY depth; | View examples |
| Index outbound edges | CREATE INDEX CONCURRENTLY edges_from_id_idx
ON app.edges (from_id); | View examples |
| Plan without traversal | EXPLAIN
WITH RECURSIVE t(n) AS (
VALUES (1) UNION ALL
SELECT n+1
FROM t
WHERE n<10
)
SELECT *
FROM t; | View examples |
Querying and Joins · 16 commands
PostgreSQL Window Functions Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Count without grouping | COUNT(*) OVER () AS row_count | View examples |
| Total each partition | SUM(amount) OVER (PARTITION BY department) AS department_total | View examples |
| Number rows | ROW_NUMBER() OVER (PARTITION BY team
ORDER BY score DESC, id
) AS position | View examples |
| Rank with gaps | RANK() OVER (ORDER BY score DESC) AS rank | View examples |
| Rank without gaps | DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank | View examples |
| Split into buckets | NTILE(4) OVER (ORDER BY score DESC) AS quartile | View examples |
| Read the previous row | LAG(revenue) OVER (PARTITION BY product_id
ORDER BY month
) AS previous_revenue | View examples |
| Read the next row | LEAD(revenue) OVER (PARTITION BY product_id
ORDER BY month
) AS next_revenue | View examples |
| Read the first value | FIRST_VALUE(price) OVER (PARTITION BY sku
ORDER BY changed_at
) AS initial_price | View examples |
| Read the partition's last value | LAST_VALUE(price) OVER (PARTITION BY sku
ORDER BY changed_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS final_price | View examples |
| Calculate a running total | SUM(amount) OVER (
ORDER BY occurred_at, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total | View examples |
| Calculate a moving average | AVG(amount) OVER (
ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS three_row_average | View examples |
| Calculate percent of total | 100.0 * amount / NULLIF(SUM(amount) OVER (), 0) AS percent_total | View examples |
| Reuse a window definition | WINDOW by_account AS (PARTITION BY account_id
ORDER BY occurred_at
) | View examples |
| Filter a window result | WITH ranked AS (...)
SELECT *
FROM ranked
WHERE row_number = 1; | View examples |
| Order the final result | SELECT ... ORDER BY department, rank, employee_id; | View examples |
Querying and Joins · 16 commands
SQL Joins Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Keep matching rows | SELECT o.id, c.name
FROM orders AS o
INNER JOIN customers AS c
ON c.id = o.customer_id; | View examples |
| Alias joined tables | SELECT o.id, c.name
FROM orders AS o
JOIN customers AS c
ON c.id = o.customer_id; | View examples |
| Join shared column names | SELECT order_id, status
FROM orders
JOIN shipments
USING (order_id); | View examples |
| Keep every left row | SELECT c.name, o.id
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id; | View examples |
| Find unmatched left rows | SELECT c.id
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id
WHERE o.id IS NULL; | View examples |
| Filter joined matches | SELECT c.name, o.id
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id AND o.status = 'paid'; | View examples |
| Keep every right row | SELECT c.name, o.id
FROM customers AS c
RIGHT JOIN orders AS o
ON o.customer_id = c.id; | View examples |
| Keep both sides | SELECT a.email, s.email
FROM app_users AS a
FULL OUTER JOIN subscribers AS s
ON s.email = a.email; | View examples |
| Merge an outer-join key | SELECT COALESCE(a.email, s.email) AS email
FROM app_users AS a
FULL JOIN subscribers AS s
ON s.email = a.email; | View examples |
| Create every combination | SELECT c.color, s.size
FROM colors AS c
CROSS JOIN sizes AS s; | View examples |
| Join a table to itself | SELECT e.name, m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m
ON m.id = e.manager_id; | View examples |
| Chain several joins | SELECT o.id, c.name, p.name
FROM orders AS o
JOIN customers AS c
ON c.id = o.customer_id
JOIN products AS p
ON p.id = o.product_id; | View examples |
| Match multiple columns | SELECT i.sku, p.price
FROM inventory AS i
JOIN prices AS p
ON p.sku = i.sku AND p.region = i.region; | View examples |
| Count related rows | SELECT c.id, COUNT(o.id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id
GROUP BY c.id; | View examples |
| Sum related values | SELECT c.id, COALESCE(SUM(o.total), 0) AS revenue
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id
GROUP BY c.id; | View examples |
| Join a correlated subquery | SELECT c.id, latest.id
FROM customers AS c
LEFT JOIN LATERAL (
SELECT o.id
FROM orders AS o
WHERE o.customer_id = c.id
ORDER BY o.created_at DESC
LIMIT 1
) AS latest
ON true; | View examples |
Querying and Joins · 21 commands
SQL SELECT Queries Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Select columns | SELECT id, name FROM customers; | View examples |
| Rename an output column | SELECT total AS order_total FROM orders; | View examples |
| Remove duplicate rows | SELECT DISTINCT country FROM customers; | View examples |
| Filter by comparison | SELECT * FROM orders WHERE total >= 100; | View examples |
| Require every condition | SELECT *
FROM orders
WHERE status = 'paid' AND total >= 100; | View examples |
| Accept either condition | SELECT *
FROM orders
WHERE status = 'new' OR status = 'processing'; | View examples |
| Match a value list | SELECT *
FROM orders
WHERE status IN ('new', 'processing'); | View examples |
| Match an inclusive range | SELECT * FROM orders WHERE total BETWEEN 50 AND 100; | View examples |
| Find missing values | SELECT * FROM customers WHERE phone IS NULL; | View examples |
| Match a text pattern | SELECT * FROM customers WHERE name LIKE 'A%'; | View examples |
| Sort the result | SELECT * FROM orders ORDER BY created_at DESC, id ASC; | View examples |
| Limit returned rows | SELECT *
FROM orders
ORDER BY created_at DESC
FETCH FIRST 10 ROWS ONLY; | View examples |
| Skip result rows | SELECT *
FROM orders
ORDER BY id
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY; | View examples |
| Count rows | SELECT COUNT(*) AS order_count FROM orders; | View examples |
| Sum a column | SELECT SUM(total) AS revenue
FROM orders
WHERE status = 'paid'; | View examples |
| Aggregate by group | SELECT status, COUNT(*) FROM orders GROUP BY status; | View examples |
| Filter grouped results | SELECT customer_id, SUM(total)
FROM orders
GROUP BY customer_id
HAVING SUM(total) >= 500; | View examples |
| Join matching rows | SELECT o.id, c.name
FROM orders AS o
INNER JOIN customers AS c
ON c.id = o.customer_id; | View examples |
| Keep every left row | SELECT c.name, o.id
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id; | View examples |
| Test for related rows | SELECT *
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.id
); | View examples |
| Name an intermediate query | WITH paid_orders AS (
SELECT *
FROM orders
WHERE status = 'paid'
)
SELECT *
FROM paid_orders; | View examples |