SELECT Statement

The SELECT statement retrieves rows from tables in the Chalk catalog, from configured data sources, and from table-valued functions. It is the primary statement in Chalk SQL: every query reads data through a SELECT (including the query embedded in COPY ... TO and EXPLAIN).

​

Examples

-- Select all columns from a data source table
SELECT * FROM "my_postgres.public.users";

-- Aggregate transactions per merchant
SELECT merchant_id, count(*) AS txn_count, sum(amount) AS total
FROM "my_bigquery.my_dataset.transactions"
GROUP BY merchant_id
ORDER BY total DESC
LIMIT 10;

-- Join tables that live in different data sources
SELECT u.user_id, sum(t.amount) AS user_spending
FROM "my_postgres.public.users" u
LEFT JOIN "my_bigquery.my_dataset.transactions" t ON u.user_id = t.user_id
GROUP BY u.user_id;

-- Use a CTE and a window function to pick each user's latest transaction
WITH ranked AS (
    SELECT
        user_id,
        amount,
        row_number() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM "my_bigquery.my_dataset.transactions"
)
SELECT user_id, amount FROM ranked WHERE rn = 1;

-- Query historical feature values from the offline store
SELECT pkey, value, observed_at
FROM "chalk.historical_values.user.account_balance"
WHERE observed_at > TIMESTAMP '2026-08-25 00:00:00';

​

Syntax

Clauses appear in the following order. Every clause other than the SELECT list is optional.

[ WITH cte_name [ (column_alias, ...) ] AS ( select ) [, ...] ]
SELECT [ DISTINCT | DISTINCT ON (expression, ...) ] expression [ AS alias ] [, ...]
[ FROM source [, ...] ]
[ [ join_type ] JOIN source [ ON condition | USING (column, ...) ] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition ]
[ QUALIFY condition ]
[ ORDER BY expression [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] [, ...] ]
[ LIMIT count [ OFFSET count ] ]

Two or more SELECT statements can also be combined with set operations (UNION, INTERSECT, EXCEPT).

​

WITH clause

Common table expressions (CTEs) name a subquery so it can be referenced like a table in the rest of the statement. CTEs may declare column aliases and may reference earlier CTEs in the same WITH list.

WITH spend(user_id, total) AS (
    SELECT user_id, sum(amount)
    FROM "my_bigquery.my_dataset.transactions"
    GROUP BY user_id
)
SELECT u.user_id, s.total
FROM "my_postgres.public.users" u
JOIN spend s ON u.user_id = s.user_id;

Not currently supported:

• WITH RECURSIVE
• MATERIALIZED CTEs

​

SELECT list

The SELECT list contains any supported expression or function, optionally renamed with AS. An alias defined earlier in the SELECT list can be referenced by later expressions in the same list (a “lateral” column alias):

SELECT amount * 100 AS cents, cents / 3 AS thirds
FROM "my_bigquery.my_dataset.transactions";

DISTINCT deduplicates the result rows, and DISTINCT ON (expressions) keeps one row per distinct value of the given expressions:

-- One row per user
SELECT DISTINCT ON (user_id) user_id, amount
FROM "my_bigquery.my_dataset.transactions"
ORDER BY user_id, created_at DESC;

The star expression * selects all columns of the source. Restrictions:

• SELECT * cannot be combined with GROUP BY or aggregate functions; list the grouped columns explicitly instead.
• * EXCLUDE (...) and * REPLACE (...) are not supported.
• * cannot appear inside another expression (e.g. count(*) is supported, but arbitrary functions of * are not).

​

FROM clause

The FROM clause names the sources rows are read from. A source can be:

• A catalog or data source table, referenced with a quoted fully-qualified name: "catalog.schema.table". This includes data source tables (e.g. "my_postgres.public.users") and Chalk catalog views such as "chalk.online_store.keys" or "chalk.historical_values.user". See Chalk Catalog Components.

• A table-valued function, such as read_parquet(...), query_values_from_operation_ids(...), or get_dataset_givens(...). See the table-valued function reference.

• A subquery, wrapped in parentheses and optionally aliased.

• A VALUES list, for inline literal rows:

  SELECT * FROM (VALUES (1, 'ada'), (2, 'grace')) AS t(id, name);
  

• A file path or glob, which reads files directly:

  SELECT * FROM 's3://my-bucket/events/*.parquet';
  

A SELECT without a FROM clause evaluates its expressions over a single row (SELECT 1 + 1).

​

TABLESAMPLE

File-backed scans (direct file paths and read_parquet(...)) support percentage-based sampling with the SYSTEM and BERNOULLI methods:

