Home / SQL / Reference / Subqueries, CTEs and sets

SQL WITH (CTE)

Name the steps of a query (common table expressions).

Syntax

WITH name AS (SELECT …),
     other AS (SELECT … FROM name)
SELECT … FROM other;

Each CTE can use the ones before it. CTEs make long queries readable and testable.

Example

Query (on the shop database used in the SQL course)

WITH order_totals AS (
  SELECT order_id, SUM(quantity * unit_price) AS total
  FROM order_items GROUP BY order_id
)
SELECT COUNT(*) AS orders, ROUND(AVG(total), 2) AS avg_total, MAX(total) AS biggest
FROM order_totals;

Result

ordersavg_totalbiggest
51459.921711.0

1 row

Related

Learn it step by step in the free lesson CTEs & Window Functions, with exercises that check your queries.