What is a CTE (WITH clause) useful for?
Options
- Creating physical tables on the disk of the database server
- Naming a subquery so a complex query reads step by step (Correct option)
- Encrypting the data that is returned by the query
- Speeding up every query of the database automatically
Answer
Naming a subquery so a complex query becomes readable step by step (and allows recursion)
Why
A Common Table Expression gives a temporary name to a query inside a statement. It makes long queries readable: define step 1 and step 2 as named blocks, then combine them.
Example: first compute totals per customer, then select the biggest ones.
WITH RECURSIVE can walk hierarchies such as an employee-manager tree.
WITH totals AS (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
)
SELECT c.name, t.total
FROM totals t
JOIN customers c ON c.id = t.customer_id
WHERE t.total > 1000;Remember
CTE = named subquery for readable queries.