What is a CTE and how is it different from a subquery?
SHORT ANSWER
A CTE (WITH clause) names a query at the top of a statement so it can be read like a step and reused several times. Results are the same as a subquery, but CTEs read better and also support recursion.
Explain it simply
A subquery is a calculation scribbled in the middle of a sentence. A CTE is writing the calculation first with a heading, "Step 1: dept averages", and then using that name below. It is easier to read and can be used more than once.
Example
sql
WITH dept_avg AS (
SELECT dept_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept_id
)
SELECT e.name, e.salary
FROM employees e
JOIN dept_avg d ON d.dept_id = e.dept_id
WHERE e.salary > d.avg_sal;Key points:
- A CTE lives only for the one statement that follows.
- You can chain several:
WITH a AS (...), b AS (...) SELECT .... WITH RECURSIVE(justWITHin SQL Server and Oracle) can walk a hierarchy such as an employee-manager chain, which a plain subquery cannot do.- Performance is usually the same as the subquery version. Some databases materialise a CTE once, others inline it; PostgreSQL before version 12 always materialised it.
What to say in the interview
"A CTE is a named, temporary result set within one statement. It improves readability, can be referenced multiple times, and supports recursion. It is not a stored object like a view."
Common mistakes
- Assuming a CTE is automatically faster or cached across queries.