Common Table Expression (CTE) and subquery are very useful when we need to use a temporary table for further analysis in a subsequent query.
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.
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.
We know you work best with examples, so let's follow through with the explanation below.
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.
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 .
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.
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.
The term CTE refers to a subquery that references itself. There are essentially 3 elements:
In the following example, we are querying for countries where the cost in the has exceeded $50,000.
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.
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.
If you're excited to explore CTEs and subqueries, practice them with DataLemur's questions!