6. The SQL dialect

Most databases treat their SQL dialect as an obligation. DuckDB treats it as a feature, under the banner “friendly SQL”, and the accumulated effect is larger than any individual item on the list.

The dialect is PostgreSQL-flavoured — the parser is a fork of Postgres’s — so anything you know from Postgres broadly works. What follows is what it adds.

The small kindnesses

Each of these fixes an irritation that SQL has had since 1986.

GROUP BY ALL and ORDER BY ALL

-- standard SQL: restate every non-aggregate
SELECT country, product, date_trunc('month', ts), sum(amount)
FROM sales
GROUP BY country, product, date_trunc('month', ts);

-- DuckDB
SELECT country, product, date_trunc('month', ts), sum(amount)
FROM sales
GROUP BY ALL;

GROUP BY ALL groups by every selected column that is not an aggregate. It is not a shortcut with edge cases — it is exactly the set the standard would have made you write. Editing the SELECT list no longer means editing the GROUP BY to match, which is one of the most common sources of a mechanical SQL error.

SELECT * EXCLUDE and REPLACE

SELECT * EXCLUDE (password_hash, internal_notes) FROM users;

SELECT * REPLACE (upper(country) AS country) FROM sales;

SELECT * EXCLUDE (raw) REPLACE (amount / 100 AS amount) FROM tx;

“Everything except these two” and “everything, but transform this one” are constant needs on wide tables, and until now the answer was to type out forty column names.

COLUMNS()

-- apply a function to every column matching a pattern
SELECT min(COLUMNS('.*_at')) FROM events;

-- or to all of them
SELECT count(COLUMNS(*)) FROM wide_table;

-- with a lambda selecting which
SELECT sum(COLUMNS(c -> c LIKE 'metric_%')) FROM stats;

This is the one that surprises people. COLUMNS() expands to a list of columns and the surrounding expression is applied to each, which turns “profile every column in this table” from a code-generation problem into one line.

The rest of the list

-- FROM first. Autocomplete knows the columns
-- before you type SELECT. And `FROM t` alone
-- is a valid query.
FROM sales SELECT country, sum(amount) GROUP BY ALL;
FROM sales;

-- Trailing commas. Everywhere.
SELECT a, b, c, FROM t;

-- Reference an alias later in the same SELECT
SELECT amount * 1.2 AS with_tax,
       with_tax - amount AS tax
FROM sales;

-- UNION BY NAME: match on column name, not position
SELECT a, b FROM t1 UNION ALL BY NAME SELECT b, a FROM t2;

-- Sane integer division and no silent overflow:
SELECT 5 / 2;    -- 2.5, not 2
SELECT 5 // 2;   -- 2, when you meant integer division

-- Case-insensitive identifiers, but case-PRESERVING.
-- Postgres lowercases; DuckDB keeps what you wrote.

-- String slicing, 1-indexed
SELECT 'duckdb'[1:4];   -- 'duck'

-- Chained comparisons
SELECT * FROM t WHERE 1 <= x <= 10;

None of these is important on its own. Together they remove enough friction that writing DuckDB SQL feels different from writing SQL, and that is a real and underrated reason people move analysis into it.

Nested types — the part that is not cosmetic

DuckDB has first-class LIST, STRUCT, MAP and UNION. This is what lets it swallow JSON and Parquet’s nested columns without flattening them first.

SELECT
  [1, 2, 3]                                AS a_list,
  {'name': 'ana', 'age': 33}               AS a_struct,
  MAP {'a': 1, 'b': 2}                     AS a_map;

   LIST      an ordered, variable-length array
             of ONE type.        INTEGER[]

   STRUCT    named fields, each with its own
             fixed type.  STRUCT(name VARCHAR,
                                 age INTEGER)

   MAP       arbitrary keys to values, all keys
             one type, all values one type.
                          MAP(VARCHAR, INTEGER)

   UNION     one of several types, tagged.

STRUCT versus MAP is the distinction that matters. A struct’s field names are part of the type — known at plan time, so s.name is a direct offset with no lookup. A map’s keys are data — arbitrary and per-row. Use a struct whenever the set of keys is fixed, which is nearly always; it is dramatically faster and it gives you column pruning.

-- struct access, and it prunes: reading only
-- payload.user.id touches only that sub-column
SELECT payload.user.id, payload.items[1].sku
FROM events;

-- unnest a list into rows
SELECT id, unnest(tags) AS tag FROM articles;

-- recursively expand a struct into columns
SELECT unnest(payload, recursive := true) FROM events;

-- build them back up
SELECT country, list(DISTINCT product) AS products
FROM sales GROUP BY 1;

Lambda functions work over lists, and they are the piece that makes nested data genuinely workable rather than merely storable:

SELECT
  list_transform(prices, p -> p * 1.2)      AS with_tax,
  list_filter(tags, t -> t LIKE 'urgent%')  AS urgent,
  list_reduce(amounts, (a, b) -> a + b)     AS total,
  list_sort(scores)                         AS sorted
FROM orders;

The joins that are not standard

ASOF JOIN

The one that is genuinely hard to write otherwise.

   PROBLEM: for each trade, find the price
   that was in effect AT THAT MOMENT.

   trades              prices
   09:00:01  AAPL      08:59:58  AAPL  180.10
   09:00:04  AAPL      09:00:03  AAPL  180.15
                       09:00:07  AAPL  180.22

   trade at 09:00:01 → price 180.10
   trade at 09:00:04 → price 180.15

SELECT t.ts, t.symbol, t.qty, p.price
FROM trades t
ASOF JOIN prices p
  ON t.symbol = p.symbol AND t.ts >= p.ts;

