0xDarkMatter/sql-ops

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