SQL WITH: CTEs (Common Table Expressions)

When a query grows long, with subqueries nested inside one another, it quickly becomes unreadable. A CTE (common table expression), introduced by WITH, lets you split the problem into named steps, which you then use like tables.

Syntax

WITH step AS (
  SELECT …
)
SELECT …
FROM step;

How it works

A CTE is declared at the start of the query: WITH name AS (query). In the main query, name is used exactly like a table. It only exists while the query runs: nothing is saved in the database.

Several steps

You can chain several CTEs, separated by commas, and each one can use the previous ones: WITH a AS (…), b AS (SELECT … FROM a) SELECT … FROM b. The query then reads from top to bottom, like a recipe.

Recursive CTEs

WITH RECURSIVE lets a CTE refer to itself. It is the ideal tool to walk through a hierarchy (an org chart) or to generate a series of values.

Diagram

Simplified diagram to show the idea. The examples below use the real SQL Quest practice database (a fictional online shop: see the full schema).

Examples with their results

WITH paniers AS (
  SELECT commande_id, SUM(quantite * prix_unitaire) AS montant
  FROM lignes_commande
  GROUP BY commande_id
)
SELECT COUNT(*) AS commandes, ROUND(AVG(montant), 2) AS panier_moyen, ROUND(MAX(montant), 2) AS plus_gros
FROM paniers;

WITH depenses AS (
  SELECT o.client_id, SUM(l.quantite * l.prix_unitaire) AS total
  FROM commandes o JOIN lignes_commande l ON l.commande_id = o.id
  GROUP BY o.client_id
),
moyenne AS (
  SELECT AVG(total) AS m FROM depenses
)
SELECT d.client_id, ROUND(d.total, 2) AS total
FROM depenses d, moyenne
WHERE d.total > moyenne.m
ORDER BY d.total DESC
LIMIT 6;

WITH RECURSIVE organigramme AS (
  SELECT id, prenom, nom, poste, 0 AS niveau
  FROM employes WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.prenom, e.nom, e.poste, o.niveau + 1
  FROM employes e JOIN organigramme o ON e.manager_id = o.id
)
SELECT niveau, prenom, nom, poste
FROM organigramme
ORDER BY niveau, nom
LIMIT 8;

Common mistakes

WITH t AS (SELECT * FROM produits); SELECT * FROM t;

WITH t AS (SELECT * FROM produits) SELECT * FROM t;

WITH a AS (SELECT 1 AS x) WITH b AS (SELECT x FROM a) SELECT * FROM b;

WITH a AS (SELECT 1 AS x), b AS (SELECT x FROM a) SELECT * FROM b;

WITH t AS (SELECT * FROM produits) SELECT 1; SELECT * FROM t;

WITH t AS (SELECT * FROM produits) SELECT COUNT(*) FROM t;

Exercises

Try writing the query yourself before opening the solution.

WITH ca AS (SELECT p.categorie, SUM(l.quantite * l.prix_unitaire) AS total FROM lignes_commande l JOIN produits p ON p.id = l.produit_id GROUP BY p.categorie) SELECT categorie, ROUND(total, 2) AS total FROM ca WHERE total > 30000;

WITH t AS (SELECT client_id, COUNT(*) AS nb FROM commandes GROUP BY client_id) SELECT ROUND(AVG(nb), 2) AS moyenne FROM t;

Frequently asked questions