SQL Cheatsheet — 100+ Statements with Pitfalls and MySQL / PostgreSQL / SQLite Differences

Return the listed columns from every row of a table. The workhorse of every read query.

⚠ Common pitfall: `SELECT *` is fine for ad-hoc exploration, but in production it locks you into whatever columns happen to exist today — add one column and every dependent app deserializes a new field.

SELECT id, name FROM users;

SELECT * FROM orders LIMIT 10;

Filter rows by a predicate. Combine with AND / OR / NOT. Evaluated row by row.

⚠ Common pitfall: `WHERE col = NULL` is ALWAYS false. NULL never equals NULL. Use `WHERE col IS NULL` instead.

SELECT * FROM users WHERE status = 'active';

SELECT * FROM orders WHERE amount > 100 AND created_at > NOW() - INTERVAL 7 DAY;

SELECT * FROM users WHERE deleted_at IS NULL;

Sort the result set by one or more columns. Default ASC. NULLs sort last in PostgreSQL, first in MySQL by default.

⚠ Common pitfall: Without ORDER BY, row order is UNDEFINED — never trust the order you saw last time. Add explicit ORDER BY for pagination.

SELECT * FROM users ORDER BY created_at DESC;

SELECT * FROM products ORDER BY price ASC, name ASC;

SELECT * FROM users ORDER BY created_at DESC NULLS LAST; -- PostgreSQL

Take the first n rows after offset m. Common for pagination. MySQL / PostgreSQL / SQLite all support this syntax; SQL Server uses TOP / OFFSET FETCH.

⚠ Common pitfall: OFFSET grows expensive — page 1000 scans all 1000 previous rows. For deep pagination use keyset (seek): `WHERE id > last_id ORDER BY id LIMIT 20`.

SELECT * FROM users ORDER BY id LIMIT 20;

SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 100;

SELECT * FROM users WHERE id > 12345 ORDER BY id LIMIT 20; -- keyset

Remove duplicate rows from the result. Operates on the WHOLE row in the SELECT list, not just one column.

⚠ Common pitfall: `SELECT DISTINCT a, b` deduplicates the pair (a, b) — not column `a` alone. People constantly read it wrong.

SELECT DISTINCT country FROM users;

SELECT DISTINCT user_id, product_id FROM orders;

SELECT COUNT(DISTINCT user_id) FROM logins;

Collapse rows that share the same value of the grouping column(s) into one row, ready for aggregates.

⚠ Common pitfall: Every non-aggregated column in SELECT must appear in GROUP BY (PostgreSQL and modern MySQL enforce this with `only_full_group_by`). Old MySQL silently returned a random row — beware legacy code.

SELECT country, COUNT(*) FROM users GROUP BY country;

SELECT user_id, SUM(amount) FROM orders GROUP BY user_id;

SELECT DATE(created_at) d, COUNT(*) FROM logs GROUP BY DATE(created_at);

Match against a fixed list of values. Equivalent to a chain of OR equalities.

⚠ Common pitfall: `NOT IN (subquery)` returns ZERO ROWS if the subquery contains any NULL — three-valued logic bites. Use `NOT EXISTS` instead.

SELECT * FROM users WHERE status IN ('active', 'pending');

SELECT * FROM orders WHERE id NOT IN (1, 2, 3);

SELECT * FROM users WHERE id IN (SELECT user_id FROM admins);

Range filter, inclusive on both ends. Works for numbers, dates, and strings.

⚠ Common pitfall: For dates, `BETWEEN '2026-01-01' AND '2026-01-31'` excludes most of Jan 31 — it stops at 2026-01-31 00:00:00. Use `>= start AND < next_start` instead.

SELECT * FROM products WHERE price BETWEEN 100 AND 500;

SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01';

Pattern match. `%` matches any number of chars, `_` matches one. Case-insensitive in MySQL by default, case-sensitive in PostgreSQL (use ILIKE).

⚠ Common pitfall: Leading wildcard (`%foo`) prevents the engine from using a normal B-tree index — full table scan. Trailing wildcard (`foo%`) is index-friendly.

SELECT * FROM users WHERE email LIKE '%@gmail.com';

SELECT * FROM products WHERE name ILIKE 'iphone%'; -- PostgreSQL

SELECT * FROM users WHERE phone LIKE '138_____1234'; -- 11-digit pattern

Inline conditional expression. Returns the first matching THEN value, else the ELSE. Works in SELECT, WHERE, ORDER BY.

⚠ Common pitfall: CASE evaluates lazily but the RESULT TYPE is unified — mix INT and TEXT and you get a string. Cast explicitly if you mean it.

SELECT name, CASE WHEN age < 18 THEN 'minor' WHEN age < 60 THEN 'adult' ELSE 'senior' END AS age_group FROM users;

SELECT SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid_total FROM orders;

Return the first non-NULL argument. The cleanest way to provide a default value.

SELECT COALESCE(nickname, name, 'anonymous') AS display_name FROM users;

SELECT COALESCE(updated_at, created_at) AS last_seen FROM posts;

Return NULL if a = b, otherwise a. Common trick to turn a sentinel value (0, empty string) into NULL.

SELECT total / NULLIF(count, 0) AS avg FROM stats; -- avoid divide-by-zero

SELECT NULLIF(name, '') AS name FROM users;

Stack two result sets vertically. UNION dedupes, UNION ALL keeps duplicates and is much faster.

⚠ Common pitfall: Both sides must have the SAME number of columns with COMPATIBLE types. ORDER BY only on the last query applies to the whole UNION.

SELECT id FROM active_users UNION SELECT id FROM trial_users;

SELECT 'a' AS source, * FROM table_a UNION ALL SELECT 'b' AS source, * FROM table_b;

Set operations between two result sets. INTERSECT keeps rows present in both; EXCEPT (MINUS in Oracle) keeps rows in the first but not the second.

⚠ Common pitfall: MySQL added INTERSECT / EXCEPT only in 8.0.31. Older versions need to emulate with LEFT JOIN ... WHERE IS NULL or NOT EXISTS.

SELECT id FROM customers INTERSECT SELECT id FROM newsletter_subscribers;

SELECT id FROM all_users EXCEPT SELECT id FROM banned;

`WHERE col IN (subquery with NULL)` is fine. `WHERE col NOT IN (subquery with NULL)` always returns empty. EXISTS / NOT EXISTS are NULL-safe.

-- TRAP:
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM bans); -- empty if any bans.user_id IS NULL
-- SAFE:
SELECT * FROM users u WHERE NOT EXISTS (SELECT 1 FROM bans b WHERE b.user_id = u.id);

Give a column or subquery a readable name. AS is optional in most dialects but always allowed. Use double quotes (or backticks in MySQL) for aliases with spaces or reserved words.

SELECT u.id AS user_id, COUNT(o.id) AS order_count FROM users u LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.id;

SELECT * FROM (SELECT user_id, SUM(amount) total FROM orders GROUP BY user_id) AS spend WHERE total > 1000;

The only correct way to test for NULL. Returns TRUE or FALSE (never UNKNOWN), unlike = / != which return UNKNOWN against NULL.

SELECT * FROM users WHERE deleted_at IS NULL;

SELECT * FROM orders WHERE shipped_at IS NOT NULL;

Convert a value to another type. CAST(...) is standard; PostgreSQL also has the shorthand `::type`. Common for forcing integer division to decimal or text to date.

⚠ Common pitfall: In PostgreSQL `5 / 2` is integer division = 2. Cast one side: `5::numeric / 2` = 2.5. MySQL `/` always returns a decimal.

SELECT CAST('2026-01-01' AS DATE);

SELECT 5::numeric / 2; -- PostgreSQL: 2.5

SELECT CAST(price AS CHAR) FROM products; -- MySQL number to string

Core string toolkit. LENGTH (CHAR_LENGTH in MySQL counts chars, LENGTH counts bytes), SUBSTRING(s FROM a FOR b), TRIM, REPLACE, UPPER, LOWER are nearly identical across engines.

⚠ Common pitfall: MySQL `LENGTH()` returns BYTES — a 3-char Chinese string in utf8mb4 returns 9. Use `CHAR_LENGTH()` for character count.

SELECT SUBSTRING('hello world' FROM 1 FOR 5); -- 'hello'

SELECT REPLACE(phone, '-', '') FROM users;

SELECT CHAR_LENGTH('你好'); -- 2 (MySQL); LENGTH would give 6

Numeric scalar functions. ROUND(x, n) rounds to n decimals, CEIL / FLOOR go up / down, ABS removes sign, MOD(a, b) (or a % b) gives the remainder.

