Production Reference

SQL Cheat Sheet

Queries, joins, aggregations, indexes, window functions, and performance patterns for debugging relational data in production.

Command-firstProduction notesSecurity warningsHardened patterns

Basic Queries

9 commands

SELECT * FROM users

All rows, all columns

SELECT name, email FROM users

Specific columns

SELECT DISTINCT city FROM users

Unique values

WHERE age > 25

Filter rows

WHERE name LIKE '%ali%'

Pattern match

WHERE status IN ('active','vip')

Match list

WHERE email IS NULL

Check for NULL

ORDER BY created_at DESC

Sort descending

LIMIT 10 OFFSET 20

Pagination

JOINs

6 commands

INNER JOIN orders ON u.id = o.user_id

Match both sides

LEFT JOIN orders ON ...

All from left + matches

RIGHT JOIN orders ON ...

All from right + matches

FULL OUTER JOIN orders ON ...

All from both sides

CROSS JOIN products

Cartesian product

LEFT JOIN ... WHERE o.id IS NULL

Users WITHOUT orders

Aggregations

7 commands

COUNT(*)

Count rows

COUNT(DISTINCT col)

Count unique values

SUM(amount)

Total

AVG(amount)

Average

MAX(amount) / MIN(amount)

Max / Min

GROUP BY category

Group rows

HAVING COUNT(*) > 5

Filter groups

Window Functions

6 commands

ROW_NUMBER() OVER (ORDER BY id)

Sequential number

RANK() OVER (ORDER BY score DESC)

Rank with gaps

DENSE_RANK() OVER (...)

Rank without gaps

LAG(col, 1) OVER (ORDER BY date)

Previous row value

LEAD(col, 1) OVER (ORDER BY date)

Next row value

SUM(amt) OVER (PARTITION BY user_id)

Running total per user

CTEs & Subqueries

4 commands

WITH cte AS (SELECT ...)

Common Table Expression

WHERE id IN (SELECT ...)

Subquery filter

SELECT *, (SELECT ...) AS x

Scalar subquery

WITH RECURSIVE cte AS (...)

Recursive CTE (trees)

Data Modification

5 commands

INSERT INTO t (a,b) VALUES (1,2)

Insert row

INSERT INTO t SELECT ... FROM s

Insert from select

UPDATE users SET name='B' WHERE id=1

Update rows

DELETE FROM users WHERE id=1

Delete rows

TRUNCATE TABLE users

Delete all rows (fast)

Indexes & Performance

5 commands

CREATE INDEX idx ON t(col)

Create index

CREATE UNIQUE INDEX idx ON t(col)

Unique index

EXPLAIN ANALYZE SELECT ...

Query execution plan

CREATE INDEX idx ON t(a, b)

Composite index

DROP INDEX idx

Remove index

Schema

5 commands

CREATE TABLE t (id SERIAL PRIMARY KEY, ...)

Create table

ALTER TABLE t ADD COLUMN col TYPE

Add column

ALTER TABLE t DROP COLUMN col

Remove column

ALTER TABLE t RENAME TO new_t

Rename table

DROP TABLE t

Delete table

Advanced Window Functions

7 commands

NTILE(4) OVER (ORDER BY score)

Split into 4 equal buckets

PERCENT_RANK() OVER (...)

Percentile rank (0-1)

CUME_DIST() OVER (...)

Cumulative distribution

FIRST_VALUE(col) OVER (... ROWS UNBOUNDED PRECEDING)

First value in window

LAST_VALUE(col) OVER (... ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

Last value in window

SUM(amt) OVER (ORDER BY date ROWS 6 PRECEDING)

Rolling 7-day sum

AVG(price) OVER (PARTITION BY category ORDER BY date ROWS 2 PRECEDING)

Moving average per group

JSON Operations (PostgreSQL)

7 commands

data->>'name'

Get JSON text value

data->'address'->>'city'

Nested JSON access

data @> '{"status":"active"}'

JSON containment check

jsonb_array_elements(data->'items')

Unnest JSON array

jsonb_set(data, '{key}', '"val"')

Update JSON field

row_to_json(t)

Row to JSON object

json_agg(col)

Aggregate rows to JSON array

Recursive CTEs

5 commands

WITH RECURSIVE cte AS (base UNION ALL recursive)

Recursive CTE pattern

-- Employee hierarchy (find all reports)

Manager-employee tree

-- Category tree (parent_id self-join)

Nested categories

-- Generate series (1,2,3...N)

Number generation

-- Graph traversal (shortest path)

BFS/DFS in SQL

Performance Tuning

8 commands

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...

Full query plan

CREATE INDEX CONCURRENTLY idx ON t(col)

Non-blocking index

CREATE INDEX idx ON t(a) WHERE status='active'

Partial index

CREATE INDEX idx ON t USING GIN(col)

Full-text/JSONB index

CREATE INDEX idx ON t(a) INCLUDE (b,c)

Covering index

VACUUM ANALYZE t

Update table statistics

SET enable_seqscan = off

Force index scan (debug only)

SELECT pg_size_pretty(pg_total_relation_size('t'))

Table size

Transactions & Locking

7 commands

BEGIN; ... COMMIT;

Explicit transaction

ROLLBACK;

Abort transaction

SAVEPOINT sp1; ... ROLLBACK TO sp1;

Nested savepoint

SELECT ... FOR UPDATE

Row-level write lock

SELECT ... FOR SHARE

Row-level read lock

SELECT ... FOR UPDATE SKIP LOCKED

Skip locked rows (queue pattern)

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Strictest isolation

Partitioning

6 commands

CREATE TABLE t (...) PARTITION BY RANGE (date)

Range partition

CREATE TABLE t_2026 PARTITION OF t FOR VALUES FROM ('2026-01-01') TO ('2027-01-01')

Create partition

CREATE TABLE t (...) PARTITION BY LIST (region)

List partition

CREATE TABLE t (...) PARTITION BY HASH (id)

Hash partition

ALTER TABLE t DETACH PARTITION t_old

Detach old partition

SELECT * FROM pg_partitions WHERE tablename='t'

List partitions

All reference sheets