WITH Clause

The WITH clause is an optional clause that precedes the body of the SELECT statement, and defines one or more CTEs (common table expressions) that can be referenced later in the statement.

Syntax

Basic CTE

[ WITH
    cte_name1 [ ( cte_column_list ) ] AS ( SELECT ... )
  [ , cte_name2 [ ( cte_column_list ) ] AS ( SELECT ... ) ]
  [ , cte_nameN [ ( cte_column_list ) ] AS ( SELECT ... ) ]
]
SELECT ...

Recursive CTE

[ WITH [ RECURSIVE ]
    cte_name1 ( cte_column_list ) AS ( anchorClause UNION ALL recursiveClause )
  [ , cte_name2 ( cte_column_list ) AS ( anchorClause UNION ALL recursiveClause ) ]
  [ , cte_nameN ( cte_column_list ) AS ( anchorClause UNION ALL recursiveClause ) ]
]
SELECT ...

Where:

• anchorClause: SELECT anchor_column_list FROM ...
• recursiveClause: SELECT recursive_column_list FROM ... [ JOIN ... ]

Parameters

Examples

Basic CTE

WITH high_value_customers AS (
    SELECT customer_id, customer_name, total_spent
    FROM customers
    WHERE total_spent > 10000
)
SELECT c.customer_name, o.order_date, o.order_amount
FROM high_value_customers c
JOIN orders o ON c.customer_id = o.customer_id
ORDER BY o.order_date DESC;

Multiple CTEs

WITH
  regional_sales AS (
    SELECT region, SUM(sales_amount) as total_sales
    FROM sales_data
    GROUP BY region
  ),
  top_regions AS (
    SELECT region, total_sales
    FROM regional_sales
    WHERE total_sales > 1000000
  )
SELECT r.region, r.total_sales
FROM top_regions r
ORDER BY r.total_sales DESC;

Recursive CTE

WITH RECURSIVE countdown AS (
    -- Anchor clause: starting point
    SELECT 10 as num

    UNION ALL

    -- Recursive clause: repeat until condition
    SELECT num - 1
    FROM countdown
    WHERE num > 1  -- Stop condition
)
SELECT num FROM countdown
ORDER BY num DESC;

Usage Notes

• CTEs are temporary named result sets that exist only for the duration of the query
• CTE names must be unique within the same WITH clause
• A CTE can reference previously defined CTEs in the same WITH clause
• Recursive CTEs require both an anchor clause and a recursive clause connected by UNION ALL
• The RECURSIVE keyword is required when using recursive CTEs