WITH (COMMON TABLE EXPRESSIONS)Query Techniques
Buy Me A Coffee

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

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 8.0 Added in MySQL 8.0
PostgreSQL ✓ Supported 8.4 Well supported since PostgreSQL 8.4
SQL Server ✓ Supported 2005 Available since SQL Server 2005
Oracle ✓ Supported 9i Release 2 Available since Oracle 9i R2
SQLite ✓ Supported 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: Since 8.0 PostgreSQL: Since 8.4 SQL Server: Since 2005 Oracle: Since 9i Release 2 SQLite: Since 3.8.3

Per-Database Syntax & Notes

MySQL

Added in MySQL 8.0

WITH cte_name AS ( SELECT col1, col2 FROM tbl WHERE … ) SELECT * FROM cte_name;

PostgreSQL

Well supported since PostgreSQL 8.4

WITH cte_name AS ( SELECT col1, col2 FROM tbl WHERE … ) SELECT * FROM cte_name;

SQL Server

Available since SQL Server 2005

WITH cte_name AS ( SELECT col1, col2 FROM tbl WHERE … ) SELECT * FROM cte_name;

Oracle

Available since Oracle 9i R2

WITH cte_name AS ( SELECT col1, col2 FROM tbl WHERE … ) SELECT * FROM cte_name;

SQLite

Added in SQLite 3.8.3

WITH cte_name AS ( SELECT col1, col2 FROM tbl WHERE … ) SELECT * FROM cte_name;