MENU


CTE

Common Table Expressions (CTEs) define a temporary named result set that can be referenced within a query, similar to a subquery but with added benefits. CTEs make it easier to write and read complex queries by breaking them into smaller, more manageable pieces.

WITH...SELECT...

WITH
cte1 AS (SELECT a, b FROM table1),
cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

A CTE can also be nested inside another CTE's scope:

WITH cte1 AS (SELECT 1)
SELECT * FROM (WITH cte2 AS (SELECT 2) SELECT * FROM cte2 JOIN cte1) AS dt;

WITH cte1 AS (SELECT 1)
SELECT * FROM (WITH cte2 AS (SELECT 2) SELECT * FROM cte2 JOIN cte1) AS dt;

21
21
WITH employee_sales AS (
SELECT employee_id, SUM(amount) AS total_sales
FROM sales
GROUP BY employee_id
)
SELECT name, total_sales
FROM employees
JOIN employee_sales ON employees.id = employee_sales.employee_id;

Parenthesized Columns

If a parenthesized list of names follows the CTE name, those names become the CTE's column names:
WITH cte (col1, col2) AS
(
SELECT 1, 2
UNION ALL
SELECT 3, 4
)
SELECT col1, col2 FROM cte;

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

col1col2
12
34

Recursion

A CTE can also be recursive, meaning it can reference itself, which makes it useful for handling hierarchical data:
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

-- Basic recursive CTE: generate the integers 1 through 5
WITH RECURSIVE cte (n) AS
(
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

n
1
2
3
4
5

Complex Variations

A CTE is not limited to standalone SELECT statements — it can be used across a wide range of SQL contexts:
WITH ... SELECT ...
WITH ... UPDATE ...
WITH ... DELETE ...
SELECT ... WHERE id IN (WITH ... SELECT ...) ...
SELECT * FROM (WITH ... SELECT ...) AS dt ...
INSERT ... WITH ... SELECT ...
REPLACE ... WITH ... SELECT ...
CREATE TABLE ... WITH ... SELECT ...
CREATE VIEW ... WITH ... SELECT ...
DECLARE CURSOR ... WITH ... SELECT ...
EXPLAIN ... WITH ... SELECT ...
WITH cte1 AS (SELECT 1)
SELECT * FROM (WITH cte2 AS (SELECT 2) SELECT * FROM cte2 JOIN cte1) AS dt;

A Challenge on HackerRank

Given a Hackers table (hacker_id, name) and a Challenges table (challenge_id, hacker_id), print the hacker_id, name, and total number of challenges created by each student, sorted by challenge count descending, then by hacker_id. Students tied on a challenge count that is not the maximum count are excluded from the result. A CTE cleanly separates the per-student count (T1) from the frequency of each count value (T2):
WITH
T1 AS(
SELECT h.hacker_id AS hacker_id, name,
COUNT(challenge_id) AS challenges_created
FROM Hackers h JOIN Challenges c ON h.hacker_id = c.hacker_id
GROUP BY h.hacker_id, name
),
T2 AS(
SELECT challenges_created,
COUNT(challenges_created) AS c
FROM T1 GROUP BY challenges_created
)
SELECT T1.hacker_id, name, T1.challenges_created
FROM T1 JOIN T2 ON T1.challenges_created = T2.challenges_created
WHERE c=1 OR T1.challenges_created=(SELECT MAX(challenges_created) FROM T1)
ORDER BY T1.challenges_created DESC, T1.hacker_id;
(from https://www.hackerrank.com/challenges/challenges/problem) 

-- HackerRank "Challenges" problem, solved with two chained CTEs
WITH
   T1 AS(
      SELECT h.hacker_id AS hacker_id, name,
                   COUNT(challenge_id) AS challenges_created
      FROM Hackers h JOIN Challenges c ON h.hacker_id = c.hacker_id
      GROUP BY h.hacker_id, name
   ),
   T2 AS(
      SELECT challenges_created,
                   COUNT(challenges_created) AS c
      FROM T1 GROUP BY challenges_created
   )
SELECT T1.hacker_id, name, T1.challenges_created
FROM T1 JOIN T2 ON T1.challenges_created = T2.challenges_created
WHERE c=1 OR T1.challenges_created=(SELECT MAX(challenges_created) FROM T1)
ORDER BY T1.challenges_created DESC, T1.hacker_id;
See also Window Functions, which is frequently combined with a CTE (for example, to delete duplicate rows via a ranked CTE), and Subquery for the non-named alternative.