⚠ Common pitfall: ROUND on a FLOAT can surprise you (0.5 may round to 0, not 1, due to banker's rounding or binary representation). Round on NUMERIC for predictable results.

SELECT ROUND(3.14159, 2); -- 3.14

SELECT CEIL(4.1), FLOOR(4.9); -- 5, 4

SELECT MOD(10, 3); -- 1

EXTRACT(YEAR FROM ts) pulls one field out of a date. PostgreSQL DATE_TRUNC('month', ts) zeros everything below the unit — the cleanest way to bucket by month / week / day.

⚠ Common pitfall: MySQL has no DATE_TRUNC. Emulate with DATE_FORMAT(ts, '%Y-%m-01') for month, or DATE(ts) for day.

SELECT EXTRACT(YEAR FROM created_at), COUNT(*) FROM orders GROUP BY 1;

SELECT DATE_TRUNC('month', created_at) m, SUM(amount) FROM orders GROUP BY m; -- PostgreSQL

SELECT DATE_FORMAT(created_at, '%Y-%m') m, SUM(amount) FROM orders GROUP BY m; -- MySQL

Return the largest / smallest of a list of expressions, evaluated per row (unlike MAX / MIN which aggregate across rows).

⚠ Common pitfall: In MySQL, GREATEST / LEAST return NULL if ANY argument is NULL. In PostgreSQL they skip NULLs. Wrap args in COALESCE if you need MySQL-style portability.

SELECT GREATEST(price, min_price) FROM products;

SELECT LEAST(quota, used + 1) FROM accounts;

Match a tuple of columns against a list of tuples. Compact way to express "either (a=1 and b=2) or (a=3 and b=4)".

SELECT * FROM order_items WHERE (order_id, product_id) IN ((10, 5), (10, 6), (11, 5));

Line comment with `--` (needs a space after in MySQL) or `#` (MySQL only). Block comment with `/* ... */`. Useful for annotating migrations and tagging queries for log grep.

SELECT * FROM users; -- active session lookup

/* batch-job:nightly-rollup */ SELECT SUM(amount) FROM orders;

LIMIT/OFFSET is simple but scans and discards all skipped rows, so deep pages get slow. Keyset pagination remembers the last seen key and uses `WHERE key > last ORDER BY key LIMIT n` — constant time at any depth.

⚠ Common pitfall: Keyset needs a stable, unique ORDER BY key (often the primary key, or a (sort_col, id) tuple to break ties). If the sort column has duplicates, include the id as a tiebreaker.

SELECT * FROM posts ORDER BY id LIMIT 20; -- page 1

SELECT * FROM posts WHERE id > 12345 ORDER BY id LIMIT 20; -- next page, no OFFSET

SELECT * FROM posts WHERE (created_at, id) < (:last_ts, :last_id) ORDER BY created_at DESC, id DESC LIMIT 20; -- tie-safe

COUNT(*) counts ALL rows including NULLs. COUNT(col) counts rows where col IS NOT NULL. COUNT(DISTINCT col) counts unique non-null values.

⚠ Common pitfall: COUNT(1) is identical to COUNT(*) in any modern optimizer — the "COUNT(1) is faster" myth is from the 90s. Pick whichever reads clearer.

SELECT COUNT(*) FROM users;

SELECT COUNT(email) FROM users; -- non-null only

SELECT COUNT(DISTINCT country) FROM users;

Numeric aggregates. SUM(NULL columns) returns NULL, not 0. AVG skips NULLs entirely (sum of non-nulls / count of non-nulls).

⚠ Common pitfall: Want a NULL-safe SUM that returns 0 on empty? Wrap with COALESCE: `COALESCE(SUM(x), 0)`.

SELECT SUM(amount) FROM orders WHERE user_id = 42;

SELECT AVG(price) FROM products;

SELECT COALESCE(SUM(amount), 0) FROM orders WHERE user_id = 999;

Smallest / largest value of a column. Works on numbers, dates, and strings (lexicographic for strings).

SELECT MIN(created_at), MAX(created_at) FROM orders;

SELECT user_id, MAX(score) AS best FROM games GROUP BY user_id;

Filter groups after GROUP BY (WHERE filters rows BEFORE grouping). Required when the predicate is an aggregate.

⚠ Common pitfall: Do not use HAVING for non-aggregate filters — that is WHERE's job and runs earlier (less data to group). HAVING on a non-aggregate works but skips index.

SELECT country, COUNT(*) c FROM users GROUP BY country HAVING COUNT(*) > 100;

SELECT user_id FROM orders GROUP BY user_id HAVING SUM(amount) > 10000;

Flatten a column into a comma-separated string per group. MySQL: GROUP_CONCAT. PostgreSQL: STRING_AGG. SQLite: GROUP_CONCAT.

⚠ Common pitfall: MySQL GROUP_CONCAT truncates silently at `group_concat_max_len` (default 1024 bytes). For long aggregations bump the session variable first.

SELECT user_id, GROUP_CONCAT(product_id ORDER BY created_at SEPARATOR ',') FROM orders GROUP BY user_id; -- MySQL

SELECT user_id, STRING_AGG(product_id::text, ',' ORDER BY created_at) FROM orders GROUP BY user_id; -- PostgreSQL

PostgreSQL aggregators that pack groups into arrays / JSON arrays. Combined with json_build_object for nested rollups.

SELECT user_id, ARRAY_AGG(product_id ORDER BY created_at) FROM orders GROUP BY user_id;

SELECT user_id, JSON_AGG(json_build_object('id', id, 'amount', amount)) FROM orders GROUP BY user_id;

Conditional aggregation in the standard / PostgreSQL form. Equivalent to SUM(CASE WHEN ... THEN ...) but cleaner.

⚠ Common pitfall: MySQL does not support FILTER yet — fall back to SUM(CASE WHEN ... THEN 1 ELSE 0 END) for portability.

SELECT COUNT(*) FILTER (WHERE status = 'paid') AS paid_count, COUNT(*) FILTER (WHERE status = 'refunded') AS refunded_count FROM orders; -- PostgreSQL

Multi-level aggregations in one query. ROLLUP(a, b) gives totals at (a, b), (a), and (). CUBE gives every subset. GROUPING SETS is the explicit form.

⚠ Common pitfall: NULL in the grouping column of a rollup row means "this level is aggregated, not a NULL value". Use `GROUPING(col)` to distinguish.

SELECT country, city, COUNT(*) FROM users GROUP BY ROLLUP(country, city);

SELECT a, b, SUM(x) FROM t GROUP BY GROUPING SETS ((a), (b), (a, b), ());

Exact COUNT(*) on a huge table is slow (full index / heap scan). For an estimate, read from system tables: `information_schema.tables.table_rows` (MySQL) or `pg_class.reltuples` (PostgreSQL).

SELECT table_rows FROM information_schema.tables WHERE table_name = 'orders'; -- MySQL estimate

SELECT reltuples::bigint FROM pg_class WHERE relname = 'orders'; -- PostgreSQL estimate

Compute a percentile / median over a group. PERCENTILE_CONT interpolates between rows; PERCENTILE_DISC returns an actual row value. The WITHIN GROUP (ORDER BY ...) syntax is required.

⚠ Common pitfall: MySQL has no PERCENTILE_CONT. Approximate the median with a window + NTH_VALUE, or use PERCENT_RANK and pick the crossing row.

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median FROM orders; -- PostgreSQL

SELECT category, PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY price) FROM products GROUP BY category;

Statistical spread aggregates. STDDEV_SAMP / VAR_SAMP use n-1 (sample), STDDEV_POP / VAR_POP use n (population). Plain STDDEV is sample in PostgreSQL and MySQL.

SELECT AVG(amount), STDDEV_SAMP(amount) FROM orders;

SELECT product_id, VAR_POP(rating) FROM reviews GROUP BY product_id;

PostgreSQL aggregates that reduce a column of booleans: BOOL_AND is true only if every row is true, BOOL_OR is true if any row is true. MySQL emulates with MIN / MAX on 0/1.

SELECT order_id, BOOL_AND(in_stock) AS all_in_stock FROM order_items GROUP BY order_id; -- PostgreSQL

SELECT order_id, MIN(in_stock) AS all_in_stock FROM order_items GROUP BY order_id; -- MySQL (0/1)

A window COUNT gives you the total row count alongside each detail row — handy for "row X of N" or computing each row's share of the total in one pass.

SELECT id, amount, amount * 100.0 / SUM(amount) OVER () AS pct_of_total FROM orders;

SELECT *, COUNT(*) OVER () AS total_rows FROM users;

Turn category rows into columns using SUM/MAX over a CASE per target column. The portable pivot that works without any vendor PIVOT keyword.

SELECT user_id,
  SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid,
  SUM(CASE WHEN status = 'refunded' THEN amount ELSE 0 END) AS refunded
FROM orders GROUP BY user_id;

Keep only rows that match on both sides. The default JOIN. If a left row has no match, it is dropped.

⚠ Common pitfall: A typo in ON or a missing predicate degrades to a CROSS JOIN — every row times every row. Always include the join condition on the indexed column.

SELECT u.name, o.amount FROM users u INNER JOIN orders o ON o.user_id = u.id;

SELECT u.id FROM users u JOIN orders o ON o.user_id = u.id AND o.status = 'paid';

Keep every row from the left table; right-side columns are NULL when no match. Workhorse for "users and their optional orders".

⚠ Common pitfall: Filtering the RIGHT table in WHERE turns a LEFT JOIN into INNER JOIN — rows with NULL right side get dropped. Move the filter into the ON clause instead.

SELECT u.id, COUNT(o.id) AS order_count FROM users u LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.id;

SELECT u.* FROM users u LEFT JOIN bans b ON b.user_id = u.id WHERE b.user_id IS NULL; -- anti-join

Keep every row from the right table. Mathematically equivalent to flipping the tables and using LEFT JOIN — rare in practice.

⚠ Common pitfall: Most teams ban RIGHT JOIN by convention — flip to LEFT JOIN so the "kept side" is always on the left and reads top-to-bottom.

SELECT u.id, b.reason FROM bans b RIGHT JOIN users u ON u.id = b.user_id;

Keep rows from BOTH sides; missing matches on either side become NULL. Useful for reconciling two ledgers.

