What is a CTE (Common Table Expression) in SQL?
Why Interviewers Ask This
This question targets practical, hands-on experience with MySQL / SQL. Interviewers want to see if you've worked with these concepts in real projects, not just read about them. Strong answers include concrete examples.
Answer
A CTE (Common Table Expression) is a named temporary result set defined within a single SQL statement using the WITH clause. It exists only for the duration of the query. Syntax: WITH cte_name AS ( SELECT ... FROM ... WHERE ... ) SELECT * FROM cte_name WHERE ...;. Multiple CTEs: WITH cte1 AS (...), cte2 AS (SELECT * FROM cte1 WHERE ...) SELECT * FROM cte2;. Benefits over subqueries: (1) Readability — complex queries become modular and named; (2) Reusability — reference the same CTE multiple times in the query (a subquery would be duplicated); (3) Recursion — recursive CTEs solve hierarchical data problems (trees, org charts, graphs). Recursive CTE example (employee hierarchy): WITH RECURSIVE org AS ( SELECT id, name, manager_id, 0 AS level FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id, org.level + 1 FROM employees e JOIN org ON e.manager_id = org.id ) SELECT * FROM org;. MySQL 8.0+ supports CTEs. Performance: CTEs in MySQL are not materialized by default in older versions — they may be inlined (re-evaluated each time) rather than cached.
Pro Tip
Before answering, structure your response: one-line definition → real-world analogy → concrete example from a project. This makes even complex MySQL / SQL answers easy to follow.