SQL syntax reference

Complete SELECT syntax

All SELECT clauses combine in the following order:

[WITH name AS (SELECT ...)]
SELECT [DISTINCT] expr_list
FROM table_or_subquery
[WHERE filter_condition]
[GROUP BY expr_list [HAVING condition]]
[ORDER BY expr_list [ASC | DESC] [NULLS FIRST | NULLS LAST]]
[LIMIT [offset,] count | LIMIT count OFFSET offset]

Projection

Syntax

SELECT expr0, expr1, ...

The SELECT clause projects columns or expressions from the source data. Projection expressions support column references, transformation functions, DISTINCT deduplication, and arithmetic. Use AS to alias projected columns.

Examples

-- Select all columns
SELECT * FROM nation;

-- Select a specific column
SELECT n_name FROM nation;

-- Apply a function
SELECT lower(n_name) FROM nation;

-- Deduplicate values
SELECT DISTINCT n_regionkey FROM nation;

-- Arithmetic expression
SELECT n_nationkey + 1 FROM nation;

-- Column alias
SELECT n_nationkey + 1 AS colname FROM nation;

Filter

Syntax

... WHERE expr0 [AND expr1] [OR expr2] ...

The WHERE clause filters rows by evaluating Boolean expressions. Supports AND, OR, LIKE fuzzy matching, and regular expression matching with regexp.

Examples

-- Equality filter
SELECT n_name FROM nation WHERE n_regionkey = 2;

-- IN list
SELECT n_nationkey FROM nation WHERE n_name IN ('CHINA', 'VIETNAM');

-- Range filter
SELECT n_name FROM nation WHERE n_nationkey BETWEEN 10 AND 20;

-- LIKE fuzzy match
SELECT n_name FROM nation WHERE n_name LIKE 'C%';

-- Regular expression match
SELECT n_name FROM nation WHERE n_name regexp '^(C|U).*';

-- Subquery filter
SELECT n_name FROM nation WHERE n_regionkey IN (SELECT r_regionkey FROM region WHERE r_name = 'ASIA');

Sort

Syntax

... ORDER BY expr0, expr1 [ASC | DESC] [NULLS FIRST | NULLS LAST]

ORDER BY sorts the result set by one or more columns. The default direction is ascending. Use NULLS FIRST or NULLS LAST to control where NULL values appear.

Examples

-- Default ascending sort
SELECT * FROM nation ORDER BY n_name;

-- Descending sort
SELECT * FROM nation ORDER BY n_name DESC;

-- NULLs first
SELECT * FROM nation ORDER BY n_name NULLS FIRST;

-- NULLs last
SELECT * FROM nation ORDER BY n_name NULLS LAST;

Pagination

Syntax

... LIMIT [offset,] count
-- or equivalently:
... LIMIT count OFFSET offset

LIMIT restricts the number of output rows. Combine with ORDER BY to get deterministic results.

Examples

-- First 5 rows
SELECT * FROM nation LIMIT 5;

-- 10 rows starting at offset 5
SELECT * FROM nation LIMIT 5, 10;

-- Ordered pagination: 5 rows starting at offset 3
SELECT * FROM nation ORDER BY n_name LIMIT 3, 5;

-- Equivalent using OFFSET keyword
SELECT * FROM nation ORDER BY n_name LIMIT 5 OFFSET 3;

Aggregation

Syntax

SELECT AGG_FUNC(expr), ... [GROUP BY expr_list] [HAVING condition]

GROUP BY groups rows by one or more columns and computes aggregates per group. Use HAVING to filter groups after aggregation.

Examples

-- Full-table count
SELECT count(*) FROM nation;

-- Maximum value
SELECT max(o_totalprice) FROM orders;

-- Count per group
SELECT c_nationkey, count(*) FROM customer GROUP BY c_nationkey;

-- Filter groups with HAVING
SELECT c_nationkey, count(*) AS user_count FROM customer GROUP BY c_nationkey HAVING user_count > 6000;

Common table expressions

Syntax

WITH name1 AS (SELECT ...) [, name2 AS (SELECT ...)]
SELECT ...

A Common Table Expression (CTE) defines a named temporary result set scoped to a single SQL statement. CTEs can be referenced multiple times in the main query and improve readability for complex queries.

