CTEs and subqueries

ReferenceApplies to: Unified engine deployments

A common table expression names an intermediate query within one statement. A subquery places a query inside another query. Neither automatically persists its result as a table.

Common table expressions

Use WITH to make a query easier to read. A CTE is scoped to its statement. Later CTEs can refer to earlier ones. Recursive CTEs and materialization modifiers are unavailable.

WITH category_prices AS (
  SELECT i_category, AVG(CAST(i_current_price AS DOUBLE)) AS average_price
  FROM demo.tpcds.item
  WHERE i_category IS NOT NULL
  GROUP BY i_category
)
SELECT i_category, average_price
FROM category_prices
ORDER BY average_price DESC, i_category
LIMIT 5;

Derived tables

A subquery in FROM is a derived table. Give it an alias. It is useful for filtering a window result because QUALIFY is unavailable.

SELECT item_key, category, position
FROM (
  SELECT i_item_sk AS item_key, i_category AS category,
         ROW_NUMBER() OVER (PARTITION BY i_category ORDER BY i_item_sk) AS position
  FROM demo.tpcds.item
) AS ranked
WHERE position <= 2
ORDER BY category NULLS LAST, item_key
LIMIT 20;

Scalar subqueries

A scalar subquery supplies a single value. It must return at most one row. Zero rows produce NULL; multiple rows produce an error. Aggregate subqueries are a convenient way to guarantee one row.

SELECT i_item_sk, i_current_price
FROM demo.tpcds.item
WHERE CAST(i_current_price AS DOUBLE) > (
  SELECT AVG(CAST(i_current_price AS DOUBLE)) FROM demo.tpcds.item
)
ORDER BY i_item_sk
LIMIT 10;

EXISTS and IN

EXISTS tests whether its subquery returns any rows. IN compares against returned values. NOT IN can become NULL when its input contains NULL, so do not substitute it for NOT EXISTS without considering null semantics. Correlated and null-aware forms can be restricted by the selected backend; an ordinary key join is often easier to inspect.

SELECT i_item_sk, i_category
FROM demo.tpcds.item
WHERE i_category IN ('Books', 'Electronics')
ORDER BY i_item_sk
LIMIT 10;

Transformation Engine

Learn Laminar

Workloads

SQL reference

HTTP API

Recipes

Operations

Common table expressions

Derived tables

Scalar subqueries

EXISTS and IN

​Common table expressions

​Derived tables

​Scalar subqueries

​EXISTS and IN

Common table expressions

Derived tables

Scalar subqueries

EXISTS and IN