SQL Cheatsheet Pack

The SQL reference that covers the three databases you actually use — PostgreSQL, MySQL, and SQLite — with syntax differences highlighted side by side. This pack goes beyond basic SELECT statements to cover window functions, CTEs, recursive queries, JSON operations, indexing strategies, and query optimization techniques. Each topic includes the query, its execution plan impact, and when to use (or avoid) the pattern. Built for developers and data engineers who write SQL daily.

What's Included

• Core SQL Syntax — SELECT, INSERT, UPDATE, DELETE, MERGE/UPSERT with cross-database syntax
• JOINs Visual Guide — INNER, LEFT, RIGHT, FULL, CROSS, LATERAL, self-joins with Venn diagrams
• Window Functions — ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, running totals, moving averages
• CTEs & Recursive Queries — WITH clauses, recursive tree traversal, hierarchical data patterns
• Indexing Strategies — B-tree, GIN, GiST, partial indexes, covering indexes, composite index ordering
• Query Optimization — EXPLAIN ANALYZE reading guide, common anti-patterns, pagination strategies
• JSON Operations — PostgreSQL JSONB, MySQL JSON, querying nested documents
• Database-Specific Features — Postgres arrays/ranges, MySQL variables, SQLite quirks

Preview / Sample Content

Window Functions — The Complete Reference

-- ROW_NUMBER: unique sequential number per partition
SELECT
    department,
    employee_name,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM employees;

-- Running total within each partition
SELECT
    order_date,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders;

-- Compare each row to the previous and next
SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
    LEAD(revenue, 1) OVER (ORDER BY month) AS next_month,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS month_over_month
FROM monthly_revenue;

-- Moving average (last 3 rows)
SELECT
    trade_date,
    close_price,
    AVG(close_price) OVER (
        ORDER BY trade_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3d
FROM stock_prices;

-- Percentile / median
SELECT
    department,
    salary,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY department) AS median_salary
FROM employees;

CTEs — From Basic to Recursive

-- Basic CTE: break complex queries into readable steps
WITH active_users AS (
    SELECT user_id, email, last_login
    FROM users
    WHERE last_login > CURRENT_DATE - INTERVAL '30 days'
),
user_orders AS (
    SELECT u.user_id, u.email, COUNT(o.id) AS order_count, SUM(o.total) AS total_spent
    FROM active_users u
    JOIN orders o ON o.user_id = u.user_id
    GROUP BY u.user_id, u.email
)
SELECT email, order_count, total_spent
FROM user_orders
WHERE total_spent > 500
ORDER BY total_spent DESC;

-- Recursive CTE: traverse hierarchical data (org chart, categories)
WITH RECURSIVE org_tree AS (
    -- Base case: top-level managers
    SELECT id, name, manager_id, 1 AS depth, name::text AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive step: employees under each manager
    SELECT e.id, e.name, e.manager_id, t.depth + 1, t.path || ' > ' || e.name
    FROM employees e
    JOIN org_tree t ON e.manager_id = t.id
)
SELECT depth, path, name
FROM org_tree
ORDER BY path;

Indexing — When and How

-- Composite index: column order matters!
-- Good: queries that filter on (status) or (status, created_at)
CREATE INDEX idx_orders_status_date ON orders (status, created_at);
-- Bad: queries that filter only on (created_at) — index not used

-- Partial index: index only the rows you query
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';
-- Smaller index, faster queries for the common case

-- Covering index: include columns to avoid table lookup
CREATE INDEX idx_users_email_covering ON users (email) INCLUDE (name, role);
-- SELECT name, role FROM users WHERE email = '...' uses index-only scan

-- Expression index: index computed values
CREATE INDEX idx_users_lower_email ON users (LOWER(email));
-- WHERE LOWER(email) = '[e-mail]' can use this index

-- PostgreSQL: GIN index for full-text search and JSONB
CREATE INDEX idx_products_search ON products USING GIN (to_tsvector('english', name || ' ' || description));
CREATE INDEX idx_data_jsonb ON events USING GIN (metadata jsonb_path_ops);

Quick Reference Table

Operation	PostgreSQL	MySQL	SQLite
UPSERT	INSERT ... ON CONFLICT DO UPDATE	INSERT ... ON DUPLICATE KEY UPDATE	INSERT OR REPLACE
String concat	`'a' \	\	'b'`
Current time	NOW() / CURRENT_TIMESTAMP	NOW()	datetime('now')
Auto-increment	GENERATED ALWAYS AS IDENTITY	AUTO_INCREMENT	AUTOINCREMENT
JSON extract	data->>'key'	JSON_EXTRACT(data, '$.key')	json_extract(data, '$.key')
Limit + offset	LIMIT 10 OFFSET 20	LIMIT 10 OFFSET 20	LIMIT 10 OFFSET 20
Boolean type	BOOLEAN (native)	TINYINT(1)	INTEGER (0/1)
Array type	TEXT[], INT[]	Not supported	Not supported
CTE support	Full + recursive	Full + recursive (8.0+)	Full + recursive (3.8.3+)
Window functions	Full support	Full (8.0+)	Full (3.25+)

Comparison: Query Optimization Anti-Patterns

Anti-Pattern	Why It's Slow	Fix
SELECT *	Reads unnecessary columns	List specific columns
WHERE function(column)	Can't use index	Use expression index or rewrite
LIKE '%term%'	Full table scan	Full-text search index
OR in WHERE	Often prevents index use	Use UNION or IN ()
NOT IN (subquery)	Poor NULL handling	Use NOT EXISTS
Correlated subquery	Executes per row	Rewrite as JOIN or CTE
OFFSET 10000	Scans and discards 10k rows	Keyset pagination (WHERE id > ?)
Missing LIMIT	Returns all rows	Always paginate
COUNT(*) for existence	Counts all matches	Use EXISTS instead

Usage Tips

1. Start with the syntax comparison table — it highlights the 20% of syntax that differs between Postgres, MySQL, and SQLite.
2. Master window functions — LAG, LEAD, and running totals eliminate the need for self-joins and subqueries.
3. Use the indexing decision guide before adding indexes — wrong index order or type wastes disk and slows writes.
4. Read EXPLAIN ANALYZE output using the included guide — the pack breaks down how to read execution plans step by step.
5. Copy the CTE templates for hierarchical queries — recursive CTEs are the clean way to handle tree data.

---

This is 1 of 11 resources in the Cheatsheet Reference Pro toolkit. Get the complete [SQL Cheatsheet Pack] with all files, templates, and documentation for $12.

Get the Full Kit →

Or grab the entire Cheatsheet Reference Pro bundle (11 products) for $79 — save 30%.

Get the Complete Bundle →

---

Related Articles

• AWS Services Quick Reference
• DevOps Tooling Reference
• Docker & Kubernetes Cheatsheets