Example

WITH china_users AS (
  SELECT c_custkey FROM customer WHERE c_nationkey = 18
),
canada_users AS (
  SELECT c_custkey FROM customer WHERE c_nationkey = 3
)
SELECT * FROM china_users
UNION ALL
SELECT * FROM canada_users;

Set operators

query1 UNION [ALL | DISTINCT] query2

-- UNION (removes duplicates, same as UNION DISTINCT)
SELECT n_regionkey FROM nation UNION SELECT n_regionkey FROM nation;

-- Explicit DISTINCT
SELECT n_regionkey FROM nation UNION DISTINCT SELECT n_regionkey FROM nation;

-- Keep duplicates
SELECT n_regionkey FROM nation UNION ALL SELECT n_regionkey FROM nation;

query1 INTERSECT query2

SELECT n_name FROM nation INTERSECT SELECT n_name FROM nation;

query1 EXCEPT query2
-- or equivalently:
query1 MINUS query2

SELECT n_name FROM nation EXCEPT SELECT n_name FROM nation;

SELECT n_name FROM nation MINUS SELECT n_name FROM nation;

Join

Syntax

... FROM table1 [LEFT | RIGHT | FULL] [OUTER | SEMI | ANTI] JOIN table2 [ON condition]

JOIN merges rows from two tables according to a join condition. Supported join types:

Examples

-- Implicit CROSS JOIN (comma syntax)
SELECT a.n_name, b.n_name FROM nation a, nation b;

-- Explicit JOIN (defaults to INNER JOIN)
SELECT a.n_name, b.n_name FROM nation a JOIN nation b;

-- CROSS JOIN
SELECT a.n_name, b.n_name FROM nation a CROSS JOIN nation b;

-- INNER JOIN with ON condition
SELECT r_name, n_name FROM nation JOIN region ON nation.n_regionkey = region.r_regionkey;

-- INNER JOIN using WHERE (equivalent)
SELECT r_name, n_name FROM nation, region WHERE r_regionkey = n_regionkey;

-- LEFT OUTER JOIN
SELECT r_name, n_name FROM nation LEFT OUTER JOIN region ON n_regionkey = r_regionkey;

-- RIGHT OUTER JOIN
SELECT r_name, n_name FROM nation RIGHT OUTER JOIN region ON n_regionkey = r_regionkey;

-- FULL OUTER JOIN
SELECT r_name, n_name FROM nation FULL OUTER JOIN region ON n_regionkey = r_regionkey;

-- LEFT SEMI JOIN
SELECT * FROM nation LEFT SEMI JOIN region ON n_regionkey = r_regionkey;

-- RIGHT SEMI JOIN
SELECT * FROM nation RIGHT SEMI JOIN region ON n_regionkey = r_regionkey;

-- LEFT ANTI JOIN
SELECT * FROM nation LEFT ANTI JOIN region ON n_regionkey = r_regionkey;

-- RIGHT ANTI JOIN
SELECT * FROM nation RIGHT ANTI JOIN region ON n_regionkey = r_regionkey;

Window functions

Syntax

window_function() OVER ([PARTITION BY expr] [ORDER BY expr] [ROWS BETWEEN ... AND ...])

Window functions compute a value for each row based on a set of related rows defined by the OVER clause. Unlike GROUP BY aggregates, they return one value per row without collapsing the result set. This lets you add computed columns alongside the original data—useful for ranking, running totals, and trend analysis.

Examples

-- Row number across all rows, ordered by region key
SELECT row_number() OVER (ORDER BY n_regionkey), n_regionkey, n_name FROM nation;

-- Row number within each partition
SELECT row_number() OVER (PARTITION BY n_regionkey ORDER BY n_regionkey), n_regionkey, n_name FROM nation;

-- Running total of order prices per year, per customer
SELECT
  o_orderdate,
  o_totalprice,
  sum(o_totalprice) OVER (
    PARTITION BY substr(o_orderdate, 1, 4)
    ORDER BY o_orderdate
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS sum
FROM orders
WHERE o_custkey = 53978
ORDER BY o_orderdate;

System info

Type conversion and string functions

JSON functions

Date and time functions

Aggregation functions

Window functions

All window functions use the OVER clause to define the window frame.