DuckDB offers several advanced SQL features as well syntactic sugar to make SQL queries more concise. We call these colloquially as “friendly SQL”.

Several of these features are also supported in other systems while some are (currently) exclusive to DuckDB.

Clauses

• Creating tables and inserting data:
  • CREATE OR REPLACE TABLE: this clause allows avoiding DROP TABLE IF EXISTS statements in scripts.
  • CREATE TABLE ... AS SELECT (CTAS): this clause allows creating a new table from the output of a table without manually defining a schema.
  • INSERT INTO ... BY NAME: this variant of the INSERT statement allows using column names instead of positions.
• Describing tables and computing statistics:
  • DESCRIBE: this clause provides a succinct summary of the schema of a table or query.
  • SUMMARIZE: this clause returns summary statistics for a table or query.
• Making SQL clauses more compact:
  • FROM-first syntax with an optional SELECT clause: DuckDB allows queries in the form of FROM tbl which selects all columns (performing a SELECT * statement).
  • GROUP BY ALL: this clause allows omitting the group-by columns by inferring them from the list of attributes in the SELECT clause.
  • ORDER BY ALL: this clause allows ordering on all columns (e.g., to ensure deterministic results).
  • SELECT * EXCLUDE: the EXCLUDE option allows excluding specific columns from the * expression.
  • SELECT * REPLACE: the REPLACE option allows replacing specific columns with different expressions in a * expression.
  • UNION BY NAME: this clause performing the UNION operation along the names of columns (instead of relying on positions).
• Transforming tables:
  • PIVOT to turn long tables to wide tables.
  • UNPIVOT to turn wide tables to long tables.
• Defining SQL-level variables:
  • SET VARIABLE
  • RESET VARIABLE

Query Features

• Column aliases in WHERE, GROUP BY, and HAVING
• COLUMNS() expression can be used to execute the same expression on multiple columns:
  • with regular expressions
  • with EXCLUDE and REPLACE
  • with lambda functions
• Reusable column aliases, e.g.: SELECT i + 1 AS j, j + 2 AS k FROM range(0, 3) t(i)
• Advanced aggregation features for analytical (OLAP) queries:
  • FILTER clause
  • GROUPING SETS, GROUP BY CUBE, GROUP BY ROLLUP clauses
• count() shorthand for count(*)

Literals and Identifiers

• Case-insensitivity while maintaining case of entities in the catalog
• Deduplicating identifiers
• Underscores as digit separators in numeric literals

Data Types

• MAP data type
• UNION data type

Data Import

• Auto-detecting the headers and schema of CSV files
• Directly querying CSV files and Parquet files
• Loading from files using the syntax FROM 'my.csv', FROM 'my.csv.gz', FROM 'my.parquet', etc.
• Filename expansion (globbing), e.g.: FROM 'my-data/part-*.parquet'

Functions and Expressions

• Dot operator for function chaining: SELECT ('hello').upper()
• String formatters: the format() function with the fmt syntax and the printf() function
• List comprehensions
• List slicing
• String slicing
• STRUCT.* notation
• Simple LIST and STRUCT creation

Join Types

• ASOF joins
• LATERAL joins
• POSITIONAL joins

Trailing Commas

DuckDB allows trailing commas, both when listing entities (e.g., column and table names) and when constructing LIST items. For example, the following query works:

SELECT
    42 AS x,
    ['a', 'b', 'c',] AS y,
    'hello world' AS z,
;

Related Blog Posts

• “Friendlier SQL with DuckDB” blog post
• “Even Friendlier SQL with DuckDB” blog post
• “SQL Gymnastics: Bending SQL into Flexible New Shapes” blog post