Cheatsheets

SQL

SQL queries, joins, aggregation, DDL, and window functions.

Visualize

SQL JOIN

How INNER / LEFT / RIGHT / FULL combine two tables.

SELECT u.name, o.item
FROM users u
INNER JOIN orders o ON u.id = o.uid

users (u)

1 · Ada

2 · Linus

3 · Grace

orders (o)

1 · Book

1 · Pen

2 · Lamp

4 · Hat

result (3 rows)

u.nameo.item

AdaBook

AdaPen

LinusLamp

Green rows are kept; struck-through rows are dropped. Outer joins pad the missing side with NULL.

38 entries

Query6

SELECT col1, col2 FROM t

Select specific columns

SELECT DISTINCT col FROM t

Unique values only

... ORDER BY col DESC

Sort results (descending)

... LIMIT 10 OFFSET 20

Paginate results

SELECT col AS alias

Rename a column in output

SELECT name, count(*) AS n
FROM orders
GROUP BY name
HAVING count(*) > 5
ORDER BY n DESC;

Full grouped + filtered query — the clause execution order matters

Filtering7

WHERE col = 'x'

Filter rows by a condition

WHERE a AND b / OR

Combine conditions

WHERE col IN (1, 2, 3)

Match any value in a list

WHERE col BETWEEN a AND b

Match a range (inclusive)

WHERE col LIKE 'a%'

Pattern match (% = any chars)

WHERE col IS NULL

Match NULL values

WHERE EXISTS (SELECT 1 FROM ...)

Correlated existence check

Joins5

SELECT *
FROM a
INNER JOIN b ON a.id = b.a_id

INNER JOIN — rows matching in both tables

LEFT JOIN b ON a.id = b.a_id

LEFT JOIN — all left rows + matches from right (NULLs when no match)

RIGHT JOIN b ON ...

RIGHT JOIN — all right rows + matches from left

FULL OUTER JOIN b ON ...

FULL OUTER JOIN — all rows from both sides

CROSS JOIN b

Cartesian product — every row in a paired with every row in b

Aggregation7

COUNT(*) / COUNT(col)

Count rows (all) vs count non-NULL values in a column

SUM(col) / AVG(col)

Sum / average — both ignore NULLs

MIN(col) / MAX(col)

Smallest / largest value

GROUP BY col

Group rows for aggregation

HAVING COUNT(*) > 1

Filter groups after aggregation (vs WHERE which filters rows before)

COALESCE(a, b, ...)

Return the first non-NULL value in the list

STRING_AGG(col, ',')

Concatenate grouped values into one string (Postgres / SQL Server)

Modify & DDL8

INSERT INTO t (a) VALUES (1)

Insert a row

UPDATE t SET a = 1 WHERE id = 2

Update rows

DELETE FROM t WHERE id = 2

Delete rows

INSERT INTO t (id, n)
VALUES (1, 'alice')
ON CONFLICT (id)
DO UPDATE SET n = EXCLUDED.n;

Upsert: insert or update on conflict (Postgres)

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Transaction — all-or-nothing group of statements

CREATE TABLE t (id INT PRIMARY KEY)

Create a table

ALTER TABLE t ADD COLUMN c TEXT

Add a column

CREATE INDEX idx ON t(col);
CREATE INDEX idx_multi ON t(a, b);
CREATE UNIQUE INDEX idx_u ON t(col);

Indexes — speed up reads at the cost of write overhead

Window & CTE5

ROW_NUMBER() OVER (ORDER BY col)

Sequential row number — unique per row, no ties

RANK() OVER (PARTITION BY a ORDER BY b)

Rank within each partition — ties get the same rank, next rank skips

SUM(x) OVER (PARTITION BY a)

Running or grouped aggregate without collapsing rows

LAG(col) OVER (ORDER BY t)

Value from the previous row in the window order

WITH recent AS (
  SELECT * FROM orders
  WHERE created > now() - interval '7 days'
)
SELECT * FROM recent;

CTE (Common Table Expression) — named subquery, readable and reusable within the statement