SQL Cheatsheet

Joins, CTEs, indexing, and transactions — the core query and schema-design tools, with the reasoning an EXPLAIN plan would give you if you asked. For window functions specifically, see the SQL window functions cheatsheet; for the full explanations, see the SQL guides.

Joins — how the engine actually resolves them

SELECT o.order_id, c.name, o.amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id;   -- every customer, orders or not

SELECT o.order_id
FROM orders o
LEFT JOIN returns r ON r.order_id = o.order_id
WHERE r.order_id IS NULL;                               -- orders with NO matching return

The LEFT JOIN ... WHERE right.key IS NULL pattern above is the standard “find what’s missing” query — it’s doing the job of EXCEPT/anti-join without every database supporting a native LEFT ANTI JOIN keyword. INNER JOIN and plain JOIN are identical; the INNER keyword is optional and exists purely for readability.

Subqueries vs CTEs vs derived tables

-- Scalar subquery — must return exactly one value
SELECT name, (SELECT AVG(amount) FROM orders WHERE customer_id = c.customer_id) AS avg_order
FROM customers c;

-- CTE — named, readable, can be referenced multiple times in the same query
WITH monthly_revenue AS (
    SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
    FROM orders
    GROUP BY 1
)
SELECT month, revenue, revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly_revenue;

-- Correlated subquery — re-evaluated once per outer row, can be a real performance cost
SELECT customer_id
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.amount > 1000);

A CTE is primarily a readability tool, not automatically a performance one — most modern query planners (Postgres 12+, SQL Server, Snowflake) inline a CTE into the main query plan the same way they would a subquery, so choosing a CTE over a subquery is about naming intermediate steps clearly, not about forcing materialization. (Older Postgres versions and some engines do materialize CTEs by default — worth checking your specific engine’s docs if a CTE-heavy query is slower than expected.) EXISTS is generally preferred over IN with a subquery when checking for related-row existence, because EXISTS can short-circuit on the first match while IN may need the full subquery result built first.

GROUP BY and aggregates

SELECT
    department,
    COUNT(*) AS headcount,
    AVG(salary) AS avg_salary,
    MAX(salary) AS top_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;                    -- filters groups, not rows — WHERE can't do this

WHERE filters rows before grouping; HAVING filters groups after aggregation — this is why WHERE COUNT(*) > 5 is a syntax error but HAVING COUNT(*) > 5 works. A WHERE clause that references an aggregate function is the most common trigger for this specific error message.

-- Every non-aggregated selected column must appear in GROUP BY (or be functionally dependent on the key)
SELECT department, job_title, COUNT(*)
FROM employees
GROUP BY department, job_title;

Set operations

SELECT product_id FROM orders_2023
UNION                                    -- removes duplicates — costs a sort/hash step
SELECT product_id FROM orders_2024;

SELECT product_id FROM orders_2023
UNION ALL                                -- keeps duplicates — cheaper, use when you know there's no overlap
SELECT product_id FROM orders_2024;

SELECT customer_id FROM active_customers
INTERSECT
SELECT customer_id FROM premium_customers;

SELECT customer_id FROM all_customers
EXCEPT                                    -- MINUS in Oracle
SELECT customer_id FROM churned_customers;

UNION deduplicates by sorting or hashing the combined result — a real cost on large result sets. UNION ALL skips that step entirely. If you already know the two sides can’t overlap (partitioned by year, as above), UNION ALL is strictly better: same result, less work.

Indexing — what actually gets used

CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42 AND order_date > '2024-01-01';

This “leftmost prefix” rule is the single most common indexing mistake: adding a composite index expecting it to speed up a query that filters only on the second column. A composite index is usable only through its leading columns, in order — if the query never filters on customer_id, the (customer_id, order_date) index is invisible to the planner for that query, and a separate index on order_date alone is what’s actually needed.

-- A function applied to the indexed column defeats the index unless it's a functional/expression index
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';    -- won't use a plain index on email
CREATE INDEX idx_users_email_lower ON users (LOWER(email));       -- fixes it

Transactions and isolation

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;   -- both updates succeed together, or ROLLBACK undoes both

ACID, and why each letter is actually load-bearing:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Most applications run on the engine’s default (Read Committed for Postgres and SQL Server) and never touch this setting — it becomes relevant specifically for financial/inventory logic where two concurrent transactions modifying the same rows could produce a result neither transaction, run alone, would have produced.

Normalization, briefly

-- 1NF: atomic values, no repeating groups
-- BAD: phone_numbers = "555-1234,555-5678"
-- GOOD: a separate customer_phones table, one row per number

-- 2NF: no partial dependency on part of a composite key
-- 3NF: no transitive dependency — non-key columns depend on the key, the whole key, and nothing but the key

-- Denormalized (fast reads, risk of update anomalies)
CREATE TABLE orders (order_id INT, customer_name TEXT, customer_email TEXT, amount NUMERIC);

-- Normalized (single source of truth, requires a join to get customer info)
CREATE TABLE customers (customer_id INT PRIMARY KEY, name TEXT, email TEXT);
CREATE TABLE orders (order_id INT, customer_id INT REFERENCES customers(customer_id), amount NUMERIC);

The practical case for normalizing: with customer_name duplicated across every order row, updating a customer’s name after a legal name change requires updating every historical order row too, or the data silently disagrees with itself. Normalizing to a separate customers table makes the name a single fact in one place — the trade-off is that every report needing both order and customer data now needs a join, which is the real reason analytics warehouses often deliberately denormalize (star schemas) despite OLTP systems normalizing aggressively.

NULL handling — the rule that catches everyone eventually

SELECT * FROM orders WHERE discount_code != 'SUMMER10';   -- silently excludes rows where discount_code IS NULL
SELECT * FROM orders WHERE discount_code IS DISTINCT FROM 'SUMMER10';   -- includes NULLs correctly (Postgres)

SELECT COALESCE(discount_code, 'none') FROM orders;         -- substitute a default for NULL
SELECT NULLIF(prev_revenue, 0) FROM monthly_totals;         -- turn a specific value INTO NULL (avoids div-by-zero)

NULL compared with anything using = or != (including NULL = NULL) evaluates to NULL, not TRUE or FALSE — and a WHERE clause only keeps rows where the condition is TRUE, so a NULL result silently excludes the row rather than raising an error. This is why WHERE column != 'x' quietly drops every row where column IS NULL, and why IS NULL / IS NOT NULL are the only correct way to test for it directly.

String and date functions, quick reference

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers;
SELECT SUBSTRING(email FROM 1 FOR POSITION('@' IN email) - 1) AS username FROM customers;
SELECT TRIM(BOTH ' ' FROM raw_input) FROM staging_table;

SELECT DATE_TRUNC('week', order_date) FROM orders;          -- snap to the start of the week
SELECT order_date + INTERVAL '30 days' FROM orders;          -- date arithmetic
SELECT EXTRACT(DOW FROM order_date) FROM orders;              -- day of week as an integer
SELECT AGE(NOW(), signup_date) FROM customers;                -- Postgres: human-readable interval

Exact function names vary by engine (DATE_TRUNC/DATEADD/DATE_ADD, SUBSTRING/SUBSTR, POSITION/CHARINDEX) — the concepts above are portable, but always check the target engine’s docs for the exact syntax before assuming Postgres/MySQL/SQL Server all spell a function the same way.

Common patterns

Upsert (insert-or-update)

INSERT INTO inventory (product_id, quantity)
VALUES (101, 50)
ON CONFLICT (product_id)
DO UPDATE SET quantity = inventory.quantity + EXCLUDED.quantity;

EXCLUDED refers to the row that would have been inserted — this pattern (Postgres/SQLite syntax; MERGE in SQL Server/Oracle) does the insert-or-update in a single atomic statement instead of a separate SELECT to check existence followed by a conditional INSERT/UPDATE, which is both slower and race-condition-prone under concurrent writers.

Pagination that doesn’t degrade on large offsets

-- OFFSET/LIMIT: simple but gets slower as OFFSET grows — the DB still scans and discards those rows
SELECT * FROM orders ORDER BY order_id LIMIT 20 OFFSET 10000;

-- Keyset pagination: uses the last seen value as a filter — stays fast at any depth
SELECT * FROM orders WHERE order_id > 10020 ORDER BY order_id LIMIT 20;

Running a quick data-quality check before trusting a table

SELECT
    COUNT(*) AS total_rows,
    COUNT(*) - COUNT(customer_id) AS null_customer_ids,
    COUNT(DISTINCT order_id) - COUNT(order_id) AS duplicate_order_ids_negative_if_ok,
    MIN(order_date) AS earliest,
    MAX(order_date) AS latest
FROM orders;

COUNT(column) skips NULL values while COUNT(*) doesn’t — subtracting the two is a one-line way to surface how many NULLs exist in a column before building anything on top of it, and comparing COUNT(DISTINCT id) against COUNT(id) catches duplicate keys the same way.

Finding duplicate rows

SELECT email, COUNT(*)
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

Conditional aggregation (pivoting without PIVOT)

SELECT
    product_id,
    SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS completed_revenue,
    SUM(CASE WHEN status = 'refunded' THEN amount ELSE 0 END) AS refunded_amount
FROM orders
GROUP BY product_id;

CASE WHEN inside SUM/COUNT is the portable way to pivot rows into columns — it works identically across every SQL engine, unlike vendor-specific PIVOT syntax.

Not covered: Stored procedures, triggers, and vendor-specific PL/SQL or T-SQL procedural extensions aren’t covered here — this sheet is scoped to standard query and schema-design SQL. For window functions, see the dedicated cheatsheet; for deeper explanations, see the SQL guides.