Databases
Intermediate
What is CTE (Common Table Expression)?
A temporary named result set defined within a SQL statement using the WITH clause, improving query readability and enabling recursion.
CTEs create named temporary tables within a query using WITH clause syntax. They improve readability by breaking complex queries into logical steps. Recursive CTEs can traverse hierarchical data like org charts or category trees.
Example: WITH active_users AS (SELECT * FROM users WHERE active = true) SELECT * FROM active_users WHERE created_at > '2024-01-01'. PostgreSQL CTEs are optimization barriers by default, though materialization can be controlled.
