Common Table Expressions (CTE) in SQL

🔹 What is a CTE?

A Common Table Expression (CTE) is a temporary, named result set defined with the WITH clause. It improves readability, lets you reuse logic within a query, and enables recursive patterns without creating permanent database objects.

Think of a CTE like a temporary view — it exists only for the duration of the statement that references it.

⚙️ 1️⃣ Basic Syntax

WITH cte_name AS (
  SELECT column1, column2
  FROM table_name
  WHERE condition
)
SELECT *
FROM cte_name
WHERE column1 = 'value';

The CTE is defined once and can be referenced multiple times in the final query.

⚙️ 2️⃣ Example – Simplifying Complex Queries

Without CTE

SELECT department, AVG(salary)
FROM employees
WHERE department IN (
  SELECT department
  FROM employees
  WHERE salary > 50000
)
GROUP BY department;

With CTE

WITH HighPaidEmployees AS (
  SELECT department
  FROM employees
  WHERE salary > 50000
)
SELECT department, AVG(salary)
FROM employees
WHERE department IN (SELECT department FROM HighPaidEmployees)
GROUP BY department;

✅ Using a CTE makes the business logic easier to read, debug, and reuse.

⚙️ 3️⃣ Using Multiple CTEs

You can chain multiple CTEs in a single WITH clause. Each definition is separated by a comma and can reference earlier CTEs.

WITH DeptCTE AS (
  SELECT dept_id, dept_name
  FROM departments
),
EmpCTE AS (
  SELECT emp_id, emp_name, dept_id
  FROM employees
)
SELECT e.emp_name, d.dept_name
FROM EmpCTE e
JOIN DeptCTE d ON e.dept_id = d.dept_id;

⚙️ 4️⃣ Recursive CTE

Recursive CTEs are perfect for hierarchical structures like org charts, file systems, or threaded comments. They combine an anchor query with a recursive member until no new rows are produced.

WITH EmployeeHierarchy AS (
  -- Anchor member (top-level)
  SELECT emp_id, emp_name, manager_id
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive member
  SELECT e.emp_id, e.emp_name, e.manager_id
  FROM employees e
  INNER JOIN EmployeeHierarchy eh
    ON e.manager_id = eh.emp_id
)
SELECT *
FROM EmployeeHierarchy;

✅ Most databases require a MAXRECURSION or depth limit safeguard to avoid infinite loops. Filter aggressively.

⚙️ 5️⃣ CTE vs Subquery

Feature	CTE	Subquery
Readability	Easier to read and maintain	Nested logic can be harder to follow
Reusability	Reference the CTE multiple times	Re-executes each time it is used
Performance	Can simplify complex joins and let the optimizer reuse work	May need to be recomputed repeatedly
Recursion	Supported in most modern SQL engines	Not supported

⚙️ 6️⃣ Updating Using CTE

CTEs work with UPDATE, DELETE, and INSERT statements. Use them to isolate the rows you want to affect before applying the change.

WITH LowSalary AS (
  SELECT emp_id, salary
  FROM employees
  WHERE salary < 40000
)
UPDATE employees
SET salary = salary + 5000
WHERE emp_id IN (SELECT emp_id FROM LowSalary);

✅ Modularizing the filter logic helps teams review and troubleshoot data changes.

⚙️ 7️⃣ Best Practices

Use descriptive names such as TopCustomers or ActiveOrders so CTE intent is obvious.
Limit recursion depth and apply filters early to avoid runaway queries.
Ensure supporting indexes exist before joining large CTE result sets.
Keep CTEs focused; avoid deeply nested chains that hurt readability.

💡 Combine CTEs with window functions for advanced analytics while keeping the SQL approachable.

← Previous Next →

← Back to Articles | 🏠 Back to Homepage