What is CTE in SQL?

CTE (Common Table Expression) is a named temporary result set defined with WITH, used in a single SELECT/INSERT/UPDATE/DELETE. Syntax: WITH cte_name AS (SELECT ...) SELECT FROM cte_name. Benefits: Improves readability, allows recursion, enables reuse. Example: WITH ranked AS (SELECT id, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY sal DESC) rn FROM emp) SELECT FROM ranked WHERE rn <= 3. Best practice: Use for complex queries; avoid deep nesting. Some engines materialize CTEs; check optimization. Recursive CTEs for hierarchies (org chart, tree traversal). Why it matters: Design choices compound at scale—wrong approach can cause 100× overhead. Scalability trade-offs: Profile before optimizing; validate on sample then full. Cost implications: Suboptimal choices multiply at billion-row scale.