Glossary · Database

CTE

Common Table Expression–a temporary named result set in SQL that makes complex queries more readable and reusable.

A Common Table Expression (CTE) is a temporary, named result set defined within a SQL statement using the WITH keyword. CTEs make complex queries more readable by breaking them into logical, named steps–similar to defining variables or helper functions in programming. A CTE can reference itself (recursive CTE) for hierarchical data traversal like organizational charts or category trees. CTEs can also reference other CTEs defined earlier in the same statement. While CTEs primarily improve readability, some databases materialize them for performance benefits. CTEs are widely supported across PostgreSQL, MySQL 8+, SQL Server, Oracle, and SQLite.

Fig — every answer ships with the tables, rows and SQL behind it.