⚠ Common pitfall: MySQL does not support FULL OUTER JOIN at all. Emulate with `LEFT JOIN UNION RIGHT JOIN` if you need it.

SELECT a.id, b.id FROM table_a a FULL OUTER JOIN table_b b ON a.id = b.id WHERE a.id IS NULL OR b.id IS NULL; -- find mismatches

Cartesian product — every left row paired with every right row. Use intentionally for date ranges, calendars, combinations.

⚠ Common pitfall: Missing JOIN condition on a comma join `FROM a, b WHERE ...` accidentally becomes a CROSS JOIN if you forget the WHERE. Prefer explicit CROSS JOIN to make intent clear.

SELECT d.day, p.name FROM generate_series('2026-01-01'::date, '2026-01-31', '1 day') d(day) CROSS JOIN products p; -- PostgreSQL daily report skeleton

Join a table to itself by giving it two aliases. Used for parent / child rows, manager / employee, pairs comparison.

SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON m.id = e.manager_id;

SELECT a.id, b.id FROM events a JOIN events b ON a.user_id = b.user_id AND b.created_at > a.created_at AND b.created_at < a.created_at + INTERVAL 1 HOUR;

Chain three or more JOINs to assemble related rows from many tables. The optimizer picks the join order, but the readability follows your written order.

⚠ Common pitfall: Long JOIN chains often have a "fan-out" — joining one row to a child with 5 rows then to a grandchild with 3 yields 15 rows per starting row. Counts get wrong fast.

SELECT u.name, o.id, oi.product_id, p.name FROM users u JOIN orders o ON o.user_id = u.id JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id;

Shorthand JOIN when both tables share the same column name. The joined column appears once in the result, not twice.

SELECT * FROM users JOIN orders USING (user_id);

SELECT * FROM a NATURAL JOIN b; -- joins on ALL columns with matching names (rarely safe)

Right side of the JOIN can reference columns of the left side — like a correlated subquery in JOIN position. Lets you fetch "top N per group" cleanly.

SELECT u.id, recent.* FROM users u LEFT JOIN LATERAL (SELECT * FROM orders o WHERE o.user_id = u.id ORDER BY created_at DESC LIMIT 3) recent ON true; -- PostgreSQL

Two idioms produce the same result. `LEFT JOIN ... WHERE right.id IS NULL` and `NOT EXISTS (subquery)`. Both engines optimize them similarly; pick whichever reads clearer.

SELECT u.* FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE o.user_id IS NULL;

SELECT u.* FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

Use EXISTS instead of a JOIN when you only want to FILTER the left table by the existence of a match — a JOIN would duplicate left rows when the right side has many matches.

⚠ Common pitfall: A plain INNER JOIN to filter "users who have any order" returns one row PER order. EXISTS returns one row per user. Know which you want.

SELECT u.* FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id); -- one row per user

When joining a table to a child you will SUM, aggregate the child in a subquery FIRST, then join. Joining then aggregating multiplies parent columns by the child row count.

⚠ Common pitfall: SELECT u.id, SUM(o.amount), SUM(p.points) joining two child tables double-counts both — each side fans the other out. Pre-aggregate each child separately.

SELECT u.id, o.total FROM users u LEFT JOIN (SELECT user_id, SUM(amount) total FROM orders GROUP BY user_id) o ON o.user_id = u.id;

JOIN conditions are not limited to equality. Join on BETWEEN or < to bucket values into bands, match events to time windows, or find overlaps.

⚠ Common pitfall: Range joins cannot use a hash join and often degrade to nested loops — slow on big tables. Make sure the range column is indexed.

SELECT s.amount, t.rate FROM sales s JOIN tax_brackets t ON s.amount BETWEEN t.low AND t.high;

Joining a VARCHAR column to a different charset/collation, or an INT to a string key, forces a per-row cast and disables index use — a top cause of "the join is slow".

⚠ Common pitfall: In MySQL, joining utf8mb4_general_ci to utf8mb4_unicode_ci silently casts and skips the index. Keep join columns identical in type AND collation.

-- Force matching collation if you cannot fix the schema:
SELECT * FROM a JOIN b ON a.code = b.code COLLATE utf8mb4_general_ci; -- MySQL

A subquery in the SELECT list that returns exactly one row, one column. Use sparingly — runs once per outer row.

⚠ Common pitfall: If the subquery returns more than one row, the query ERRORS at runtime. Add LIMIT 1 + ORDER BY to make it deterministic.

SELECT u.id, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count FROM users u;

SELECT u.id, (SELECT MAX(amount) FROM orders WHERE user_id = u.id) AS biggest FROM users u;

Filter outer rows whose column matches any value returned by the subquery. The optimizer often rewrites to a semi-join.

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

Returns true if the subquery returns at least one row. Short-circuits — the engine stops at the first hit.

⚠ Common pitfall: `SELECT 1 FROM ...` inside EXISTS is idiomatic — the SELECT list is IGNORED, only existence matters. Stop typing `SELECT *`.

SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

SELECT * FROM users u WHERE NOT EXISTS (SELECT 1 FROM bans b WHERE b.user_id = u.id);

Subquery that references a column from the outer query — evaluated once per outer row. Powerful but easy to write quadratic queries.

⚠ Common pitfall: A correlated subquery in WHERE can often be rewritten as a JOIN + GROUP BY for orders-of-magnitude speedup. EXPLAIN both.

SELECT u.id FROM users u WHERE u.last_login < (SELECT MAX(o.created_at) FROM orders o WHERE o.user_id = u.id);

Common Table Expression — name a subquery and reference it later. Improves readability for stacked transformations.

⚠ Common pitfall: In older PostgreSQL (< 12), CTEs were always materialized — an optimization fence. Modern PostgreSQL inlines by default, MySQL 8 too. Use `MATERIALIZED` keyword to force the old behavior.

WITH heavy_users AS (SELECT user_id FROM orders GROUP BY user_id HAVING SUM(amount) > 10000) SELECT u.* FROM users u JOIN heavy_users h ON h.user_id = u.id;

CTE that references itself. Used for tree / graph traversal, generated sequences, hierarchical rollups.

⚠ Common pitfall: Forget the UNION ALL anchor or the recursive step termination and you get infinite recursion. Most engines cap at ~1000 iterations and error.

WITH RECURSIVE org AS (SELECT id, name, manager_id, 0 AS depth FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id, o.depth + 1 FROM employees e JOIN org o ON e.manager_id = o.id) SELECT * FROM org ORDER BY depth, id;

WITH RECURSIVE nums(n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM nums WHERE n < 100) SELECT * FROM nums; -- generate 1..100

`col > ANY (subquery)` is true if col exceeds at least one value. `col > ALL (...)` requires it exceeds every value. Equivalent to MAX/MIN tricks.

SELECT * FROM products WHERE price > ANY (SELECT price FROM products WHERE category = 'book');

SELECT * FROM products WHERE price >= ALL (SELECT price FROM products); -- most expensive

A subquery in the FROM clause acts as an inline temporary table. Used to pre-aggregate, pre-filter, or attach a computed column before the outer query consumes it.

⚠ Common pitfall: MySQL requires a mandatory alias on every derived table (`) AS t`). PostgreSQL also requires it. Forget it and you get a syntax error.

SELECT t.user_id, t.total FROM (SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id) AS t WHERE t.total > 1000;

Stack several named CTEs separated by commas; later ones can reference earlier ones. Turns a deeply nested query into a readable top-to-bottom pipeline.

WITH paid AS (
  SELECT user_id, SUM(amount) total FROM orders WHERE status = 'paid' GROUP BY user_id
), ranked AS (
  SELECT user_id, total, RANK() OVER (ORDER BY total DESC) rnk FROM paid
)
SELECT * FROM ranked WHERE rnk <= 10;

PostgreSQL lets a CTE be an INSERT / UPDATE / DELETE with RETURNING, then use those rows downstream — e.g. move rows between tables in one statement.

⚠ Common pitfall: All sub-statements see the SAME snapshot — a CTE that DELETEs is not visible to a sibling CTE that SELECTs the same table. MySQL does not support modifying CTEs.

WITH moved AS (
  DELETE FROM orders WHERE created_at < NOW() - INTERVAL '1 year' RETURNING *
)
INSERT INTO orders_archive SELECT * FROM moved; -- PostgreSQL

Use a correlated subquery for "does a match exist / a single scalar per row"; use a JOIN when you need columns FROM the other table. The optimizer often converts between them, but readability differs.

-- scalar per row → subquery:
SELECT u.id, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) c FROM users u;
-- need columns → join:
SELECT u.id, o.amount FROM users u JOIN orders o ON o.user_id = u.id;

Reverse of a pivot: turn wide columns back into (key, value) rows. Portable form is a UNION ALL per column, or a CROSS JOIN against a VALUES list in PostgreSQL.

SELECT id, 'q1' AS quarter, q1 AS amount FROM sales
UNION ALL SELECT id, 'q2', q2 FROM sales
UNION ALL SELECT id, 'q3', q3 FROM sales;

SELECT s.id, v.quarter, v.amount FROM sales s CROSS JOIN LATERAL (VALUES ('q1', s.q1), ('q2', s.q2)) AS v(quarter, amount); -- PostgreSQL

Assign a sequential number 1..N within each partition. The classic "top N per group" tool — wrap in subquery then filter rn <= N.

⚠ Common pitfall: Ties break by whatever the ORDER BY says; if you want gaps for ties use RANK, no gaps use DENSE_RANK. ROW_NUMBER is always unique.

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn FROM orders) t WHERE rn <= 3; -- top 3 most recent orders per user

