DuckDB SQL Basics

DuckDB’s SQL dialect is closely aligned with PostgreSQL, so if you know standard SQL, almost everything below will already feel familiar — with a handful of DuckDB-specific extras layered on top.

Data Types

Alongside the usual scalar types, DuckDB has first-class nested types, which is unusual for a SQL engine and extremely useful for analytics:

CREATE TABLE products (
    id          INTEGER,
    name        VARCHAR,
    price       DECIMAL(10, 2),
    in_stock    BOOLEAN,
    tags        VARCHAR[],                       -- LIST: a Python-style array
    metadata    STRUCT(weight_kg DOUBLE, sku VARCHAR),  -- STRUCT: nested fields
    attributes  MAP(VARCHAR, VARCHAR)             -- MAP: key/value pairs
);

INSERT INTO products VALUES (
    1, 'Widget', 9.99, true,
    ['new', 'sale'],
    {'weight_kg': 0.4, 'sku': 'WID-001'},
    MAP {'color': 'blue', 'material': 'steel'}
);

SELECT name, tags[1] AS first_tag, metadata.sku
FROM products;

Other notable types: TIMESTAMP, DATE, INTERVAL, UUID, BLOB, HUGEINT (128-bit integers), and ENUM.

Creating Tables & Inspecting Data

CREATE TABLE orders (order_id INTEGER, customer VARCHAR, amount DECIMAL, order_date DATE);

DESCRIBE orders;      -- column names, types, nullability
SUMMARIZE orders;     -- min/max/avg/null-count/approx unique per column, instantly

SUMMARIZE is a DuckDB-specific shortcut that replaces a dozen manual SELECT MIN(x), MAX(x), ... statements when you’re getting to know a new dataset.

Everyday Queries

SELECT customer, SUM(amount) AS total_spent
FROM orders
WHERE order_date >= '2026-01-01'
GROUP BY customer
HAVING SUM(amount) > 100
ORDER BY total_spent DESC
LIMIT 10;

DuckDB also supports the shorthand GROUP BY ALL and ORDER BY ALL, which infer the grouping/ordering columns from the SELECT list — handy for quick exploration:

SELECT customer, COUNT(*) AS orders, SUM(amount) AS total
FROM orders
GROUP BY ALL
ORDER BY ALL DESC;

Joins

Standard INNER, LEFT, RIGHT, FULL OUTER, and CROSS joins all work exactly as in Postgres:

SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id;

DuckDB also supports USING for equality joins on identically-named columns, saving some typing:

SELECT * FROM orders JOIN customers USING (customer_id);

The next guide covers the SQL features that are distinctly DuckDB’s own — window functions, QUALIFY, PIVOT, and native ASOF joins.

Written by NPBlue Data Team — Data Platform Engineers who uses DuckDB daily for local analytics, feature engineering, and data pipeline development.

Reviewed for technical accuracy. Spot an error? Let us know.