SQL Patterns

Quick reference for common SQL patterns.

CTE (Common Table Expressions)

WITH active_users AS (
    SELECT id, name, email
    FROM users
    WHERE status = 'active'
)
SELECT * FROM active_users WHERE created_at > '2024-01-01';

Chained CTEs

WITH
    active_users AS (
        SELECT id, name FROM users WHERE status = 'active'
    ),
    user_orders AS (
        SELECT user_id, COUNT(*) as order_count
        FROM orders GROUP BY user_id
    )
SELECT u.name, COALESCE(o.order_count, 0) as orders
FROM active_users u
LEFT JOIN user_orders o ON u.id = o.user_id;

Window Functions (Quick Reference)

Function	Use
ROW_NUMBER()	Unique sequential numbering
RANK()	Rank with gaps (1, 2, 2, 4)
DENSE_RANK()	Rank without gaps (1, 2, 2, 3)
LAG(col, n)	Previous row value
LEAD(col, n)	Next row value
SUM() OVER	Running total
AVG() OVER	Moving average

SELECT
    date,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY date) as prev_day,
    SUM(revenue) OVER (ORDER BY date) as running_total
FROM daily_sales;

JOIN Reference

Type	Returns
INNER JOIN	Only matching rows
LEFT JOIN	All left + matching right
RIGHT JOIN	All right + matching left
FULL JOIN	All rows, NULL where no match

Pagination

-- OFFSET/LIMIT (simple, slow for large offsets)
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 40;

-- Keyset (fast, scalable)
SELECT * FROM products WHERE id > 42 ORDER BY id LIMIT 20;

Transaction Isolation Levels

Level	Dirty Read	Non-Repeatable Read	Phantom Read	Use
READ UNCOMMITTED	Possible	Possible	Possible	Rarely (PostgreSQL treats as READ COMMITTED)
READ COMMITTED	No	Possible	Possible	Default in most databases
REPEATABLE READ	No	No	Possible*	Consistent multi-statement reads
SERIALIZABLE	No	No	No	Critical invariants (retry on serialization failure)

*PostgreSQL's REPEATABLE READ also prevents phantoms via snapshot isolation.

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... critical operations ...
COMMIT;  -- be prepared to retry on serialization failure

Keep the default READ COMMITTED globally; raise the level per-transaction only where the logic requires it.

Index Quick Reference

Index Type	Best For
B-tree	Range queries, ORDER BY
Hash	Exact equality only
GIN	Arrays, JSONB, full-text
Covering	Avoid table lookup

Anti-Patterns

Mistake	Fix
SELECT *	List columns explicitly
WHERE YEAR(date) = 2024	WHERE date >= '2024-01-01'
NOT IN with NULLs	Use NOT EXISTS
N+1 queries	Use JOIN or batch

Additional Resources

For detailed patterns, load:

• ./references/window-functions.md - Complete window function patterns
• ./references/indexing-strategies.md - Index types, covering indexes, optimization