Skip to content
Tutorials Center
My learning

Topics

Lesson 9 of 38 ยท SQL Dialect and Types

Friendly SQL

Learn DuckDB's friendly SQL extensions: FROM-first queries, SELECT * EXCLUDE and REPLACE, COLUMNS, GROUP BY ALL, ORDER BY ALL, QUALIFY, alias reuse and trailing commas.

DuckDB speaks standard SQL, closely following PostgreSQL's dialect. On top of it, DuckDB adds a long list of small conveniences, collectively called Friendly SQL. None are essential, but together they remove the boilerplate that makes analytical SQL tedious: repeating a long column list in GROUP BY, spelling out 40 columns to drop one, nesting a subquery to filter on a window function.

Friendly SQL syntax only runs in DuckDB. Each feature below is shown in a read-only DuckDB block, followed by a runnable stand-in that does the same job in portable SQL, so you can see exactly what the shortcut replaces.

FROM-first queries

You may start a query with FROM, and the SELECT is optional (it defaults to *):

SQL
-- DuckDB (read-only example)
FROM products;                              -- same as SELECT * FROM products
FROM products SELECT name, price WHERE price > 50;
FROM products WHERE stock = 0 SELECT count(*);

This follows the way you think ("take this table, then pick things") and makes for a quick way to peek at data, since you can type FROM tbl; with nothing else. It also makes completion in editors easier, because the table is known before you choose columns.

GROUP BY ALL and ORDER BY ALL

GROUP BY ALL groups by every column in the SELECT list that is not an aggregate. ORDER BY ALL sorts by all the columns, left to right:

SQL
-- DuckDB (read-only example)
SELECT country, city, count(*) AS customers
FROM customers
GROUP BY ALL
ORDER BY ALL;

The portable version repeats the columns by name (or by position):

SQL Stand-in: the long form of GROUP BY ALL ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

You also get a shorter ORDER BY ALL DESC, and ORDER BY ALL NULLS LAST. Be careful: GROUP BY ALL silently changes if you edit the SELECT list, so use it in exploration and keep explicit groups in code that must stay stable.

SELECT * EXCLUDE, REPLACE and RENAME

Often you want "everything but one column", or everything with one column transformed:

SQL
-- DuckDB (read-only example)
SELECT * EXCLUDE (added_on, stock) FROM products;
SELECT * REPLACE (round(price * 1.2, 2) AS price) FROM products;
SELECT * RENAME (name AS product_name) FROM products;
SELECT * EXCLUDE (id) REPLACE (upper(name) AS name) FROM products;

In a table with 60 columns, this saves 59 names. The stand-in lists the columns by hand:

SQL Stand-in: everything except two columns ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Pick columns by pattern: COLUMNS()

COLUMNS('regex') expands to all columns whose names match, and applies an expression to each:

SQL
-- DuckDB (read-only example)
SELECT COLUMNS('.*_at') FROM events;               -- all columns ending in _at
SELECT min(COLUMNS(*)), max(COLUMNS(*)) FROM products;
SELECT COLUMNS(c -> c LIKE 'unit%') FROM order_items;   -- with a lambda
SELECT max(COLUMNS('^(price|stock)$')) FROM products;

It turns a repetitive statement into one line. It can also appear in WHERE, as in WHERE COLUMNS(*) IS NOT NULL to drop rows with any NULL.

QUALIFY: filter on window functions

SQL runs WHERE before window functions, so you cannot filter on a window result directly. The standard fix is a subquery. DuckDB adds QUALIFY, which runs after the window functions:

SQL
-- DuckDB (read-only example)
SELECT name, category_id, price
FROM products
QUALIFY row_number() OVER (PARTITION BY category_id ORDER BY price DESC) <= 2;

The runnable stand-in does the same with a subquery:

SQL Stand-in: the two dearest products per category ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Reusing aliases

In most databases you cannot use a SELECT alias elsewhere in the same SELECT. In DuckDB you can, and in WHERE, GROUP BY and HAVING too:

SQL
-- DuckDB (read-only example)
SELECT price * 1.2 AS with_tax,
       with_tax - price AS tax_amount
FROM products
WHERE with_tax > 100;

Without that, the stand-in must repeat the expression or nest a query:

SQL Stand-in: reusing an expression through a subquery ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

More small conveniences

Feature Example (DuckDB)
Trailing commas SELECT a, b, FROM t and in CREATE TABLE column lists
Column list in INSERT by name INSERT INTO t BY NAME SELECT ...
UNION BY NAME combines queries by column names, not positions
CREATE OR REPLACE CREATE OR REPLACE TABLE t AS ...
Prefix aliases SELECT total: sum(x) FROM t
Function chaining 'duck'.upper().reverse()
LIMIT as a percent FROM t LIMIT 10%
SELECT DISTINCT ON one row per key, as in PostgreSQL
String and list slicing 'duckdb'[1:4], [1,2,3][2:]
Positional join FROM a POSITIONAL JOIN b matches by row position
ASOF JOIN nearest earlier match in time series
SUMMARIZE, DESCRIBE profile a table or query

A couple of these deserve a short example:

SQL
-- DuckDB (read-only example)
-- one row per customer: their latest order
SELECT DISTINCT ON (customer_id) customer_id, id, ordered_at
FROM orders
ORDER BY customer_id, ordered_at DESC;

-- stack two tables whose columns are in different orders
SELECT * FROM jan_sales UNION ALL BY NAME SELECT * FROM feb_sales;

UNION ALL BY NAME is a lifesaver when files from different months have the same columns in different orders, or when a new column appears halfway through the year. Missing columns are filled with NULL.

Portability

Standard SQL still works everywhere. Use Friendly SQL where it saves effort, but remember that queries using it will not run on PostgreSQL or SQLite as written. In shared dbt models or documentation, decide as a team whether DuckDB-only syntax is acceptable.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does GROUP BY ALL do?

    GROUP BY ALL looks at the SELECT list and groups by everything that is not an aggregate.
  2. 2. Which query returns all columns of products except stock?

    EXCLUDE removes the named columns from the star expansion.
  3. 3. What problem does QUALIFY solve?

    QUALIFY is evaluated after window functions, like HAVING is after aggregates.
  4. 4. What is a drawback of GROUP BY ALL in long-lived code?

    The grouping follows the SELECT list, so a careless edit can change the meaning of the query.
  5. 5. When is UNION ALL BY NAME useful?

    It matches columns by name instead of position, filling missing ones with NULL.

Summary

  • FROM tbl; works as a whole query; FROM can come first.
  • GROUP BY ALL and ORDER BY ALL remove repeated column lists.
  • EXCLUDE, REPLACE, RENAME and COLUMNS() work on many columns at once.
  • QUALIFY filters on window functions; aliases can be reused in the same query.
  • UNION BY NAME, DISTINCT ON, slicing and chaining save more typing, at the cost of portability.

Next: Data types and casting