A CTE names a subquery at the top of a statement with WITH name AS (…). Queries read top-down like a pipeline instead of inside-out.

Syntax

SQL

WITH dept_stats AS (
    SELECT dept_id, COUNT(*) AS staff, AVG(salary) AS avg_sal
    FROM employees
    WHERE dept_id IS NOT NULL
    GROUP BY dept_id
)
SELECT d.name, s.staff, ROUND(s.avg_sal) AS avg_sal
FROM dept_stats s
JOIN departments d ON d.dept_id = s.dept_id
ORDER BY s.avg_sal DESC;

Output

name        | staff | avg_sal
------------+-------+---------
Engineering | 4     | 115000.0
Sales       | 3     | 84000.0
HR          | 2     | 82500.0

Chaining CTEs

Each CTE can use the ones defined before it. A multi-step report becomes a list of named steps.

SQL

WITH order_totals AS (           -- step 1: value of each valid order
    SELECT o.order_id, o.customer_id, SUM(oi.quantity * oi.unit_price) AS total
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.status <> 'cancelled'
    GROUP BY o.order_id, o.customer_id
),
customer_totals AS (             -- step 2: per customer
    SELECT customer_id, COUNT(*) AS orders, SUM(total) AS spent
    FROM order_totals
    GROUP BY customer_id
),
overall AS (                     -- step 3: one number to compare against
    SELECT AVG(spent) AS avg_spent FROM customer_totals
)
SELECT c.name, ct.orders, ct.spent,
       CASE WHEN ct.spent > o.avg_spent THEN 'above avg' ELSE 'below avg' END AS segment
FROM customer_totals ct
JOIN customers c ON c.customer_id = ct.customer_id
CROSS JOIN overall o
ORDER BY ct.spent DESC;

Output

name   | orders | spent | segment
-------+--------+-------+----------
Ananya | 3      | 68400 | above avg
Rahul  | 2      | 61200 | above avg
Sneha  | 2      | 13700 | below avg

CTE vs subquery vs view vs temp table

	Scope	Reusable in the statement	Stored
Derived table (subquery)	One place in one query	No — repeat it	No
CTE	One statement	Yes — reference by name, many times	No
View	The database	Across all queries	Definition only
Temporary table	Session	Across statements	Yes (data, can be indexed)

CTEs with DML

SQL

-- give a 5% raise to everyone below their department's average
WITH below AS (
    SELECT e.emp_id
    FROM employees e
    WHERE e.salary < (SELECT AVG(salary) FROM employees i WHERE i.dept_id = e.dept_id)
)
UPDATE employees
SET salary = salary * 105 / 100
WHERE emp_id IN (SELECT emp_id FROM below);

SELECT name, salary FROM employees WHERE emp_id IN (3, 4, 6, 7, 8) ORDER BY emp_id;

Output

name   | salary
-------+-------
Meera  | 99750
Karan  | 99750
Arjun  | 73500
Neha   | 75600
Vikram | 84000

What is a CTE and why use it?

Is a CTE faster than a subquery?