RANK leaves gaps after ties (1, 2, 2, 4). DENSE_RANK does not (1, 2, 2, 3). Use for leaderboards.

SELECT user_id, score, RANK() OVER (ORDER BY score DESC) AS rnk FROM scores;

SELECT category, name, price, DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) FROM products;

Reach the previous (LAG) or next (LEAD) row inside a partition. Best for time-series deltas, "previous order amount", funnel transitions.

SELECT user_id, created_at, amount, LAG(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_amount FROM orders;

SELECT day, sales, sales - LAG(sales) OVER (ORDER BY day) AS day_over_day_change FROM daily_stats;

Running / cumulative aggregate within a window. PARTITION BY makes it per-group; ORDER BY makes it cumulative up to the current row.

⚠ Common pitfall: Without explicit frame, ORDER BY defaults to `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` — ties get aggregated together. For "exactly N rows back" use ROWS, not RANGE.

SELECT day, sales, SUM(sales) OVER (ORDER BY day) AS cumulative FROM daily_stats;

SELECT user_id, created_at, amount, SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS lifetime_spend FROM orders;

Sliding window aggregate over the last n rows. Used for moving averages, smoothing time series.

SELECT day, price, AVG(price) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7d FROM prices;

Bucket the rows into n approximately equal groups by the ORDER BY. Use for percentiles, quartiles, decile splits.

SELECT user_id, total_spend, NTILE(4) OVER (ORDER BY total_spend DESC) AS quartile FROM user_spend;

Reach the first / last value inside the window frame. LAST_VALUE needs an explicit frame to ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING — otherwise it returns the current row.

SELECT user_id, created_at, amount, FIRST_VALUE(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS first_order_amount FROM orders;

SELECT *, LAST_VALUE(amount) OVER (PARTITION BY user_id ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM orders;

Statistical window functions. PERCENT_RANK = (rank - 1) / (n - 1). CUME_DIST = rows up to and including current / total rows.

SELECT user_id, score, PERCENT_RANK() OVER (ORDER BY score) AS pr FROM scores;

Define a window once with the WINDOW clause and reuse across multiple window calls. Cleaner than repeating the same OVER (...) three times.

SELECT id, amount,
  RANK() OVER w AS rnk,
  AVG(amount) OVER w AS avg_in_window,
  SUM(amount) OVER w AS sum_in_window
FROM orders
WINDOW w AS (PARTITION BY user_id ORDER BY created_at);

Return the value of the column from the nth row in the window frame. Combine with a full frame to grab, say, the 2nd-largest amount per group.

⚠ Common pitfall: Like LAST_VALUE, NTH_VALUE is frame-sensitive — without an explicit frame it only sees up to the current row and may return NULL for n beyond it.

SELECT user_id, amount, NTH_VALUE(amount, 2) OVER (PARTITION BY user_id ORDER BY amount DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_biggest FROM orders;

Classic pattern: subtract ROW_NUMBER() from a sequential value; rows in the same run share the difference, letting you GROUP BY that difference to collapse consecutive streaks.

SELECT user_id, MIN(day) AS streak_start, MAX(day) AS streak_end, COUNT(*) AS len
FROM (
  SELECT user_id, day, day - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day))::int AS grp
  FROM logins
) t
GROUP BY user_id, grp; -- PostgreSQL consecutive-day streaks

Window functions are computed AFTER WHERE / GROUP BY / HAVING, so you cannot filter on them in the same SELECT. Wrap the windowed query in a subquery / CTE and filter outside.

⚠ Common pitfall: WHERE row_number() OVER (...) = 1 is a syntax error. This trips up everyone once. Always wrap it.

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) rn FROM orders
) t WHERE rn = 1; -- latest order per user

A QUALIFY clause filters on window-function results directly, without the wrapping subquery. Supported in Snowflake, BigQuery, DuckDB, Databricks. PostgreSQL / MySQL still need the subquery.

SELECT * FROM orders QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) = 1; -- Snowflake / BigQuery / DuckDB

Remove duplicates keeping a chosen representative: number rows with ROW_NUMBER() partitioned by the dedup key, then keep rn = 1. Pick the survivor with the ORDER BY.

DELETE FROM users WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) rn FROM users
  ) t WHERE rn > 1
); -- keep the oldest row per email

Add new rows. Always list target columns explicitly — column-position INSERTs break the day someone reorders the table.

⚠ Common pitfall: Single-row INSERT in a loop is the #1 cause of "import takes 4 hours". Batch into one INSERT with many VALUES tuples or use COPY (PostgreSQL) / LOAD DATA (MySQL).

INSERT INTO users (id, name, email) VALUES (1, 'alice', 'a@example.com');

INSERT INTO users (name, email) VALUES ('a', 'a@x.com'), ('b', 'b@x.com'), ('c', 'c@x.com'); -- batch

Copy rows from one query into a table. Used for ETL, materialized snapshots, table backfills.

INSERT INTO users_archive (id, name, email, created_at) SELECT id, name, email, created_at FROM users WHERE created_at < NOW() - INTERVAL 1 YEAR;

Modify existing rows that match the predicate. The WHERE clause is non-optional in your head even when SQL allows omitting it.

⚠ Common pitfall: Run `SELECT * FROM t WHERE ...` first with the SAME WHERE clause, count the rows, THEN swap SELECT for UPDATE. A bare UPDATE without WHERE rewrites every row.

UPDATE users SET status = 'active' WHERE id = 42;

UPDATE orders SET amount = amount * 1.1 WHERE created_at < '2026-01-01';

Update one table using values from another. PostgreSQL uses UPDATE ... FROM; MySQL uses UPDATE t1 JOIN t2 SET ... .

UPDATE orders o SET amount = c.fixed_amount FROM corrections c WHERE o.id = c.order_id; -- PostgreSQL

UPDATE orders o JOIN corrections c ON o.id = c.order_id SET o.amount = c.fixed_amount; -- MySQL

Remove rows. Same WHERE-safety discipline as UPDATE — always preview with SELECT first.

⚠ Common pitfall: DELETE writes to the WAL / binlog for every row — deleting 100M rows in one statement bloats logs, holds locks, and can take the DB down. Batch by primary key range with a LIMIT.

DELETE FROM logs WHERE created_at < NOW() - INTERVAL 30 DAY LIMIT 10000; -- batch in a loop

DELETE FROM users WHERE id = 42;

Insert if absent, update if a unique key collides. PostgreSQL: ON CONFLICT (col) DO UPDATE. MySQL: ON DUPLICATE KEY UPDATE. SQLite supports both.

⚠ Common pitfall: MySQL ON DUPLICATE KEY consumes auto-increment IDs even on the update path — heavy upsert churn leaves gaps in your id sequence.

INSERT INTO users (id, name, email) VALUES (1, 'alice', 'a@x.com') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, email = EXCLUDED.email; -- PostgreSQL

INSERT INTO users (id, name) VALUES (1, 'alice') ON DUPLICATE KEY UPDATE name = VALUES(name); -- MySQL

INSERT INTO counters (key, n) VALUES ('a', 1) ON CONFLICT (key) DO UPDATE SET n = counters.n + 1; -- atomic increment

Empty a table fast — DDL-level operation, no per-row WAL. Resets auto-increment on most engines.

⚠ Common pitfall: TRUNCATE cannot be rolled back in MySQL (implicit commit). Fires no row-level triggers. Cascading FKs are NOT automatic in PostgreSQL — add CASCADE.

TRUNCATE TABLE logs;

TRUNCATE TABLE orders RESTART IDENTITY CASCADE; -- PostgreSQL

Get back the inserted / updated / deleted rows in one round trip. Saves a separate SELECT after the write.

⚠ Common pitfall: MySQL does not support RETURNING (as of 8.x). Use LAST_INSERT_ID() + SELECT, or wait for MariaDB-style support.

INSERT INTO users (name) VALUES ('alice') RETURNING id, created_at;

UPDATE orders SET status = 'paid' WHERE id = 42 RETURNING *;

DELETE FROM logs WHERE id < 100 RETURNING id;

Standard SQL UPSERT. Supported in PostgreSQL 15+, SQL Server, Oracle. More expressive than ON CONFLICT — you can DELETE on match too.

⚠ Common pitfall: MERGE has subtle races under high concurrency (the lookup and the insert are not atomic). For pure upsert, PostgreSQL INSERT ... ON CONFLICT is safer.

MERGE INTO target t USING source s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.name = s.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);

Delete rows from one table by matching another. PostgreSQL: DELETE ... USING. MySQL: DELETE t1 FROM t1 JOIN t2 ON ... .

DELETE FROM orders USING users WHERE orders.user_id = users.id AND users.deleted_at IS NOT NULL; -- PostgreSQL

DELETE o FROM orders o JOIN users u ON u.id = o.user_id WHERE u.deleted_at IS NOT NULL; -- MySQL

Insert rows, silently skipping any that violate a unique constraint. PostgreSQL / SQLite: ON CONFLICT DO NOTHING. MySQL: INSERT IGNORE.

⚠ Common pitfall: MySQL `INSERT IGNORE` swallows OTHER errors too (type truncation, FK violations become warnings) — it is broader than skipping duplicates. Prefer ON DUPLICATE KEY UPDATE id=id if you only mean duplicates.

INSERT INTO tags (name) VALUES ('sql') ON CONFLICT (name) DO NOTHING; -- PostgreSQL / SQLite

