Common table expressions are a feature of SQL that lets a developer create a query that can be referenced multiple times. This feature gives developers another tool to add flexibility or just to simplify code.
Why Common Table Expression?
Why is it called a Common Table Expression (CTE)? Because that’s what the documentation calls it!
No, seriously, that’s it. Different databases have different terms for this feature. That’s what we call it in SQL Server.
What does a CTE do?
A CTE effectively creates a temporary view that a developer can reference multiple times in the underlying query. You can think of the CTE as if it were an inline view.
Here’s a slight re-write of a StackExchange query to find interesting and unanswered questions.
WITH unanswered AS (
FROM dbo.Posts p
WHERE (SELECT COUNT(*)
FROM dbo.Posts a
WHERE a.ParentId = p.Id
AND a.Score > 0) = 0
AND CommunityOwnedDate IS NULL
AND ClosedDate IS NULL
AND ParentId IS NULL
AND AcceptedAnswerId IS NULL
SELECT TOP 2000 unanswered.Id AS [Post Link] ,
(10.0 + us.Reputation
/ 200.0 + p.Score * 100)
JOIN dbo.Posts p ON unanswered.Id = p.Id
JOIN PostTags pt ON pt.PostId = unanswered.Id
JOIN Users u ON u.Id = p.OwnerUserId
GROUP BY unanswered.Id, u.Reputation, p.Score
ORDER BY Weight DESC ;
The CTE, lines 1 – 12, effectively creates a temporary view that we can use throughout the rest of the query. You can also think of it in the same way that you’d think of a derived table (a join to a subquery).
CTEs make it possible to simplify code, express complex ideas more concisely, or just write code quickly without having to worry as much about structure. They’re a great feature of the SQL language that many people overlook.