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.


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.


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.


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:

  1. Non-recursive query (AKA the base query or anchor),
  2. Recursive query, and
  3. 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.


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.


If you're excited to explore CTEs and subqueries, practice them with DataLemur's questions!

Test Your Knowledge with DataLemur Questions