INSERT IGNORE INTO tags (name) VALUES ('sql'); -- MySQL

The fastest way to ingest a file. PostgreSQL: COPY t FROM ... . MySQL: LOAD DATA INFILE. SQLite CLI: .import. Orders of magnitude faster than row-by-row INSERT.

⚠ Common pitfall: MySQL `LOAD DATA INFILE` (server-side) needs FILE privilege + secure_file_priv path; use `LOAD DATA LOCAL INFILE` for a client file but the client must enable local-infile.

COPY users (id, name, email) FROM '/tmp/users.csv' WITH (FORMAT csv, HEADER true); -- PostgreSQL

LOAD DATA INFILE '/tmp/users.csv' INTO TABLE users FIELDS TERMINATED BY ',' IGNORE 1 LINES; -- MySQL

Read back the auto-generated key after an INSERT. PostgreSQL: prefer RETURNING id, or lastval(). MySQL: LAST_INSERT_ID(). SQLite: last_insert_rowid().

⚠ Common pitfall: LAST_INSERT_ID() is per-connection and reflects the FIRST id of a multi-row INSERT, not the last. With a connection pool, always read it on the same connection that did the INSERT.

INSERT INTO users (name) VALUES ('alice'); SELECT LAST_INSERT_ID(); -- MySQL

INSERT INTO users (name) VALUES ('alice') RETURNING id; -- PostgreSQL

Delete or update millions of rows in chunks to avoid one giant transaction that holds locks and bloats the log. Loop on a key range or LIMIT until zero rows are affected.

DELETE FROM logs WHERE created_at < NOW() - INTERVAL '90 days' AND ctid IN (SELECT ctid FROM logs WHERE created_at < NOW() - INTERVAL '90 days' LIMIT 5000); -- PostgreSQL chunk

DELETE FROM logs WHERE created_at < '2026-01-01' ORDER BY id LIMIT 5000; -- MySQL chunk, repeat until 0 rows

Update many rows to different values in one statement by putting a CASE in the SET. One table pass instead of N separate UPDATEs.

UPDATE products SET price = CASE
  WHEN category = 'book' THEN price * 0.9
  WHEN category = 'toy' THEN price * 0.8
  ELSE price
END;

Define a new table with columns, types, and constraints. The first place to enforce data quality — get it right before any INSERT.

⚠ Common pitfall: Decide NULLability and PRIMARY KEY at create time — adding NOT NULL later requires backfilling defaults on every row and may need a full table rewrite.

CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

Mark one or more columns as the row identifier. Implicitly UNIQUE + NOT NULL. Each table has exactly one PRIMARY KEY.

CREATE TABLE orders (id BIGINT PRIMARY KEY, user_id BIGINT, amount NUMERIC);

CREATE TABLE order_items (order_id BIGINT, product_id BIGINT, PRIMARY KEY (order_id, product_id));

Constrain a column to values that exist in another table. ON DELETE CASCADE / SET NULL / RESTRICT controls what happens when parent goes away.

⚠ Common pitfall: FK checks need the parent column indexed (the PK already is, but if you reference a non-PK UNIQUE column, double-check). At scale, CASCADE deletes can take down the DB.

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);

Forbid duplicate values in a column or a tuple of columns. Allows NULLs by default in most engines (multiple NULLs are not considered equal).

CREATE TABLE users (email TEXT UNIQUE);

ALTER TABLE orders ADD CONSTRAINT uq_user_day UNIQUE (user_id, DATE(created_at));

Reject rows that fail a boolean predicate at INSERT / UPDATE time. Good for enums, bounded numbers, format hints.

⚠ Common pitfall: MySQL silently IGNORED CHECK constraints until 8.0.16 — old MySQL code "had" them in DDL but never enforced. Verify with `SHOW CREATE TABLE`.

CREATE TABLE products (price NUMERIC CHECK (price >= 0));

ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age BETWEEN 0 AND 150);

Add or remove a column. ADD COLUMN with default is fast on modern PostgreSQL (≥ 11) and MySQL (≥ 8.0); on older versions it rewrites the entire table.

⚠ Common pitfall: DROP COLUMN on a huge table is a long-running DDL that holds an ACCESS EXCLUSIVE lock — schedule into a window. Online schema tools (pt-online-schema-change, gh-ost) work around it.

ALTER TABLE users ADD COLUMN last_login TIMESTAMP;

ALTER TABLE users DROP COLUMN deprecated_field;

ALTER TABLE users ADD COLUMN status TEXT NOT NULL DEFAULT 'active'; -- fast on new engines

Change the data type of an existing column. Casting must succeed for every row.

⚠ Common pitfall: Widening (INT → BIGINT, VARCHAR(50) → TEXT) is usually cheap. Narrowing or changing to incompatible types may fail or require a USING clause in PostgreSQL.

ALTER TABLE orders ALTER COLUMN amount TYPE NUMERIC(18, 4); -- PostgreSQL

ALTER TABLE users MODIFY COLUMN id BIGINT; -- MySQL

ALTER TABLE t ALTER COLUMN flag TYPE BOOLEAN USING (flag::int <> 0); -- PostgreSQL

Build a B-tree index on a column to speed up WHERE / JOIN / ORDER BY on that column. Adds write cost on every INSERT / UPDATE that touches the column.

⚠ Common pitfall: PostgreSQL: use `CREATE INDEX CONCURRENTLY` in production — plain CREATE INDEX takes a long-lived write lock. MySQL InnoDB online DDL handles most cases without that.

CREATE INDEX idx_users_email ON users (email);

CREATE INDEX CONCURRENTLY idx_orders_created ON orders (created_at); -- PostgreSQL production

CREATE UNIQUE INDEX idx_users_email_unique ON users (email);

Delete a table and all its data. IF EXISTS prevents the error when the table is already gone — handy in idempotent migrations.

⚠ Common pitfall: DROP is forever — no undo, no recycle bin. Always check `SELECT COUNT(*)` first and confirm you are on the right database.

DROP TABLE IF EXISTS old_logs;

DROP TABLE orders CASCADE; -- PostgreSQL

Save a SELECT query as a named virtual table. Queries against the view re-run the underlying SELECT every time.

⚠ Common pitfall: Views are NOT cached. For expensive aggregations use MATERIALIZED VIEW (PostgreSQL) + REFRESH MATERIALIZED VIEW.

CREATE VIEW active_users AS SELECT * FROM users WHERE deleted_at IS NULL AND status = 'active';

CREATE MATERIALIZED VIEW daily_sales AS SELECT day, SUM(amount) FROM orders GROUP BY day; -- PostgreSQL

Build an index without blocking writes on the table. Slower than a regular CREATE INDEX and CANNOT run inside a transaction block.

⚠ Common pitfall: If CREATE INDEX CONCURRENTLY fails partway, the index is left INVALID — visible to `\d table` but skipped by the planner. DROP it and retry.

CREATE INDEX CONCURRENTLY idx_orders_created ON orders (created_at);

Supply the value used when an INSERT omits the column. Can be a literal, CURRENT_TIMESTAMP, or in PostgreSQL an expression / sequence.

⚠ Common pitfall: A DEFAULT only fires when the column is OMITTED. Explicitly inserting NULL stores NULL, not the default. Use COALESCE in the app or a BEFORE trigger if you need NULL → default.

CREATE TABLE t (status TEXT NOT NULL DEFAULT 'pending', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);

ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active'; -- PostgreSQL

A column whose value is derived from other columns. STORED persists it; VIRTUAL computes on read. Great for indexing a JSON field or a normalized form.

⚠ Common pitfall: You cannot INSERT / UPDATE a generated column directly — it is read-only. PostgreSQL only supports STORED (no VIRTUAL yet).

ALTER TABLE products ADD COLUMN price_with_tax NUMERIC GENERATED ALWAYS AS (price * 1.1) STORED;

ALTER TABLE events ADD COLUMN event_type TEXT GENERATED ALWAYS AS (data->>'type') STORED; -- PostgreSQL, then index it

Rename objects in place. PostgreSQL: ALTER TABLE ... RENAME TO / RENAME COLUMN. MySQL: RENAME TABLE or ALTER TABLE ... RENAME COLUMN (8.0+).

⚠ Common pitfall: Renaming a column is metadata-only and instant, but breaks every query, view, and app that referenced the old name. Coordinate a deploy or add the new name as a generated alias first.

ALTER TABLE users RENAME COLUMN uname TO username; -- PostgreSQL / MySQL 8

RENAME TABLE old_users TO users; -- MySQL

Three ways to constrain a column to a fixed set. MySQL ENUM is compact but painful to alter; PostgreSQL has a real ENUM type; a CHECK (col IN (...)) or a small FK lookup table are the most portable.

⚠ Common pitfall: Adding a value to a MySQL ENUM rewrites the table on old versions; PostgreSQL ALTER TYPE ... ADD VALUE cannot run inside a transaction and cannot be removed. A lookup table avoids both.

CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped'); -- PostgreSQL

ALTER TABLE orders ADD CONSTRAINT chk_status CHECK (status IN ('pending', 'paid', 'shipped')); -- portable

A table that exists only for the current session / transaction and is dropped automatically. Useful for staging bulk loads or holding intermediate results.

⚠ Common pitfall: PostgreSQL ON COMMIT DROP / DELETE ROWS controls transaction-level lifetime. MySQL temp tables are session-scoped and can hide a real table of the same name — naming clashes are silent.

