SQL WITH Clause: Common Table Expressions (CTE) and Recursive Queries
Published: 2026-02-04
The WITH clause (also called Common Table Expression or CTE) lets you define temporary named result sets that you can reference in a query. This makes complex queries more readable and reusable. It’s available in most modern SQL databases including SQLite, PostgreSQL, MySQL 8.0+, SQL Server, and Oracle.
Sample Data
We use two tables in all examples:
employees table:
| id | name | manager_id | salary |
|---|---|---|---|
| 1 | Alice | NULL | 120000 |
| 2 | Bob | 1 | 80000 |
| 3 | Carol | 1 | 85000 |
| 4 | Dave | 2 | 60000 |
| 5 | Eve | 2 | 62000 |
| 6 | Frank | 3 | 58000 |
sales table:
| id | employee_id | amount | month |
|---|---|---|---|
| 1 | 2 | 5000 | January |
| 2 | 2 | 6000 | February |
| 3 | 3 | 7000 | January |
| 4 | 3 | 8000 | February |
| 5 | 4 | 3000 | January |
| 6 | 5 | 4000 | January |
| 7 | 6 | 2500 | February |
Basic WITH Clause (Non-Recursive)
A WITH clause defines one or more named temporary tables that exist only for the duration of the query.
Example: Total Sales by Employee
The employee_sales CTE calculates total sales per employee, then we join it with the employees table to show names.
Multiple CTEs
You can define multiple CTEs separated by commas. Later CTEs can reference earlier ones.
Example: Sales Analysis with Multiple Metrics
WITH RECURSIVE: Traversing Hierarchies
WITH RECURSIVE allows a CTE to reference itself, enabling you to traverse hierarchical or tree-like data. This is useful for organization charts, category hierarchies, or any parent-child relationships.
A recursive CTE has two parts:
- Base query: The initial set of rows
- Recursive query: References the CTE itself to find related rows
Example: Organization Chart (All Employees Under a Manager)
Find all employees under Alice (the CEO):
Example: Chain of Command (Find Manager Hierarchy)
Find the full chain of command for Dave (who reports to Bob, who reports to Alice):
WITH RECURSIVE Syntax Across Databases
WITH RECURSIVE is supported in:
- SQLite — 3.8.3+ (2013)
- PostgreSQL — 8.4+ (2009)
- SQL Server — 2005+ (called “Recursive CTE”)
- Oracle — 11g+ (called “Recursive Subquery Factoring”)
- MySQL — 8.0+ (2018)
Note: MySQL older versions and some other databases don’t support recursive CTEs—they may require different approaches like stored procedures or application-level recursion.
Common Use Cases for WITH CLAUSES
- Simplifying complex queries — Break a large query into logical steps
- Reusing subqueries — Define a calculation once, use it multiple times
- Hierarchical traversal — Navigate trees (org charts, categories, comments)
- Generating sequences — Create number ranges or date ranges
- Progressive aggregations — Build up results in steps
Summary
WITH(CTE) creates a named temporary result set for use in a query- Multiple CTEs can be chained together
WITH RECURSIVEallows a CTE to reference itself for hierarchical queries- Recursive CTEs require a base case and a recursive case separated by
UNION ALL - Supported in all modern SQL databases (though syntax may vary slightly)
Try modifying the examples above to explore different hierarchies or calculations!