CTE

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.