CREATE TEMPORARY TABLE staging (id BIGINT, payload JSONB); -- session-scoped

CREATE TEMP TABLE t ON COMMIT DROP AS SELECT * FROM orders WHERE status = 'pending'; -- PostgreSQL

Split a big table into physical partitions by a key (often a date range). The planner prunes irrelevant partitions, and you can drop old data by detaching a partition instantly.

⚠ Common pitfall: The partition key MUST be part of the primary key / unique constraint in PostgreSQL declarative partitioning. Plan the key before you have billions of rows.

CREATE TABLE logs (id BIGINT, created_at DATE) PARTITION BY RANGE (created_at);
CREATE TABLE logs_2026_01 PARTITION OF logs FOR VALUES FROM ('2026-01-01') TO ('2026-02-01'); -- PostgreSQL

Adding NOT NULL to an existing nullable column fails if any row is NULL. Roll it out in steps: backfill the NULLs, then add a validated CHECK or the NOT NULL constraint.

⚠ Common pitfall: In PostgreSQL, `SET NOT NULL` scans the whole table under a lock. A trick: add `CHECK (col IS NOT NULL) NOT VALID`, then `VALIDATE CONSTRAINT` (non-blocking), avoiding the long lock.

UPDATE users SET status = 'active' WHERE status IS NULL; -- backfill first

ALTER TABLE users ADD CONSTRAINT chk_status_nn CHECK (status IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT chk_status_nn; -- PostgreSQL non-blocking validate

Open a transaction — subsequent writes are not visible to other sessions until COMMIT (under read-committed and above).

⚠ Common pitfall: A transaction left open holds locks and bloats undo / WAL. App connection pools that hide BEGIN inside a wrapper plus an exception cause "idle in transaction" forever — set `idle_in_transaction_session_timeout`.

BEGIN;

START TRANSACTION;

START TRANSACTION READ ONLY;

COMMIT makes all pending writes permanent and visible. ROLLBACK discards them. Every BEGIN must end in one of these.

COMMIT;

ROLLBACK;

Nest a checkpoint inside a transaction so you can roll back PART of it. Useful for "try this batch; if it fails, undo just this batch and continue".

BEGIN;
INSERT INTO users (id, name) VALUES (1, 'a');
SAVEPOINT sp1;
INSERT INTO orders (user_id) VALUES (1);
ROLLBACK TO SAVEPOINT sp1;
COMMIT;

Choose isolation: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. Higher = fewer anomalies + lower throughput + more deadlocks.

⚠ Common pitfall: Default differs: PostgreSQL = READ COMMITTED, MySQL InnoDB = REPEATABLE READ, Oracle = READ COMMITTED. Same SQL behaves differently on different engines.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- MySQL

Take a row-level write lock on the rows returned by the SELECT. Held until the transaction commits or rolls back. The right primitive for "read-modify-write" without race conditions.

⚠ Common pitfall: FOR UPDATE without a covering index can lock far more rows than expected (or whole gaps in MySQL InnoDB REPEATABLE READ). Confirm with EXPLAIN and gap-lock awareness.

BEGIN;
SELECT * FROM accounts WHERE id = 42 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 42;
COMMIT;

SELECT * FROM jobs WHERE status = 'queued' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; -- worker queue

When two transactions hold a lock the other needs, the engine kills one (the "victim") with a deadlock error. Avoid by always locking rows in the SAME ORDER across transactions.

⚠ Common pitfall: Application code must retry on deadlock — it is a normal outcome, not a bug in the SQL engine. Log + retry with backoff.

-- MySQL: SHOW ENGINE INNODB STATUS \G  -- latest deadlock
-- PostgreSQL: log line 'deadlock detected', error SQLSTATE 40P01

Lock the rows you grab and skip any already locked by another worker. The canonical pattern for a concurrent worker queue inside a relational database — no double processing.

⚠ Common pitfall: Pair with FOR UPDATE SKIP LOCKED + LIMIT inside a transaction, then mark the row done and COMMIT. Forgetting to commit holds the lock and stalls every other worker.

BEGIN;
SELECT id FROM jobs WHERE status = 'queued' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
-- ... process, then:
UPDATE jobs SET status = 'done' WHERE id = :id;
COMMIT;

Instead of holding a lock, add a version column and update WHERE version = old_version. If zero rows are affected, someone else changed the row — retry. Scales better than pessimistic locks for low-contention writes.

UPDATE accounts SET balance = :new, version = version + 1 WHERE id = :id AND version = :expected_version; -- 0 rows affected → retry

Increment in a single UPDATE statement so the read and write are atomic at the row level. Never SELECT then UPDATE with the new value in app code — two clients will clobber each other.

⚠ Common pitfall: Read-modify-write in the app (SELECT n; n = n + 1; UPDATE SET n = n) loses updates under concurrency. `SET n = n + 1` in one statement is safe.

UPDATE counters SET n = n + 1 WHERE key = 'page_views';

INSERT INTO counters (key, n) VALUES ('page_views', 1) ON CONFLICT (key) DO UPDATE SET n = counters.n + 1; -- PostgreSQL upsert counter

A named lock not tied to any row, used to serialize an application section (e.g. ensure only one cron runs). PostgreSQL: pg_advisory_lock(key). MySQL: GET_LOCK(name, timeout).

⚠ Common pitfall: A session-level advisory lock is NOT released on transaction rollback — only on explicit unlock or session end. Use pg_advisory_xact_lock for transaction-scoped auto-release.

SELECT pg_advisory_xact_lock(42); -- PostgreSQL, auto-released at COMMIT/ROLLBACK

SELECT GET_LOCK('nightly_job', 10); -- MySQL, returns 1 if acquired within 10s

Two transactions read the same value, each computes a new one, and the second COMMIT overwrites the first — one update is lost. Prevent with SELECT ... FOR UPDATE, an atomic `SET x = x + 1`, or optimistic version checks.

-- LOST UPDATE risk (app reads then writes):
SELECT balance FROM accounts WHERE id = 1; -- both read 100
UPDATE accounts SET balance = 90 WHERE id = 1; -- second clobbers first
-- SAFE:
UPDATE accounts SET balance = balance - 10 WHERE id = 1;

Show the execution plan the optimizer picked. ANALYZE actually runs the query and reports real row counts / timings.

⚠ Common pitfall: EXPLAIN ANALYZE on an UPDATE / DELETE actually executes the write — wrap in a transaction and ROLLBACK if you only want to inspect.

EXPLAIN SELECT * FROM users WHERE email = 'a@x.com';

EXPLAIN ANALYZE SELECT * FROM orders WHERE created_at > NOW() - INTERVAL 1 DAY; -- PostgreSQL

EXPLAIN FORMAT=JSON SELECT * FROM users; -- MySQL

Index on multiple columns. Only useful for filters that match a LEFTMOST PREFIX — (a), (a, b), (a, b, c) all use the index; (b), (c), (b, c) do not.

⚠ Common pitfall: Column order matters. Put the highest-selectivity filter first if you mostly filter on it; put the column you ORDER BY in last position to enable index-only sort.

CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);

-- Uses index:
SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC;
-- Does NOT use this index (skips leftmost):
SELECT * FROM orders WHERE created_at > '2026-01-01';

Index that contains every column the query needs — the engine can answer from the index alone without touching the table heap.

CREATE INDEX idx_orders_cover ON orders (user_id) INCLUDE (amount, status); -- PostgreSQL ≥ 11

CREATE INDEX idx_orders_cover ON orders (user_id, amount, status); -- MySQL covering (all in B-tree)

Wrapping the indexed column in a function (`WHERE LOWER(email) = ...`), implicit type cast (`WHERE int_col = '42'`), leading wildcard (`LIKE '%foo'`), OR across different indexed columns. EXPLAIN exposes all of these.

⚠ Common pitfall: Fix with: functional index on the expression, match the type in the query, change `%foo` to `foo%` or use a trigram / FTS index, rewrite OR as UNION ALL.

CREATE INDEX idx_users_email_lower ON users (LOWER(email)); -- functional index

SELECT * FROM users WHERE id = 42; -- not '42', integer literal

Index only the subset of rows that match a predicate. Saves space and write cost when most rows do not match the common filter (e.g. only active rows).

CREATE INDEX idx_active_users_email ON users (email) WHERE deleted_at IS NULL; -- PostgreSQL

Refresh the planner statistics so it picks good plans. Run after a big bulk load or a table churn that shifted the distribution.

ANALYZE users; -- PostgreSQL

ANALYZE TABLE users; -- MySQL

VACUUM ANALYZE users; -- PostgreSQL: reclaim space AND refresh stats

PostgreSQL extension that records normalized query stats: total time, mean time, calls, rows. The fastest way to find the actual slow query in production.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, mean_exec_time, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

A GIN (Generalized Inverted Index) speeds up containment queries on JSONB, arrays, and tsvector full-text. The right index for `@>`, `?`, `&&`, and `@@` operators.

⚠ Common pitfall: A plain B-tree index does nothing for `data @> '{"k":"v"}'`. You need GIN. For equality on a single JSON key, an expression B-tree on `(data->>'k')` is often cheaper.

CREATE INDEX idx_events_data ON events USING GIN (data); -- PostgreSQL JSONB containment

SELECT * FROM events WHERE data @> '{"type":"signup"}'; -- uses the GIN index

Index the RESULT of a function so a query that wraps the column in the same function can still use an index. The fix for `WHERE LOWER(email) = ...` going full-scan.

