⚡ AdvancedintermediateCommonly used

How to use CTEs (WITH clause) in SQL

Write cleaner, reusable subqueries using Common Table Expressions.

Basic CTEAll databases

WITH active_users AS (
  SELECT id, name, email
  FROM users
  WHERE active = true
)
SELECT *
FROM active_users
WHERE country = 'Kenya';

The WITH clause defines a named temporary result set. The main query uses it like a table.

When to use: Replacing complex subqueries with readable named steps.

Multiple CTEsAll databases

WITH
  active_users AS (
    SELECT id, name
    FROM users
    WHERE active = true
  ),
  high_value_orders AS (
    SELECT user_id, SUM(total) AS total_spent
    FROM orders
    WHERE status = 'completed'
    GROUP BY user_id
    HAVING SUM(total) > 1000
  )
SELECT
  u.name,
  o.total_spent
FROM active_users u
INNER JOIN high_value_orders o ON u.id = o.user_id;

Chain multiple CTEs. Each can reference CTEs defined before it.

CTE vs subqueryAll databases

-- Subquery version (harder to read):
SELECT name, total_spent
FROM (
  SELECT u.name, SUM(o.total) AS total_spent
  FROM users u
  INNER JOIN orders o ON u.id = o.user_id
  GROUP BY u.id, u.name
) AS user_totals
WHERE total_spent > 1000;

-- CTE version (cleaner):
WITH user_totals AS (
  SELECT u.name, SUM(o.total) AS total_spent
  FROM users u
  INNER JOIN orders o ON u.id = o.user_id
  GROUP BY u.id, u.name
)
SELECT name, total_spent
FROM user_totals
WHERE total_spent > 1000;

CTEs and subqueries are often equivalent. CTEs are easier to read and debug.

Recursive CTE — hierarchy traversalAll databases

WITH RECURSIVE org_chart AS (
  -- Base case: top-level employees
  SELECT id, name, manager_id, 0 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive case: employees reporting to someone
  SELECT e.id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT level, name
FROM org_chart
ORDER BY level, name;

Recursive CTEs traverse hierarchies like org charts, category trees, or comment threads.

When to use: Querying tree or graph structures stored in a self-referencing table.

Common Mistakes

→CTEs are not necessarily cached — in PostgreSQL, each CTE reference may re-execute the query
→Infinite recursion in recursive CTEs — always have a base case and termination condition

Common Mistakes

Related Topics