WITH

Use WITH to specify a Common Table Expression.

What is a Common Table Expression (CTE)?

A Common Table Expression (CTE) is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs help to break down complex queries into more readable parts and can be referenced multiple times within the same query.

Basic syntax of CTE

CTEs are typically defined using the WITH keyword. The basic syntax is as follows:

  1. WITH cte_name [(column1, column2, ...)] AS (
  2.     QUERY
  3. )
  4. SELECT ...
  5. FROM cte_name;

Examples

Non-recursive CTE

  1. WITH cte AS (SELECT number FROM numbers LIMIT 2) SELECT * FROM cte t1, cte t2;
  1. +--------+--------+
  2. | number | number |
  3. +--------+--------+
  4. |      0 |      0 |
  5. |      0 |      1 |
  6. |      1 |      0 |
  7. |      1 |      1 |
  8. +--------+--------+

If a parenthesized list of names follows the CTE name, those names are the column names:

  1. WITH cte (col1, col2) AS
  2. (
  3.   SELECT 1, 2
  4.   UNION ALL
  5.   SELECT 3, 4
  6. )
  7. SELECT col1, col2 FROM cte;

The number of names in the list must be the same as the number of columns in the result set.

  1. +------+------+
  2. | col1 | col2 |
  3. +------+------+
  4. |    1 |    2 |
  5. |    3 |    4 |
  6. +------+------+

Join two CTEs:

  1. WITH
  2.   cte1 AS (SELECT number AS a FROM NUMBERS LIMIT 2),
  3.   cte2 AS (SELECT number AS b FROM NUMBERS LIMIT 2)
  4. SELECT * FROM cte1 JOIN cte2
  5. ON cte1.a = cte2.b;
  1. +------+------+
  2. | a    | b    |
  3. +------+------+
  4. |    1 |    1 |
  5. |    0 |    0 |
  6. +------+------+

Recursive CTE

Recursive CTE is not implemented currently.