⚠ Common pitfall: The query expression must MATCH the indexed expression exactly. `LOWER(email)` index does not help `UPPER(email)` or `email LIKE`. MySQL supports functional indexes only from 8.0.13.

CREATE INDEX idx_users_email_lower ON users (LOWER(email)); -- PostgreSQL / MySQL 8

SELECT * FROM users WHERE LOWER(email) = 'a@x.com'; -- now uses the index

An index only helps when it narrows results a lot. A boolean column where 90% of rows are `true` is poorly selective — the planner often skips the index and scans instead.

⚠ Common pitfall: Indexing a low-cardinality column (gender, status with 3 values) alone rarely pays off. Combine it as the leading column of a composite index with a selective second column, or use a partial index.

CREATE INDEX idx_orders_status_created ON orders (status, created_at); -- composite beats lone status

CREATE INDEX idx_pending ON orders (created_at) WHERE status = 'pending'; -- PostgreSQL partial

Most engines auto-index the PRIMARY KEY but NOT the child-side FK column. Without it, every parent DELETE / UPDATE scans the child table, and joins on the FK are slow.

⚠ Common pitfall: PostgreSQL never auto-creates an index on the referencing column — a classic source of slow cascading deletes. MySQL InnoDB DOES auto-create one. Always check.

CREATE INDEX idx_orders_user_id ON orders (user_id); -- the FK column, PostgreSQL needs this manually

In a plan, Seq Scan / full table scan reads every row; Index Scan / ref / range uses an index. A Seq Scan on a large table with a selective filter usually signals a missing or unused index.

⚠ Common pitfall: A Seq Scan is not always bad — for a small table or a filter that matches most rows, scanning is faster than random index lookups. Judge by the table size and estimated row count.

EXPLAIN SELECT * FROM orders WHERE user_id = 42; -- want Index Scan, not Seq Scan

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'paid'; -- check actual rows vs estimate

When every column a query needs is in the index, the engine answers from the index alone without reading the table — an index-only scan. The single biggest win for hot read paths.

⚠ Common pitfall: PostgreSQL needs the visibility map fresh (run VACUUM) for index-only scans; otherwise it still checks the heap for row visibility. MySQL InnoDB always stores the PK in secondary indexes.

CREATE INDEX idx_cover ON orders (user_id) INCLUDE (status); -- PostgreSQL covering

SELECT user_id, status FROM orders WHERE user_id = 42; -- answered from index alone

MySQL: `BIGINT AUTO_INCREMENT PRIMARY KEY`. PostgreSQL: `BIGSERIAL PRIMARY KEY` or `GENERATED ALWAYS AS IDENTITY`. SQLite: `INTEGER PRIMARY KEY AUTOINCREMENT`.

CREATE TABLE t (id BIGINT AUTO_INCREMENT PRIMARY KEY); -- MySQL

CREATE TABLE t (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY); -- PostgreSQL standard

CREATE TABLE t (id INTEGER PRIMARY KEY AUTOINCREMENT); -- SQLite

MySQL / PostgreSQL / SQLite: `LIMIT n OFFSET m`. SQL Server: `OFFSET m ROWS FETCH NEXT n ROWS ONLY`. Oracle pre-12: subquery + ROWNUM.

SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 100;

SELECT * FROM users ORDER BY id OFFSET 100 ROWS FETCH NEXT 20 ROWS ONLY; -- SQL Server / standard

PostgreSQL / SQLite / standard: `a || b` (NULL propagates). MySQL: `CONCAT(a, b)` (treats NULL as empty in CONCAT_WS, NULL → NULL in CONCAT). SQL Server: `+`.

⚠ Common pitfall: MySQL `a || b` is BOOLEAN OR by default — not string concat. Set `sql_mode=PIPES_AS_CONCAT` if you need portable code.

SELECT 'hello' || ' ' || 'world'; -- PostgreSQL / SQLite

SELECT CONCAT('hello', ' ', 'world'); -- MySQL / portable

SELECT CONCAT_WS(' ', 'hello', NULL, 'world'); -- MySQL: 'hello world'

`NOW()` works in MySQL and PostgreSQL. `CURRENT_TIMESTAMP` is standard and portable. SQLite: `CURRENT_TIMESTAMP` or `datetime('now')`.

SELECT NOW();

SELECT CURRENT_TIMESTAMP;

SELECT datetime('now'); -- SQLite

MySQL: `NOW() - INTERVAL 7 DAY`. PostgreSQL / standard: `NOW() - INTERVAL '7 days'`. SQLite: `datetime('now', '-7 days')`.

SELECT NOW() - INTERVAL 7 DAY; -- MySQL

SELECT NOW() - INTERVAL '7 days'; -- PostgreSQL

SELECT datetime('now', '-7 days'); -- SQLite

PostgreSQL has native BOOLEAN. MySQL uses TINYINT(1) under the alias BOOLEAN. SQLite stores 0 / 1 in any column.

CREATE TABLE t (active BOOLEAN NOT NULL DEFAULT TRUE); -- PostgreSQL

CREATE TABLE t (active TINYINT(1) NOT NULL DEFAULT 1); -- MySQL

PostgreSQL: prefer JSONB (binary, indexable). MySQL: JSON (validated, stored as binary). SQLite: TEXT + json1 extension functions.

⚠ Common pitfall: PostgreSQL JSON preserves whitespace + key order; JSONB does not. Use JSON only if your app cares about exact text round-trip.

SELECT data->>'name' FROM events WHERE data->>'event' = 'signup'; -- PostgreSQL

SELECT JSON_EXTRACT(data, '$.name') FROM events; -- MySQL

SELECT json_extract(data, '$.name') FROM events; -- SQLite json1

Standard SQL: single quotes for strings, double quotes for identifiers. MySQL default: double quotes are also strings — set `sql_mode=ANSI_QUOTES` for portability.

SELECT 'hello' AS greeting, "user_id" FROM users; -- standard / PostgreSQL

SELECT `user_id` FROM users; -- MySQL backtick quotes identifiers always

PostgreSQL folds unquoted identifiers to lowercase ("Users" → users). MySQL lowercases on Linux/Mac but preserves case on Windows (`lower_case_table_names`). String compare is case-insensitive in MySQL utf8mb4_general_ci collation, case-sensitive in PostgreSQL.

SELECT * FROM "Users"; -- PostgreSQL: case-sensitive when quoted

SELECT * FROM users WHERE LOWER(email) = LOWER('A@x.com'); -- portable case-insensitive compare

PostgreSQL: gen_random_uuid() (built in since 13, or pgcrypto). MySQL: UUID() (v1, time-based) or UUID_TO_BIN for compact storage. SQLite: no built-in, generate in the app.

⚠ Common pitfall: Random UUID v4 primary keys hurt insert locality (random B-tree pages) on huge tables. Consider a time-ordered key (UUID v7, ULID) or store UUIDs as BINARY(16) in MySQL to save space.

SELECT gen_random_uuid(); -- PostgreSQL 13+

SELECT UUID(); -- MySQL

INSERT INTO t (id) VALUES (UUID_TO_BIN(UUID())); -- MySQL compact 16-byte storage

PostgreSQL: `~` (case-sensitive), `~*` (insensitive), or regexp_match. MySQL: REGEXP / RLIKE. SQLite: REGEXP only if the app registers a function. Standard SIMILAR TO is rarely used.

⚠ Common pitfall: Regex matching cannot use a normal index — it always scans. For prefix patterns prefer LIKE 'foo%'; for fuzzy search use a trigram (pg_trgm) or full-text index.

SELECT * FROM users WHERE email ~* '^[a-z]+@gmail\.com$'; -- PostgreSQL case-insensitive

SELECT * FROM users WHERE phone REGEXP '^1[3-9][0-9]{9}$'; -- MySQL CN mobile

Get written rows back inline. PostgreSQL / SQLite / MariaDB: RETURNING. SQL Server: OUTPUT inserted.* / deleted.* . MySQL (8.x): not supported — use LAST_INSERT_ID() then SELECT.

UPDATE orders SET status = 'paid' WHERE id = 1 RETURNING id, status; -- PostgreSQL / SQLite

DELETE FROM logs OUTPUT deleted.id WHERE id < 100; -- SQL Server

MySQL / PostgreSQL / SQLite: LIMIT n. SQL Server: SELECT TOP n. Standard / Oracle 12c+: FETCH FIRST n ROWS ONLY. WITH TIES keeps rows tied with the nth.

SELECT * FROM products ORDER BY price DESC LIMIT 5; -- MySQL / PostgreSQL

SELECT TOP 5 * FROM products ORDER BY price DESC; -- SQL Server

SELECT * FROM products ORDER BY price DESC FETCH FIRST 5 ROWS WITH TIES; -- standard, keep ties

Shorthand for a two-branch CASE. MySQL: IF(cond, a, b). SQL Server / SQLite: IIF(cond, a, b). PostgreSQL has no IF expression — use CASE or COALESCE / NULLIF tricks.

SELECT IF(age >= 18, 'adult', 'minor') FROM users; -- MySQL

SELECT CASE WHEN age >= 18 THEN 'adult' ELSE 'minor' END FROM users; -- portable

MySQL allows LIMIT on UPDATE / DELETE for batching. PostgreSQL does NOT — you must use a subselect on the primary key (ctid or id) with LIMIT instead.

DELETE FROM logs WHERE created_at < '2026-01-01' LIMIT 1000; -- MySQL

