One task · six dialects

The same SQL, in every dialect.

Running totals, top-N per group, dedup with ROW_NUMBER, pivot/unpivot, upsert, JSON and recursive CTEs — each shown in MySQL, PostgreSQL, SQLite, BigQuery, Snowflake and DuckDB, with the gotchas that bite between them.

40 recipes

• Query Handle NULLs — COALESCE, NULLIF, and IS DISTINCT FROM Three NULL-handling tools in one recipe — COALESCE for defaults, NULLIF to dodge divide-by-zero, and null-safe equality with IS DISTINCT FROM (MySQL's <=>) — across MySQL, PostgreSQL, SQLite, BigQuery, Snowflake and DuckDB. 3 min →
• Query EXISTS vs IN vs JOIN for "has a matching row" Three ways to test whether a matching row exists — EXISTS, IN, and JOIN + DISTINCT — with identical syntax across MySQL, PostgreSQL, SQLite, BigQuery, Snowflake and DuckDB, and the NOT IN + NULL trap that catches all six. 3 min →
• Query FULL OUTER JOIN (and emulating it in MySQL) Keep unmatched rows from both sides of a join with FULL OUTER JOIN — native in PostgreSQL, SQLite 3.39+, BigQuery, Snowflake, DuckDB — and the LEFT JOIN + anti-joined RIGHT JOIN emulation MySQL needs, since it has none. 3 min →
• Query Run a subquery per row with LATERAL / CROSS APPLY A subquery in FROM that can reference columns from an earlier table — e.g. the top 3 orders per customer with extra derived columns. PostgreSQL, MySQL 8.0.14+, Snowflake, DuckDB, and the BigQuery/SQLite alternatives. 3 min →
• Query Select a random sample of rows Pick N random rows with ORDER BY RANDOM()/RAND() LIMIT — and when to switch to real table sampling (Snowflake SAMPLE, BigQuery TABLESAMPLE SYSTEM, DuckDB USING SAMPLE, PostgreSQL TABLESAMPLE) so you don't sort the whole table. 3 min →
• Query Fast pagination without OFFSET (keyset / seek method) Page through large result sets with a WHERE cursor instead of OFFSET, so page 10,000 is as fast as page 1. Works in MySQL, PostgreSQL, SQLite, BigQuery, Snowflake and DuckDB. 3 min →
• Window functions Find gaps and consecutive runs (gaps and islands) Group consecutive rows into runs ("islands") and find the breaks between them ("gaps") with the classic ROW_NUMBER() subtraction trick — MySQL, PostgreSQL, SQLite, BigQuery, Snowflake, DuckDB. 3 min →
• Window functions Running (cumulative) MIN and MAX A row-by-row running minimum and maximum with MIN()/MAX() OVER and a ROWS frame — same syntax in MySQL, PostgreSQL, SQLite, BigQuery, Snowflake and DuckDB — plus the tie-order gotcha. 2 min →
• Window functions First and last value per group (FIRST_VALUE / LAST_VALUE) Get the first or last row's value within each group using window functions — and the frame trick LAST_VALUE needs to actually return the last row. Works in MySQL, PostgreSQL, SQLite, BigQuery, Snowflake, DuckDB. 3 min →
• Window functions Split rows into equal buckets (NTILE quartiles & deciles) Use NTILE to divide ordered rows into N equal groups — quartiles, deciles, percentile buckets. Same syntax in MySQL, PostgreSQL, SQLite, BigQuery, Snowflake and DuckDB. 2 min →
• Window functions Compare a row to the previous one (LAG / LEAD) Day-over-day change and previous/next row values with LAG and LEAD, the same way in MySQL, PostgreSQL, SQLite, BigQuery, Snowflake and DuckDB. 2 min →
• Window functions Moving average (rolling window) in SQL A rolling N-row average with AVG() OVER and a ROWS frame — the same syntax in MySQL, PostgreSQL, SQLite, BigQuery, Snowflake and DuckDB, plus calendar windows with RANGE. 2 min →
• Window functions Percent of total (and percent of group) Each row's share of the grand total or of its group with SUM() OVER () — plus the integer-division trap — in MySQL, PostgreSQL, SQLite, BigQuery, Snowflake and DuckDB. 2 min →
• Window functions ROW_NUMBER vs RANK vs DENSE_RANK The three ranking window functions side by side — how they treat ties, with the same syntax in MySQL, PostgreSQL, SQLite, BigQuery, Snowflake and DuckDB. 2 min →
• Window functions Running total (cumulative sum) in SQL A row-by-row running total with SUM() OVER, in MySQL, PostgreSQL, SQLite, BigQuery, Snowflake and DuckDB — including the ROWS-vs-RANGE frame trap. 3 min →
• Window functions Top-N rows per group in SQL Get the top N rows per group (e.g. top 3 orders per customer) with ROW_NUMBER — QUALIFY in BigQuery/Snowflake/DuckDB, a subquery in MySQL/PostgreSQL/SQLite, DISTINCT ON in Postgres. 3 min →
• Grouping & aggregation Aggregate values into an array (ARRAY_AGG / JSON_ARRAYAGG) Collapse grouped rows into an array or list per group — array_agg, JSON_ARRAYAGG, json_group_array/list — across MySQL, PostgreSQL, SQLite, BigQuery, Snowflake and DuckDB, with ordering inside the aggregate. 3 min →
• Grouping & aggregation Filter groups with HAVING (find duplicates & above-average groups) GROUP BY ... HAVING COUNT(*) > 1 to find duplicate keys, plus filtering groups against their own average — identical syntax in MySQL, PostgreSQL, SQLite, BigQuery, Snowflake, DuckDB, and where HAVING differs from WHERE. 2 min →
• Grouping & aggregation Bucket values into a histogram Group numeric values into equal-width buckets with WIDTH_BUCKET where it exists (PostgreSQL, DuckDB, Snowflake), and a portable FLOOR()/CASE fallback for MySQL, SQLite and BigQuery, which don't have it. 3 min →
• Grouping & aggregation Weighted average: SUM(x*w)/SUM(w) The weighted-average formula and the NULLIF/SAFE_DIVIDE guard against a zero-weight divide-by-zero — identical in MySQL, PostgreSQL, SQLite, BigQuery, Snowflake, DuckDB, none of which have a native weighted-average aggregate. 2 min →
• Grouping & aggregation Subtotals and grand totals with ROLLUP Add per-group subtotals and a grand total in one query with GROUP BY ROLLUP / GROUPING SETS — with the SQLite workaround. MySQL, PostgreSQL, BigQuery, Snowflake, DuckDB. 3 min →
• Grouping & aggregation Conditional count and sum (COUNT/SUM with a condition) Count or sum only the rows that match a condition in one pass — CASE, the FILTER clause, COUNTIF and COUNT_IF across MySQL, PostgreSQL, SQLite, BigQuery, Snowflake and DuckDB. 2 min →
• Grouping & aggregation Concatenate rows into one string per group Aggregate values into a comma-separated list — STRING_AGG, GROUP_CONCAT and LISTAGG across MySQL, PostgreSQL, SQLite, BigQuery, Snowflake and DuckDB, with the ordering and length traps. 3 min →
• Grouping & aggregation Median and percentiles in SQL Compute the median (and any percentile) with PERCENTILE_CONT where it exists, and a window-function workaround for MySQL and SQLite, which have no built-in. 3 min →
• Grouping & aggregation Pivot rows into columns Turn row values into columns (e.g. quarters across the top) with portable conditional aggregation, plus the PIVOT operator in BigQuery, Snowflake and DuckDB. 3 min →
• Grouping & aggregation Unpivot columns into rows Turn wide columns (q1, q2, q3, q4) into rows with portable UNION ALL or a LATERAL VALUES join, plus the UNPIVOT operator in BigQuery, Snowflake and DuckDB. 3 min →
• Insert, update & upsert Conditional UPDATE with CASE Update different rows differently in a single UPDATE statement using CASE WHEN inside SET — identical syntax in MySQL, PostgreSQL, SQLite, BigQuery, Snowflake and DuckDB, plus the missing-ELSE trap. 2 min →
• Insert, update & upsert Keep only the latest row per group (dedupe with ROW_NUMBER) SELECT just the most recent row per key from a table where rows legitimately differ — a change log or events table — with ROW_NUMBER/QUALIFY. Different problem from delete-duplicates, which removes exact repeat rows. 3 min →
• Insert, update & upsert DELETE rows using a join to another table Delete rows in one table based on a match in another — the correct syntax for MySQL, PostgreSQL, SQLite, BigQuery, Snowflake and DuckDB (they differ). 3 min →
• Insert, update & upsert Delete duplicate rows, keeping one Remove duplicate rows while keeping a single copy per key — ROW_NUMBER, MIN(rowid)/ctid, and CREATE OR REPLACE across PostgreSQL, MySQL, SQLite, BigQuery, Snowflake and DuckDB. 3 min →
• Insert, update & upsert UPDATE a table from a join Update rows using values from another table — UPDATE ... FROM, UPDATE ... JOIN, and the correlated-subquery form across PostgreSQL, DuckDB, MySQL, SQLite, BigQuery and Snowflake. 3 min →
• Insert, update & upsert Upsert (insert or update) in SQL Insert a row or update it if the key already exists — ON CONFLICT, ON DUPLICATE KEY UPDATE and MERGE across PostgreSQL, SQLite, DuckDB, MySQL, BigQuery and Snowflake. 3 min →
• JSON Build JSON from rows (aggregate rows into a JSON array/object) Turn grouped rows into a JSON array of objects with json_agg / JSON_ARRAYAGG / json_group_array — the right function name for MySQL, PostgreSQL, SQLite, BigQuery, Snowflake and DuckDB. 3 min →
• JSON Expand a JSON array into rows Turn a JSON array column into one row per element — jsonb_array_elements, JSON_TABLE, json_each, UNNEST and FLATTEN across PostgreSQL, MySQL, SQLite, BigQuery, Snowflake and DuckDB. 3 min →
• JSON Read a field from a JSON column Extract a scalar (and a nested path) from a JSON/JSONB column — ->/->>, JSON_EXTRACT, JSON_VALUE and the colon syntax across PostgreSQL, MySQL, SQLite, BigQuery, Snowflake and DuckDB. 3 min →
• Strings Get the Nth part of a delimited string Pull out one field from a delimited string — split_part, SUBSTRING_INDEX, SPLIT()[OFFSET], and the SQLite workaround — across MySQL, PostgreSQL, SQLite, BigQuery, Snowflake and DuckDB. 3 min →
• Strings Case-insensitive search (ILIKE / LOWER / collation) Match text ignoring case in MySQL, PostgreSQL, SQLite, BigQuery, Snowflake and DuckDB — ILIKE, LOWER(), and collation, plus how to keep it index-friendly. 3 min →
• Strings Split a delimited string into rows Turn 'a,b,c' into three rows — string_to_array/unnest, SPLIT, SPLIT_TO_TABLE, plus the JSON_TABLE and recursive-CTE workarounds for MySQL and SQLite. 3 min →
• CTEs & recursion Generate a sequence of numbers Produce rows 1..N (for filling gaps, building calendars, or test data) with generate_series, GENERATE_ARRAY, GENERATOR, and a portable recursive CTE. 2 min →
• CTEs & recursion Walk a hierarchy with a recursive CTE Traverse a parent/child tree (org chart, category tree) with WITH RECURSIVE — the same pattern in MySQL, PostgreSQL, SQLite, DuckDB, BigQuery and Snowflake. 3 min →

You know the query. You don't remember which dialect spells it which way.

Each recipe puts the task on one page with the exact syntax per dialect, side by side, plus the traps (argument order, version requirements, NULL handling).

FAQ