ASOF JOIN matches each left row to the single closest right row satisfying the inequality. The standard-SQL version is a correlated subquery or a window function over a union, it is slow, and it is easy to get subtly wrong. This is a specialised operator that does it in one pass over sorted input.

It is not only for finance. Any “what was the state of X when Y happened” question is an as-of join: which experiment variant was active, which price tier applied, which config version was deployed.

POSITIONAL JOIN and LATERAL

-- join two tables row 1 to row 1, row 2 to row 2.
-- For data that arrived as parallel columns.
SELECT * FROM a POSITIONAL JOIN b;

-- LATERAL: the right side can reference the left
SELECT u.id, top.product
FROM users u,
LATERAL (SELECT product FROM orders o
         WHERE o.user_id = u.id
         ORDER BY amount DESC LIMIT 3) top;

Windows, QUALIFY, PIVOT

-- QUALIFY filters on a window function.
-- Without it you need a subquery.
SELECT country, product, revenue
FROM sales
QUALIFY row_number() OVER (PARTITION BY country
                           ORDER BY revenue DESC) <= 3;

QUALIFY is to window functions what HAVING is to aggregates, and its absence from standard SQL is why “top N per group” queries have an extra layer of nesting for no reason.

-- PIVOT without writing a CASE per value
PIVOT sales ON month USING sum(amount) GROUP BY country;

-- and back
UNPIVOT monthly ON jan, feb, mar INTO NAME month VALUE amount;

PIVOT in DuckDB works out the distinct values itself, which no other SQL dialect does without dynamic SQL.

Sampling, and the functions worth knowing

SELECT * FROM huge USING SAMPLE 1%;
SELECT * FROM huge USING SAMPLE 10000 ROWS;

-- a whole-table profile in one statement
SUMMARIZE sales;
SUMMARIZE SELECT * FROM 'events.parquet';

SUMMARIZE gives you per-column type, min, max, approximate distinct count, average, standard deviation, quartiles and null percentage. On an unfamiliar Parquet file it is the first thing to run, and it replaces about twenty lines of exploratory code.

-- approximate aggregates, when exact is not needed
SELECT approx_count_distinct(user_id) FROM events;   -- HyperLogLog
SELECT approx_quantile(latency, 0.99) FROM requests; -- T-Digest

-- ordered aggregates
SELECT arg_max(product, revenue) FROM sales;  -- the product with
                                              -- the highest revenue
SELECT mode(country), median(amount) FROM sales;

-- lists and strings
SELECT string_agg(name, ', ' ORDER BY name) FROM users;
SELECT regexp_extract(url, 'utm_source=([^&]+)', 1) FROM visits;

Where the dialect surprises Postgres users

   ─ NO stored procedures, NO PL/pgSQL,
     no triggers. There is no procedural
     language at all. Macros (below) are the
     substitute and they are expressions,
     not programs.

   ─ Identifiers are CASE-INSENSITIVE for
     matching but PRESERVED as written.
     Postgres folds to lower case.

   ─ NULL ordering defaults differ; use
     NULLS FIRST / NULLS LAST explicitly if
     it matters.

   ─ Fewer index types — one, an ART
     (chapter 3). No GIN, GiST, BRIN.

   ─ No table inheritance, no domains,
     no custom operators.

   ─ Integer division: `/` returns a DOUBLE.
     `//` is the integer one.

Macros, instead of functions

-- a scalar macro
CREATE MACRO tax(amount, rate := 0.2) AS amount * (1 + rate);
SELECT tax(100), tax(100, rate := 0.05);

-- a table macro — a parameterised view
CREATE MACRO recent_sales(days) AS TABLE
  SELECT * FROM sales WHERE ts >= now() - to_days(days);

SELECT country, sum(amount) FROM recent_sales(30) GROUP BY ALL;

A macro is textual substitution into the query plan, not a function call, so it is fully optimised and pushed down like any other expression. Table macros are the closest DuckDB gets to a parameterised view, and they compose well with dbt’s own macros — though the two are doing the same job at different layers, and using both for the same thing gets confusing quickly.

What to take from this chapter

• The dialect is PostgreSQL-flavoured — the parser is a Postgres fork — plus a deliberate set of additions branded “friendly SQL”.
• GROUP BY ALL removes the most common mechanical SQL error. SELECT * EXCLUDE / REPLACE and COLUMNS() turn wide-table work from code generation into one line.
• Also: FROM-first syntax, trailing commas, aliases reusable in the same SELECT, UNION BY NAME, string slicing, chained comparisons. Individually trivial; collectively the reason people move analysis here.
• Nested types are first class — LIST, STRUCT, MAP, UNION. Use STRUCT when the keys are fixed: field names are part of the type, so access is an offset and sub-columns prune.
• Lambdas over lists (list_transform, list_filter, list_reduce) are what make nested data workable rather than merely storable.
• ASOF JOIN answers “what was the state of X when Y happened” in one pass — the standard-SQL version is a slow correlated subquery that is easy to get wrong.
• QUALIFY is HAVING for window functions; PIVOT discovers its own distinct values.
• SUMMARIZE is the first thing to run on an unfamiliar file — types, ranges, distinct counts, quartiles, null percentages in one statement.
• Postgres users should expect no procedural language, no triggers, one index type, case-preserving identifiers, and / returning a DOUBLE.
• Macros are textual substitution into the plan, so they optimise like any expression. Table macros are parameterised views.

Next: a query traced end to end — parser, binder, optimiser, physical plan, pipelines, morsels and result.