DELETE FROM logs WHERE id IN (SELECT id FROM logs WHERE created_at < '2026-01-01' LIMIT 1000); -- PostgreSQL

PostgreSQL / SQLite: INSERT ... ON CONFLICT (key) DO UPDATE SET .... MySQL: INSERT ... ON DUPLICATE KEY UPDATE .... SQL Server / Oracle / standard: MERGE.

INSERT INTO kv (k, v) VALUES ('a', 1) ON CONFLICT (k) DO UPDATE SET v = EXCLUDED.v; -- PostgreSQL / SQLite

INSERT INTO kv (k, v) VALUES ('a', 1) ON DUPLICATE KEY UPDATE v = VALUES(v); -- MySQL

Comparisons with NULL return UNKNOWN, not TRUE or FALSE. `WHERE col = NULL`, `WHERE col != NULL`, even `WHERE NULL = NULL` are all UNKNOWN (treated as false in WHERE).

⚠ Common pitfall: Use `IS NULL` / `IS NOT NULL` / `IS DISTINCT FROM` (PostgreSQL standard) / `<=>` (MySQL NULL-safe equals) instead.

SELECT * FROM users WHERE deleted_at IS NULL;

SELECT * FROM users WHERE last_login IS DISTINCT FROM previous_login; -- PostgreSQL: NULL-safe inequality

SELECT * FROM users WHERE col <=> NULL; -- MySQL NULL-safe equals

Every SELECTed column that is not inside an aggregate function must appear in GROUP BY. PostgreSQL / modern MySQL enforce it; old MySQL silently returned a random row.

⚠ Common pitfall: Turn on `only_full_group_by` in MySQL config to surface latent bugs before they corrupt reports.

-- WRONG (old MySQL silently allowed):
SELECT country, name FROM users GROUP BY country;
-- RIGHT:
SELECT country, MAX(name) FROM users GROUP BY country;
-- OR:
SELECT country, name FROM users GROUP BY country, name;

Building SQL by string concatenation with user input enables `' OR 1=1; DROP TABLE users; --` style attacks. ALWAYS use parameterized / prepared statements.

⚠ Common pitfall: ORMs and DB drivers (psycopg, pg, mysql2, sqlx, prisma) all provide `?` or `$1` placeholders for free — there is no reason to ever interpolate.

-- WRONG (vulnerable):
-- query('SELECT * FROM users WHERE id = ' + userInput);
-- RIGHT:
-- query('SELECT * FROM users WHERE id = ?', [userInput]);   // mysql2 / sqlite
-- query('SELECT * FROM users WHERE id = $1', [userInput]);  // pg / postgres

Filtering on the right-side table in WHERE drops the rows where the right side is NULL — turning a LEFT JOIN into an INNER JOIN by accident.

⚠ Common pitfall: Move the right-side predicate into the ON clause, OR allow NULL explicitly with `WHERE r.col = X OR r.col IS NULL`.

-- WRONG (drops users with no orders):
SELECT u.* FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE o.status = 'paid';
-- RIGHT:
SELECT u.* FROM users u LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'paid';

Forgetting the WHERE clause on DELETE or UPDATE rewrites every row. The classic Friday-evening incident. Treat as the #1 reason to enable safe-updates mode.

⚠ Common pitfall: MySQL: `SET SQL_SAFE_UPDATES = 1`. PostgreSQL: write a trigger or use a tool like pg_safeupdate. As a habit, always SELECT first with the same WHERE.

-- DISASTER:
DELETE FROM users; -- deletes everything
-- ALWAYS:
SELECT * FROM users WHERE id = 42; -- confirm one row
DELETE FROM users WHERE id = 42;

COUNT(col) only counts non-NULL values. To count conditionally use `SUM(CASE WHEN ... THEN 1 ELSE 0 END)` or `COUNT(*) FILTER (WHERE ...)`.

SELECT SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_count, COUNT(*) AS total FROM orders;

SELECT COUNT(*) FILTER (WHERE status = 'paid') FROM orders; -- PostgreSQL

Using FLOAT / DOUBLE for currency makes 0.1 + 0.2 = 0.30000000000000004. Always store money in NUMERIC / DECIMAL with explicit scale.

CREATE TABLE orders (amount NUMERIC(18, 4) NOT NULL); -- safe for money

-- WRONG:
CREATE TABLE orders (amount DOUBLE);

MySQL silently converts a string literal to a number when comparing to an INT column, but the OPPOSITE — comparing an INT column to a long string — converts the column instead and skips the index. Same trap for character set mismatches.

-- Index used (literal cast):
SELECT * FROM users WHERE id = '42';
-- Index SKIPPED (column cast):
SELECT * FROM users WHERE phone_number_int = '13800001234';  -- if column is INT and literal is wider

Listing tables with a comma without a WHERE join condition (or with the wrong ON) multiplies row counts. 1k × 1k = 1M rows out of nowhere.

-- DISASTER (no join condition):
SELECT * FROM users u, orders o;
-- RIGHT:
SELECT * FROM users u JOIN orders o ON o.user_id = u.id;

Filtering a non-aggregate condition in HAVING works but runs AFTER grouping the full table — wasted work. Push the filter into WHERE so fewer rows are grouped.

-- SLOW (groups all, then filters):
SELECT country, COUNT(*) FROM users GROUP BY country HAVING country = 'CN';
-- FAST:
SELECT country, COUNT(*) FROM users WHERE country = 'CN' GROUP BY country;

Dividing two integers performs integer division in PostgreSQL, SQLite, and SQL Server — `7 / 2 = 3`, not 3.5. Cast one operand to numeric / float first.

⚠ Common pitfall: MySQL `/` returns a decimal (7/2 = 3.5) but `DIV` is integer division. Know your engine before computing rates / percentages.

SELECT 7 / 2; -- PostgreSQL: 3

SELECT 7::numeric / 2; -- PostgreSQL: 3.5

SELECT 100.0 * paid / total FROM stats; -- force float by using 100.0

PostgreSQL TIMESTAMP (without tz) stores wall-clock with no offset; TIMESTAMPTZ stores an absolute instant (UTC) and converts on display. Mixing them silently shifts times.

⚠ Common pitfall: Always store instants as TIMESTAMPTZ (PostgreSQL) or UTC DATETIME (MySQL has no real tz type — store UTC and convert in the app). Comparing a tz and non-tz value applies the session timezone implicitly.

CREATE TABLE events (occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW()); -- PostgreSQL, store absolute instant

SET TIME ZONE 'UTC'; SELECT occurred_at FROM events; -- view in UTC

A WHERE with OR over two different columns (`a = 1 OR b = 2`) often cannot use either single-column index efficiently. Rewrite as UNION of two indexed lookups.

-- May scan:
SELECT * FROM users WHERE email = 'a@x.com' OR phone = '13800001234';
-- Index-friendly:
SELECT * FROM users WHERE email = 'a@x.com'
UNION
SELECT * FROM users WHERE phone = '13800001234';

COUNT(col) ignores NULL rows, so a "total" computed as COUNT(some_nullable_col) under-counts. Use COUNT(*) for true row count.

-- under-counts if email can be NULL:
SELECT COUNT(email) FROM users;
-- true row count:
SELECT COUNT(*) FROM users;

CHAR(n) pads with trailing spaces and many engines ignore trailing spaces in `=` comparisons (per SQL standard PAD SPACE), so `'a' = 'a '` can be TRUE. LIKE does NOT ignore them.

⚠ Common pitfall: This means `WHERE name = 'a'` may match `'a '` but `WHERE name LIKE 'a'` will not. Prefer VARCHAR over CHAR and TRIM inputs to avoid surprises.

SELECT 'a' = 'a '; -- may return 1 (true) under PAD SPACE semantics

SELECT * FROM t WHERE TRIM(code) = 'a'; -- explicit, predictable

`created_at BETWEEN '2026-01-01' AND '2026-01-31'` treats the upper bound as midnight, excluding almost all of Jan 31. Use a half-open range `>= start AND < next_start`.

-- WRONG:
SELECT * FROM orders WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31';
-- RIGHT:
SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01';

MySQL's legacy `utf8` charset stores at most 3 bytes per character and cannot hold 4-byte characters like most emoji or some CJK extension characters. Use `utf8mb4` everywhere.

⚠ Common pitfall: Inserting a 4-byte char into a utf8 (3-byte) column either errors or silently truncates the string at that point. Set charset AND collation to utf8mb4 on the column, table, and connection.

ALTER TABLE messages CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE t (body TEXT) CHARACTER SET utf8mb4;

A SELECT with an aggregate and no GROUP BY returns exactly one row over the whole table. Adding a non-aggregate column to it is the GROUP BY error — the aggregate has nothing to align that column to.

SELECT COUNT(*), SUM(amount) FROM orders; -- one row, fine
-- ERROR (name is not aggregated and no GROUP BY):
-- SELECT name, COUNT(*) FROM users;

Any arithmetic with NULL yields NULL (`5 + NULL = NULL`), and standard string concat with NULL yields NULL (`'a' || NULL = NULL`). One missing value can blank out a whole computed column.

⚠ Common pitfall: Wrap nullable operands in COALESCE before arithmetic / concat: `COALESCE(a, 0) + b`, `first_name || COALESCE(' ' || middle, '')`. MySQL CONCAT_WS skips NULLs by design.

SELECT 5 + NULL; -- NULL

SELECT COALESCE(discount, 0) + price FROM products; -- safe