Common Table Expressions (CTEs) provide a way to write auxiliary statements for use in a larger query.
Defines named temporary result sets for use within a single query, improving readability by extracting complex logic into readable named blocks. Recursive CTEs can traverse hierarchical data (org charts, trees). Non-recursive CTEs are optimized away if referenced once.
Compatibility
| Database System | Support Status | Since Version | Notes |
|---|---|---|---|
| MySQL | Native | 8.0 | Added in MySQL 8.0 |
| PostgreSQL | Native | 8.4 | Well supported since PostgreSQL 8.4 |
| SQL Server | Native | 2005 | Available since SQL Server 2005 |
| Oracle | Native | 9i Release 2 | Available since Oracle 9i R2 |
| SQLite | Native | 3.8.3 | Added in SQLite 3.8.3 |
Details
Common Table Expressions (CTEs) are named temporary result sets that exist within the scope of a single statement.
Standard Syntax
WITH cte_name AS (
SELECT column1, column2
FROM table_name
WHERE condition
)
SELECT * FROM cte_name;
Version Support
MySQL: Native since 8.0
PostgreSQL: Native since 8.4
SQL Server: Native since 2005
Oracle: Native since 9i Release 2
SQLite: Native since 3.8.3
Per-Database Syntax & Notes
MySQL Native syntax
Added in MySQL 8.0
WITH cte_name AS (
SELECT col1, col2
FROM tbl
WHERE …
)
SELECT *
FROM cte_name;
PostgreSQL Native syntax
Well supported since PostgreSQL 8.4
WITH cte_name AS (
SELECT col1, col2
FROM tbl
WHERE …
)
SELECT *
FROM cte_name;
SQL Server Native syntax
Available since SQL Server 2005
WITH cte_name AS (
SELECT col1, col2
FROM tbl
WHERE …
)
SELECT *
FROM cte_name;
Oracle Native syntax
Available since Oracle 9i R2
WITH cte_name AS (
SELECT col1, col2
FROM tbl
WHERE …
)
SELECT *
FROM cte_name;
SQLite Native syntax
Added in SQLite 3.8.3
WITH cte_name AS (
SELECT col1, col2
FROM tbl
WHERE …
)
SELECT *
FROM cte_name;