SELECT * FROM 's3://my-bucket/events/*.parquet' TABLESAMPLE SYSTEM (10 PERCENT);

Not currently supported: sampling catalog or data source tables, RESERVOIR sampling, absolute row-count samples, sampling subqueries or joins, and the USING SAMPLE clause.

​

JOIN clause

Chalk SQL supports joining tables, including tables that live in different data sources, with the following join types:

• [INNER] JOIN
• LEFT [OUTER] JOIN
• RIGHT [OUTER] JOIN
• FULL [OUTER] JOIN
• CROSS JOIN
• ASOF JOIN and ASOF LEFT JOIN, for matching each row to the nearest preceding row by an inequality condition (commonly a timestamp)

Join conditions can be written with ON <condition> or USING (columns). Inner joins also accept non-equality ON conditions.

-- Match each transaction to the feature value observed at or before it
SELECT t.user_id, t.amount, f.value AS balance_at_txn
FROM "my_bigquery.my_dataset.transactions" t
ASOF LEFT JOIN "chalk.historical_values.user.account_balance" f
ON t.user_id = f.pkey AND t.created_at >= f.observed_at;

Not currently supported: NATURAL joins, POSITIONAL joins, and SEMI/ANTI joins.

​

WHERE clause

Filters rows with any supported boolean expression, including subqueries with IN and EXISTS. Where possible, Chalk SQL pushes filters down into the underlying data source so that less data is scanned; pushdown is best-effort and does not change results.

SELECT * FROM "my_postgres.public.users"
WHERE signup_date > DATE '2026-01-01'
  AND country IN ('US', 'CA');

​

GROUP BY clause

Groups rows by one or more expressions for use with aggregate functions. HAVING filters the grouped rows and requires a GROUP BY clause or an aggregate in the SELECT list.

SELECT date_trunc('day', created_at) AS day, count(*) AS txns
FROM "my_bigquery.my_dataset.transactions"
GROUP BY date_trunc('day', created_at)
HAVING count(*) > 100;

GROUPING SETS, ROLLUP, and CUBE are supported for aggregating across several groupings in one query:

-- Per-country counts plus a grand-total row (country IS NULL)
SELECT country, count(*) FROM "my_bigquery.my_dataset.transactions"
GROUP BY ROLLUP (country);

Not currently supported: GROUP BY ALL.

​

Window functions and QUALIFY

Window functions compute a value over a window of rows related to the current row, using OVER (PARTITION BY ... ORDER BY ... [frame]). ROWS frames with literal bounds are supported (e.g. ROWS BETWEEN 1 PRECEDING AND CURRENT ROW).

The QUALIFY clause filters on window function results, the way HAVING filters on aggregate results. A query must contain at least one window function to use QUALIFY:

SELECT user_id, amount,
       row_number() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM "my_bigquery.my_dataset.transactions"
QUALIFY rn <= 3;

Not currently supported in window functions: the FILTER clause, frame EXCLUDE options, non-literal frame bounds, and RANGE frames with offset bounds (the default frame implied by ORDER BY works).

​

ORDER BY clause

Sorts the result by one or more expressions. Each sort key accepts ASC (default) or DESC, and NULLS FIRST / NULLS LAST. Sort keys can be arbitrary expressions (including expressions not in the SELECT list), positional references to SELECT list items (ORDER BY 2), or ORDER BY ALL to sort by every selected column left to right.

​

LIMIT and OFFSET clauses

LIMIT restricts the number of returned rows and OFFSET skips rows before returning. Both must be literal integers.

SELECT * FROM "my_postgres.public.users" ORDER BY user_id LIMIT 100 OFFSET 200;

​

Set operations

Two or more SELECT statements can be combined with:

• UNION (deduplicates) and UNION ALL (keeps duplicates)
• INTERSECT
• EXCEPT

An ORDER BY following a set operation may only reference output column names or positions. To apply a LIMIT (or sort by a computed expression), wrap the set operation in a subquery:

SELECT * FROM (
    SELECT user_id FROM "my_postgres.public.users"
    UNION
    SELECT user_id FROM "my_postgres.public.archived_users"
)
ORDER BY user_id
LIMIT 50;

​

Subqueries

Subqueries are supported in the FROM clause, as scalar expressions, and in IN / EXISTS predicates, including correlated forms:

SELECT * FROM "my_postgres.public.users" u
WHERE EXISTS (
    SELECT 1 FROM "my_bigquery.my_dataset.transactions" t
    WHERE t.user_id = u.user_id AND t.amount > 10000
);

Some constructs are not currently supported inside subqueries, including GROUP BY within an EXISTS subquery, DISTINCT in correlated scalar subqueries, and ARRAY(SELECT ...).