CTE vs. Subquery
Common Table Expression (CTE) and subquery are very useful when we need to use a temporary table for further analysis in a subsequent query.
What is a CTE?
A Common Table Expression (CTE) is a query created using a statement. We can simply use its output as a temporary table, just like a subquery. It only exists during the execution of that query.
Syntax
What is a Subquery?
As its name suggests, a subquery is a query inside a query; it's a nested query. Occasionally, it's referred to as an** inner query**.
To use a subquery, we simply add parentheses around the inner query and put it inside the outer query. The output created by a subquery acts as a temporary table which can be used within the query.
Syntax
Example: DataLemur Duplicate Job Listing
We know you work best with examples, so let's follow through with the explanation below.
Using CTE
In the query below, we declared the CTE as and put it in a statement before the main query. Then, the CTE is reused in the main query in the clause to perform a distinct count of the companies with more than 1 job.
Using Subquery
In the following query, we created the inner subquery in the outer query's clause, wrap it with a pair of parentheses and declared it as .
Advantages of Using CTE
Advantage #1: CTE is reusable
CTE can be reused multiple times in a query. Instead of declaring the query every time you need to use it, you declare it once at the beginning, give it a meaningful name and simply refer to the CTE name in the subsequent query.
Example
Advantage #2: CTE is more readable
CTE is declared at the top of the query which is easier for users to interpret by following the query's logic from top to bottom as compared to the subquery's nested fashion.
Breaking the query into smaller pieces using CTE with meaningful names (i.e. , ) also makes the entire query easily understandable.
Advantage #3: CTEs can be recursive
The term CTE refers to a subquery that references itself. There are essentially 3 elements:
- Non-recursive query (AKA the base query or anchor),
- Recursive query, and
- Termination condition
Advantages of Using Subquery
Advantage #1: Subqueries can be used in the WHERE clause
In the following example, we are querying for countries where the cost in the has exceeded $50,000.
Advantage #2: Subquery can act as a column in the SELECT clause
In the following example, we are creating a new column by using a subquery to compute the average cost.
The only limitation is that the subquery must return only one value.
Example
Advantage #3: Subquery can be used with Correlated Subquery
A correlated subquery is an inner subquery that uses values from the outer query. Because the inner subquery may be evaluated once for each row processed by the outer query, the performance can be slow.
Example
If you're excited to explore CTEs and subqueries, practice them with DataLemur's questions!