SELECT

A one-shot SELECT reads the current state of one or more relations (joined; see Joins) and returns a finite result. For a query that keeps running and delivers changes, prefix it with STREAM; see Streaming queries.

Clause order

The clauses are parsed in this order; each is optional except FROM:

A relation is a table, a view, or a system relation such as system.tables (System tables).

Projections and aggregates

SELECT *
FROM balances;

SELECT address, token, amount
FROM balances
WHERE chain_id = 1
LIMIT 10;

SELECT COUNT(*) AS transfers, SUM(amount) AS volume
FROM transfers
WHERE token = 'USDC';

An output is a column, an expression or function call (see Functions), or an aggregate such as COUNT, SUM, MIN, MAX, AVG and COUNT(DISTINCT …). COUNT() and COUNT(*) count rows. Without AS, an output is named after its column; an aggregate is named after the function and its argument, such as count or sum_amount; another expression is named expression_<position>.

WHERE

A condition combines predicates with AND, OR, NOT and parentheses. A predicate is one of:

• <column> =, <>, !=, <, <=, > or >= a literal, a parameter or another column;
• <column> [NOT] IN (<literal>, …);
• <column> [NOT] BETWEEN <literal> AND <literal>;
• <column> [NOT] LIKE '<pattern>';
• <column> IS [NOT] NULL;
• (<column>, …) IN ((<literal>, …), …), a row-value list.

A comparison with NULL is not true, so WHERE keeps only rows whose condition is true. Use IS NULL to match nulls.

SELECT address, token, amount
FROM balances
WHERE (chain_id, address) IN ((1, '0x9c2e4f1a'), (10, '0x4b1e7c2a'));

A row-value IN matches whole tuples. When a row-value list covers the shard key, only the shards that own the listed tuples are read.

GROUP BY, HAVING and DISTINCT

SELECT token, COUNT(*) AS n
FROM transfers
WHERE chain_id = 1
GROUP BY token;

SELECT token, COUNT(*)
FROM transfers
WHERE chain_id = 1
GROUP BY token
HAVING COUNT(*) > 4;

SELECT DISTINCT token
FROM balances
WHERE chain_id = 1;

GROUP BY takes column names, output positions (GROUP BY 1), or an expression that matches one SELECT output. Every non-aggregate output is grouped. HAVING filters groups by their aggregate outputs.

ORDER BY, LIMIT and OFFSET

ORDER BY names SELECT outputs, each with ASC or DESC and optionally NULLS FIRST or NULLS LAST. LIMIT takes a positive integer; OFFSET an unsigned integer.

SELECT address, amount
FROM balances
WHERE chain_id = 1 AND token = 'USDC'
ORDER BY amount DESC
LIMIT 10 OFFSET 10;

Derived tables and subqueries nest anywhere a relation or a value is expected:

SELECT token, total
FROM (SELECT token, SUM(amount) AS total FROM balances GROUP BY token) totals
WHERE total > 0;

AS OF COMMIT

On a VERSIONED table, AS OF COMMIT <n> reads the rows as they stood at commit n:

SELECT address, token, amount
FROM balances AS OF COMMIT 42
WHERE chain_id = 1;

See Versioned tables.

Set operations

UNION, INTERSECT and EXCEPT, each with ALL or DISTINCT (the default), combine SELECT statements whose outputs match. INTERSECT binds tighter than UNION.

SELECT sender AS address
FROM transfers
UNION
SELECT recipient AS address
FROM transfers;

WITH

WITH <name> AS (<select>) <select> names a query for the statement that follows it:

WITH recent AS (
  SELECT token, amount
  FROM transfers
  WHERE transferred_at > '2026-10-01 00:00:00.000'
)
SELECT token, SUM(amount) AS volume
FROM recent
GROUP BY token;

Parameters

A ? marks a parameter whose value is sent beside the statement:

SELECT address, amount
FROM balances
WHERE chain_id = ? AND token = ?
LIMIT 100;

Windows

Window functions take OVER (PARTITION BY … ORDER BY …), and time windows (TUMBLE, HOP, SESSION) group streaming queries.