Unless you are new to SQL, at some point you have written a query that begins to resemble a puzzle box query, query within query, difficult to read, more difficult to debug. CTEs fix that.
So, What is a CTE?
CTE is an abbreviation that means Common Table Expression. It is simply a means of assigning value to a query in order to be able to employ it later in the same statement like a table.
It is the keyword WITH.
What is the point of using one?
Readability. Your SQL is top down rather than inwards.
Reusability. You need not rewrite the query, you can refer to the same CTE more than once.
Easier debugging. You can even do CTE alone which would check what it is returning.
What is the Difference Between CTEs and Subqueries?
The two are similar in that they allow you to work on a derived result set. But they feel very different to write and read.
The following query expressed in either direction:
Subquery:
There are one thing subqueries that can do but not CTEs.
A correlated sub query is able to reference the outer query. CTEs can't do that.
Here, the e.department_id is the reference that is correlatedand recalculated on a per row basis. CTEs are determined in advance of the main query, and thus can do nothing to do this.
Side-by-Side Comparison
| CTE | Subquery | |
|---|---|---|
| Readability | Reads top to bottom | Nested, harder to follow |
| Reusability | Reference it multiple times | Have to rewrite it each time |
| Recursion | ✅ Supported | ❌ Not possible |
| Correlated queries | ❌ Can't reference outer query | ✅ Can reference outer query |
| Works everywhere | All modern databases | Universal |
Subqueries can appear in 3 different Clauses
In SELECT: adds a computed column. Runs once per row, so can be slow on large data.
In FROM: creates a temporary table. Always give it an alias.
In WHERE: filters based on another query's result.
When to Use Which:
Use a CTE when:
There are several steps to your query.
You want your SQL serving as easy to read and maintain.
You must cite the same finding more than twice.
Use a sub query in circumstances where:
It is a fast and easy filter.
You must have a correlated query (which cites the outer query).
You are filtering in a WHERE or HAVING statement.
Reflection
The CTEs and subqueries were indeed one of the more difficult things I have learned so far. Originally, the syntax was foreign particularly where each segment resides and why. Queries that contained a subquery and nested queries were especially difficult to reason and read. CTEs simplified it because it dawned on me that you are just giving a query a name and calling it a table. The most difficult was not writing the queries but understanding which tool to consult and why. It was beginning to trade sense after I had practiced and had gone through examples. They are a work in progress, but the comprehension is approaching.
Top comments (0)