Query syntax

SELECT

[WITH [RECURSIVE] cte_name AS [MATERIALIZED | NOT MATERIALIZED] (query), ...]
SELECT [ALL | DISTINCT [ON (expression [, ...])]] select_item [, ...]
[FROM source [, ...]]
[WHERE predicate]
[GROUP BY grouping_item [, ...]]
[HAVING predicate]
[QUALIFY predicate]
[ORDER BY expression [ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...]]
[LIMIT count [OFFSET count]];

SELECT * EXCLUDE (...), * REPLACE (...), * RENAME (...), COLUMNS(...), GROUP BY ALL and ORDER BY ALL are supported where the expanded columns are unambiguous.

SELECT * EXCLUDE (internal_id)
REPLACE (lower(email) AS email)
FROM customers;

DISTINCT ON keeps one row for each distinct key. Use an ORDER BY whose leading expressions match the distinct key so the selected row is deterministic.

SELECT DISTINCT ON (customer_id)
       customer_id, order_id, ordered_at
FROM sales.orders
ORDER BY customer_id, ordered_at DESC, order_id;

FROM-first syntax

For exploratory SQL, the query can begin with FROM. Add later clauses in normal logical order.

FROM sales.orders
SELECT customer_id, net_amount
WHERE status = 'paid'
ORDER BY ordered_at DESC
LIMIT 100;

Expressions

Supported expression forms include arithmetic and boolean operators, searched and simple CASE, explicit and try casts, IN, BETWEEN, LIKE, SIMILAR TO, IS NULL, IS [NOT] DISTINCT FROM, scalar subqueries, EXISTS, and ALL/ANY/SOME comparisons.

SELECT
  CASE WHEN amount < 0 THEN 'refund' ELSE 'sale' END AS kind,
  try_cast(raw_amount AS DECIMAL(18, 2)) AS amount
FROM raw_events
WHERE region IN ('eu', 'me')
  AND deleted_at IS NULL;

Joins

VegaDB supports inner, left, right, full, semi, anti, cross, natural, positional, as-of and lateral joins. USING and NATURAL return one coalesced output column for each matching name.

SELECT o.id, o.ordered_at, p.price
FROM orders o
ASOF LEFT JOIN prices p
  ON o.symbol = p.symbol
 AND o.ordered_at >= p.effective_at;

Outer joins, lateral correlation and as-of ordering create join barriers. The optimizer can reorder an inner-join island but will not move work across a barrier that changes row preservation.

Keyless and theta joins can expand rapidly. VegaDB applies warehouse resource and result limits even when the SQL is syntactically valid; add selective predicates before using them on large inputs.

Common table expressions

Ordinary, explicitly materialized, explicitly non-materialized and recursive CTEs are supported.

WITH RECURSIVE tree(id, parent_id, depth) AS (
  SELECT id, parent_id, 0 FROM nodes WHERE parent_id IS NULL
  UNION ALL
  SELECT n.id, n.parent_id, t.depth + 1
  FROM nodes n
  JOIN tree t ON n.parent_id = t.id
)
SELECT * FROM tree;

Recursive CTEs also support keyed union-table behavior with USING KEY for algorithms that repeatedly replace the current row for a logical key. The key columns must identify the retained row, and the anchor and recursive terms must resolve to compatible schemas. Keep recursion bounded with a monotonic condition or explicit depth predicate.

A retained materialized CTE is evaluated once for its bound identity and published to all consumer domains. Retries reuse that publication; a volatile producer is not re-evaluated independently for each consumer.

Grouping and aggregates

Use GROUP BY, GROUPING SETS, ROLLUP and CUBE. Aggregate calls support DISTINCT, a per-aggregate FILTER, and order modifiers where the function is order-sensitive.

SELECT region, channel,
       sum(amount) FILTER (WHERE status = 'paid') AS revenue,
       grouping(region, channel) AS grouping_mask
FROM orders
GROUP BY GROUPING SETS ((region, channel), (region), ());

The distributed plan uses partial and final aggregation when the function has a mergeable state. Order-sensitive aggregates retain a stable ordinal where required. Functions without a safe distributed state fail admission rather than merge incompatible local answers.

Window functions

SELECT customer_id, ordered_at, amount,
       row_number() OVER (
         PARTITION BY customer_id
         ORDER BY ordered_at
       ) AS order_number,
       sum(amount) OVER (
         PARTITION BY customer_id
         ORDER BY ordered_at
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_spend
FROM orders
QUALIFY order_number <= 10;

Named windows, partitioning, ordering and ROWS, RANGE or GROUPS frames follow the bound SQL semantics. QUALIFY filters after window evaluation.

Set operations

UNION, INTERSECT and EXCEPT support distinct and ALL forms. Inputs are cast to a compatible result schema before set semantics are applied.

UNION BY NAME and UNION ALL BY NAME align columns by name instead of ordinal position. A column present on only one side is filled with a typed NULL on the other side.

SELECT order_id, net_amount FROM current_orders
UNION ALL BY NAME
SELECT net_amount, order_id, source_system FROM archived_orders;

Use ordinal set operations when schemas are deliberately identical and name-based forms when upstream column order can change.

PIVOT and UNPIVOT

PIVOT monthly_sales
ON month
USING sum(amount)
GROUP BY region;

UNPIVOT quarterly_sales
ON q1, q2, q3, q4
INTO NAME quarter VALUE revenue;

When PIVOT expands a dynamic set of columns, VegaDB determines a bounded, deterministic output schema before the query runs.

Sampling and summarization

Bernoulli, system and reservoir sampling are supported. Supply a seed when the sample must be reproducible. SUMMARIZE returns column-level descriptive statistics for a table or query.

SELECT * FROM events USING SAMPLE 2% (BERNOULLI, 42);
SUMMARIZE orders;