withclauses

With clauses are a SQL construct that introduces one or more common table expressions (CTEs) to be referenced by the following statement. They help organize complex queries, improve readability, and enable modular data transformations. Many databases also support recursive CTEs using the same syntax.

Syntax: WITH [RECURSIVE] cte_name [(column_list)] AS ( subquery ) [, ...] main_query

In a with clause, each CTE defines a named result set that exists only for the duration of the following statement. The main query can reference one or more CTEs by name, and the CTEs may be defined in sequence, separated by commas.

Recursive CTEs let a query build a result set by referencing the CTE within its own definition. An anchor member starts the recursion, and a recursive member adds new rows until a termination condition is reached. Not all systems use the same syntax for recursion; PostgreSQL and SQLite use WITH RECURSIVE, while others implement recursion via alternative constructs.

Examples:

Non-recursive: WITH totals AS ( SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id ) SELECT * FROM totals WHERE total > 1000.

Recursive: WITH RECURSIVE nums(n) AS ( SELECT 1 UNION ALL SELECT n+1 FROM nums WHERE n < 5 ) SELECT * FROM nums;

Performance and optimization: Depending on the engine, CTEs may be inlined or materialized, which influences performance. While CTEs improve readability, they can affect optimization; in some cases rewriting as subqueries or views yields better plans.

See also: Common table expression, recursive query, SQL.