Reference
Look it up, fast.
Deeper guides, scannable cheatsheets, and fixes for the mistakes everyone hits. All in one place.
Querying
SELECT syntax The shape of a query and its logical processing order. Order of execution The order a query is processed in, and what each clause sees. WHERE operators Comparisons, AND/OR, IN, BETWEEN, and LIKE. JOIN types INNER, LEFT, RIGHT, FULL, and CROSS joins. Aggregate functions COUNT, SUM, AVG, MIN, MAX, GROUP BY, and HAVING. Statistical aggregates Median, percentiles, standard deviation, mode, and histograms. Subqueries and EXISTS Nested SELECTs, IN subqueries, EXISTS, and correlation. Common table expressions The WITH clause, chained steps, and CTE vs subquery. Set operations UNION ALL, UNION, INTERSECT, and EXCEPT. Window functions OVER, PARTITION BY, ranking, offsets, and running totals. Deduplication Find duplicates, keep one row per key, and delete copies.
DuckDB shortcuts
Tables & objects
CREATE TABLE Declare columns, types, defaults, and constraints. Views Save a query under a name and reuse it like a table. CREATE TABLE AS SELECT Store a query result as a table you can query again. Materialized views Stored query results, how engines refresh them, and DuckDB's snapshots. Temporary tables Scratch tables and views that clean up after themselves. Schemas Group objects into namespaces like staging and reporting. SHOW TABLES and DESCRIBE List tables, columns, and types before you query. Table constraints NOT NULL, DEFAULT, CHECK, UNIQUE, and keys. Indexes CREATE INDEX for uniqueness, and when an index is used at all. Upserts ON CONFLICT, INSERT OR REPLACE, and MERGE. Generated columns Columns the table computes from other columns in the row. CREATE TYPE and enums Enums that accept a fixed list of values, plus aliases and structs. Sequences Generate ID values with nextval and column defaults. Macros and functions Name a reusable expression or a parameterized query. Stored procedures Named programs run with CALL, and what replaces them in DuckDB. Triggers Code that runs on INSERT, UPDATE, or DELETE, and its alternatives. ALTER TABLE Add, rename, retype, and drop columns on a live table. DROP statements Remove tables, views, schemas, and more without breakage.
Functions & expressions
Date functions Today's date, parts, math, and truncation. Date buckets & spines Group by week or month, and fill empty periods with zero. String functions Length, case, trimming, slicing, and concatenation. Regular expressions Match, extract, and replace text by its shape. Null handling IS NULL, COALESCE, NULLIF, and null-safe logic. CASE expressions Searched and simple CASE for conditional values. CAST and type conversion CAST, ::, TRY_CAST, and the rounding rules they follow.
Nested data & JSON
Query basics
SELECT, WHERE, ORDER BY, and LIMIT at a glance. Joins
INNER, LEFT, RIGHT, FULL, and CROSS, with what each one keeps. Aggregates & grouping
COUNT, SUM, AVG, GROUP BY, and HAVING. Distributions
Median, percentiles, spread, mode, and histogram recipes. Date & string functions
The everyday date and text functions, with dialect notes. Date ranges & buckets
Half-open ranges, week and month buckets, and date spines. Window functions
OVER, PARTITION BY, ranking, LAG/LEAD, and running totals. Subqueries & CTEs
Scalar, IN, and EXISTS subqueries plus the WITH clause. Set operations
UNION, UNION ALL, INTERSECT, and EXCEPT side by side. CASE expressions
Searched and simple CASE, plus conditional aggregation. NULL handling
IS NULL, COALESCE, NULLIF, and null-safe equality. Deduplication
Find duplicates, keep one row per key, and delete copies. Why = NULL never matches Comparisons to NULL return unknown, so the row is dropped. Use IS NULL. COUNT or SUM is too high after a JOIN A one-to-many join repeats rows, inflating totals. Count what you mean. Why a JOIN without ON multiplies rows A missing join condition pairs every row with every row. Add the key match. Why a many-to-many JOIN inflates totals When both sides have repeats, the join multiplies combinations. Pre-aggregate first. Why joining on names instead of ids is risky Names and labels can repeat or change. Join on stable ids whenever the schema gives you one. Why my query returns no rows Often a filter or join does not match the real data: check case, ranges, NULLs, and boolean logic. Why my percentage comes out 0 Some SQL engines truncate integer division. Use a decimal operand when you need a fractional result. Why a window running total jumps on ties The default RANGE frame can group tied ORDER BY values. Use ROWS for row-by-row totals. Why NOT IN with a NULL returns nothing A single NULL in the list means no NOT IN test can be true. Use NOT EXISTS. LIMIT without ORDER BY gives unpredictable rows Without ORDER BY, SQL does not guarantee which rows come first. Sort when the selected rows or their order matter. Why a date range drops the last day Comparing a timestamp to a bare date cuts off that day. Use a half-open range. Why a LEFT JOIN turns into an INNER JOIN A null-rejecting WHERE filter on the right table removes unmatched LEFT JOIN rows. Why an INNER JOIN drops rows with NULL keys A NULL key matches nothing, so the row leaves the join. Use a LEFT JOIN. Why INNER JOIN filters belong in the right place Relationship predicates belong in ON; row filters belong in WHERE. Mixing them hides bugs. Why COUNT(column) skips NULL values COUNT(*) counts rows, but COUNT(column) counts only non-NULL values. Why averaging averages gives the wrong result An average of group averages ignores group size. Recompute from totals. Why OR without parentheses changes your filter AND binds more tightly than OR. Add parentheses so the filter matches your intent. Why UNION ALL keeps duplicate rows UNION ALL stacks results exactly as written. Use UNION only when duplicates should collapse. Why numbers stored as text sort wrong Text sorts character by character, so '9' lands above '75'. Cast to a number before sorting or comparing. Why money totals show long decimals A DOUBLE column stores cents approximately, so a SUM drifts off by a hair. Cast to DECIMAL before you add. Why rank() = 1 returns more than one row Tied rows share rank 1, so a top-1-per-group query can return two winners. Break the tie on purpose. Why MAX(id) isn't the latest row Ids record when a row was inserted, not when the thing happened. Order by the date to find the latest. Why percent change from zero breaks Dividing by a zero prior period gives Infinity or NaN in DuckDB. Guard with NULLIF and label the new rows. Why rounded percentages don't add up to 100 Each share rounds on its own, so the column can total 99 or 101. Give the missing points to the largest remainders. Why deduplicating without ORDER BY keeps the wrong row ROW_NUMBER() with no ORDER BY keeps whichever row the engine reads first. Say which row should win. Why COUNT(*) miscounts funnel stages A session that views a page twice counts twice. Count distinct sessions or users at every stage. Why LAST_VALUE returns the current row The default window frame stops at the current row, so LAST_VALUE sees nothing after it. Extend the frame, or use FIRST_VALUE. Why GROUP BY splits one value into two groups Stray spaces and capitals make 'Perth' and ' PERTH ' different values. Group by a cleaned key, or clean the column. Why LIKE '%_%' matches every row In LIKE, _ matches any one character and % any run. Escape them to match the characters themselves. Why COALESCE(x, 0) drags an average down Replacing NULL with 0 before avg counts missing values as real zeros. Let avg skip them, and count them separately. Why regexp_replace only replaces the first match DuckDB and PostgreSQL replace the first match unless you pass 'g'. MySQL, Oracle, and Snowflake replace them all. Why DISTINCT ON returns the wrong row DISTINCT ON keeps the first row of each group in ORDER BY order. Sort by the key alone and the row kept is whichever comes first. Why UNION drops rows from a total UNION removes duplicate rows, so equal amounts from different records collapse into one. Use UNION ALL to stack results. Why sorting by a rounded value changes the top N An alias that reuses the column name makes ORDER BY sort the rounded value, and its ties change who makes the cut. Why dates stored as text filter wrong Text compares character by character, so '11/03/2024' comes after '11/01/2025'. Parse it into a date before comparing. Why trailing spaces make a join match nothing A key padded with trailing spaces never equals the clean one, so an inner join silently drops the rows. Join on trim(), or clean the column.