One task · six dialects · copy the one you need

The same SQL, in every dialect.

Running totals, top-N per group, removing duplicates with ROW_NUMBER, pivot and unpivot, upsert, reading JSON, recursive CTEs — each shown in MySQL, PostgreSQL, SQLite, BigQuery, Snowflake and DuckDB, with the gotchas that bite when you move 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 →

Why this exists

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

Every engine does the common things differently — upsert is ON CONFLICT in Postgres, ON DUPLICATE KEY UPDATE in MySQL, and MERGE in BigQuery and Snowflake; filtering a window function needs QUALIFY in some and a subquery in others. So you end up googling the same five patterns over and over. sqlindex.pages.dev puts each task on one page with the exact syntax per dialect, side by side, plus the traps (argument order, version requirements, NULL handling).

How it works

Find the task, copy your dialect's version

Pick a recipe. Browse by topic in the full recipe list.
Copy the block for your database (one-click copy on every snippet).
Swap in your table and column names — the examples use plain placeholders.

FAQ

Frequently asked questions

Are these SQL recipes free?

Yes. Every recipe on sqlindex.pages.dev is free to read and copy, with no account, paywall, or sign-up. Some outbound links (for example to managed databases, data warehouses, or courses) may be affiliate links, which never change the price you pay.

Which databases are covered?

Each recipe shows the syntax for MySQL (8+), PostgreSQL, SQLite (3.25+), BigQuery, Snowflake, and DuckDB. Where a feature needs a specific version (window functions, QUALIFY, JSON_TABLE) the recipe says so.

Why does the same query differ between databases?

The SQL standard leaves a lot implementation-defined, and each engine grew its own functions — so aggregating strings is STRING_AGG in Postgres/BigQuery/DuckDB, GROUP_CONCAT in MySQL/SQLite, and LISTAGG in Snowflake. These recipes hand you the right one for your engine instead of making you translate.

What's QUALIFY, and why isn't it in every example?

QUALIFY filters the result of a window function inline (e.g. keep ROW_NUMBER() = 1), and it exists in BigQuery, Snowflake, and DuckDB. MySQL, PostgreSQL, and SQLite do not have it, so those versions wrap the window function in a subquery or CTE and filter outside. The recipes show both forms.

Is this the same as datesql?

They are companions with no overlap. datesql.pages.dev covers date and time across dialects (DATEDIFF, DATE_TRUNC, formatting, time zones). sqlindex covers everything else: window functions, grouping and pivots, upsert/MERGE, JSON, strings, and recursive CTEs.

Will the snippets run as-is?

They are minimal, working statements written with placeholder table and column names (orders, amount, customer_id…) that you swap for yours. Window functions need MySQL 8+, SQLite 3.25+, or any modern warehouse — version-sensitive parts are flagged in each recipe.