What is a Common Table Expression, and how is it different from a subquery?
A common table expression is a query that you give a name to, so that you can use it like a table further down the query.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount DECIMAL(10, 2) NOT NULL
);
WITH customer_totals AS (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
)
SELECT
c.name,
totals.total
FROM customers c
JOIN customer_totals totals ON totals.customer_id = c.id
WHERE totals.total > 100
ORDER BY totals.total DESC;
So how is it different from a subquery? In four ways.
The first one is the name. A subquery in the FROM clause also needs an alias, but a common table expression gives you a name that you can use again and again, and it reads like a sentence.
The second one is that you can use it more than once. A derived table has to be written out again if you want it twice, and the engine then works it out twice. A common table expression can be referenced several times.
The third one is that several of them can be chained, and each one can use the one before it. That is what makes them good for a query with several layers of logic.
The fourth one is that a common table expression can be recursive, which a plain subquery cannot be. That is the subject of a later question in this topic.
So what are the rules? You start with the word WITH, then the name, then the word AS, then the query in brackets. If there is more than one, you separate them with commas. And in most engines you put the word RECURSIVE after the WITH when one of them refers to itself.
The next question in this topic asks the question that matters most, which is when one of these actually changes the way the query runs.