A Common Table Expression (CTE) in SQL is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.
It is defined using the WITH ... AS syntax.
Think of a CTE like a temporary view that exists only for the duration of the query.
✅ Basic Syntax
WITH cte_name AS (
SELECT column1, column2, ...
FROM some_table
WHERE condition
)
SELECT *
FROM cte_name;
📌 Example 1: Simple CTE
Suppose we have a table employees:
| emp_id | name | department | salary |
|---|---|---|---|
| 1 | Alice | HR | 5000 |
| 2 | Bob | IT | 7000 |
| 3 | Charlie | IT | 6000 |
| 4 | Diana | HR | 5500 |
| 5 | Eve | Finance | 6500 |
We want to get employees in IT earning more than 6000:
WITH HighPaidIT AS (
SELECT emp_id, name, salary
FROM employees
WHERE department = 'IT' AND salary > 6000
)
SELECT *
FROM HighPaidIT;
🔹 Result:
| emp_id | name | salary |
|---|---|---|
| 2 | Bob | 7000 |
📌 Example 2: Using CTE for Aggregation
Find the average salary per department, then list only departments with average salary > 6000:
WITH DeptAvg AS (
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
)
SELECT *
FROM DeptAvg
WHERE avg_salary > 6000;
📌 Example 3: Recursive CTE
Recursive CTEs are useful for hierarchical data (like org charts or folder trees).
Example: Generate numbers from 1 to 5.
WITH RECURSIVE Numbers AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1
FROM Numbers
WHERE n < 5
)
SELECT * FROM Numbers;
🔹 Result:
| n |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |