The fundamentals of SQL Subqueries and Common Table Expressions (CTEs)

As you work on data with SQL you will encounter situations where you are trying to achieve very specific results depending on the conditions of your query.

For instance given a study group schema, with members table and quiz results table, and you are required to figure out the members who scored above the average for all the tests.

To solve this you will end up with a series of queries, or a complex query, where you need to filter out the members results , find the average score for the tests and finally filter out only the members who scored above the average found.

While this may be achievable for a small dataset or static data, what will you do when you are working with a large dataset with thousands of records or a dynamic dataset with records constantly being added?

Well fear no more, that's where SQL subqueries and Common Table Expressions (CTEs) come to the rescue.

These two methods provide a way to use SQL queries within your main SQL query but they differ in structure, reusability, and capability.

Lets explore them individually.

SUBQUERY

A subquery in SQL is a query nested inside another SQL query, it is written inline within the SELECT, FROM, or WHERE clauses of the main query.

It allows complex filtering, aggregation and data manipulation by using the result of one query inside another and are concise thus used for simple, single-use filters or scalar values.

syntax of a subquery

SELECT Columns
From table
outer query functionality 
operator clause (   SELECT something FROM table
              subquery functionality);

To make sense of this syntax I will refer to the example I had mentioned earlier. We will use the outer query to go through the members test results then use subquery to find the average of the same and finally compare them to get our desired result set.

The operator clause is what determines how our two queries will engage with each other. The operator is significant to note since it has to work with the output of your subquery.

You can use SQL comparison operators or logical operators in instances where you want to filter based on a single calculated value derived from the subquery.

In our case we will use a greater than (>) operator since we want to compare all the members results in the tests to the average test score, which is a single result.

Our SQL becomes:

select 
    score_id,
    member_id,
    score 
from study_group.quiz_scores
where score > (select avg(score) from study_group.quiz_scores)
order by score asc;

The line where score > (select avg(score) from study_group.quiz_scores) is where the magic of subqueries is happening.
We are comparing every score for each member to a dynamically calculated score average.

This ensures that regardless of whatever data manipulation occurs in our tables we are always comparing our scores to the correct average thus no need to figure out the average score beforehand.

We also have another instance where our subquery returns a set of results in the form of a temporary table. In these cases, we have to give the table an alias that will enable us to reference it while performing further queries.

Note: The result is temporary as it only exists while the query is running.

Consider the same study group schema, you are now told to calculate each members average score then show those who have a average score above 70.

We can use a subquery to calculate the average score for each member, this will give us a temporary table having the member_id and their average score, .We can then compare this to our threshold of 70 to solve our problem.

Our SQL query becomes:

select 
    member_id,
    average_member_score
from (select 
            qs.member_id,
            avg(score) as average_member_score
        from study_group.quiz_scores qs 
        group by qs.member_id
        ) as member_averages
where average_member_score > 70;

As you can see we are able to reference the average_member_score in our main query from our subquery whose result is stored in a temporary table we name member_averages.

Subqueries are incredible when you only need a result once in a query as they cannot be reused and have to be duplicated if the same result is needed multiple times in a query. This makes your query harder to read and maintain.

Common Table expressions (CTEs) solve this issue.

CTE (Common Table Expressions)

A CTE defines a temporary result set that you can reference, possibly multiple times within the scope of a SQL statement. CTEs organize code into logical, sequential stages enabling reusability and recursion.

A CTE is used mainly in a SELECT statement.

Syntax of CTE

WITH cte_name AS (
    SELECT column1, column2
    FROM table_name
    condition
)
SELECT * 
FROM cte_name
main query functionality;

A CTE is defined at the start of the query using the WITH keyword followed by the name for the CTE.

The CTE name is followed by a set of parenthesis () that contain the CTEs functionality, closing the CTE, we now write the main query functionality where we are able to pull from the CTE as many times as we need; since CTEs are reusable within a query statement.

This makes complex, multi-step logic significantly easier to read and maintain.

For example we can redo the question of finding the members who scored above the score average using a CTE as follows:

with group_avg as ( -- namimg the CTE
    select avg(score) as avg_score      -- CTE functionality
    from study_group.quiz_scores q
) 
select score_id, member_id,score 
from study_group.quiz_scores q 
cross join group_avg                    -- calling CTE using a cross join
where q.score > avg_score
order by score asc; 

In this case we create a CTE named group_avg and we calculate the score average inside the CTE. We can then use a cross join to call the CTE and compare the scores to the average score found in the CTE.

While the query achieves the exact same functionality as when using a subquery, our query is much more readable and easier to maintain incase changes are made.

We can also write multiple CTEs in sequence to handle complex queries, in such cases the CTEs are separated by a comma (,).

syntax for multiple CTEs

WITH cte_1 AS (
    SELECT columns
    FROM table_1
    cte_1 functionality
), cte_2 AS (
    SELECT columns
    FROM table_2
    cte_2 functionality
)
SELECT columns
FROM cte_1 ct1
INNER JOIN cte_2 ct2
ON ct1.column_a = ct2.column;

For instance when asked to find members whose personal average is higher than the group average we can write the following query:

with group_avg as  ( 
    select avg(score) as group_avg_score
    from study_group.quiz_scores
),
member_avg as (
    select member_id, avg(score) as member_avg_score
    from study_group.quiz_scores
    group by member_id
)
select m.member_id, m.member_avg_score 
from member_avg m
cross join group_avg g
where m.member_avg_score > g.group_avg_score
order by m.member_avg_score;

Limitations of CTEs

While useful, CTEs come with a few practical constraints.

• Temporary: A CTE works only while the query runs, then it disappears.
• Performance: On very large data, CTEs can be slower if reused many times.

Summary

Feature	CTE	Subquery
Definition	WITH clause (before main query)	Nested inside SELECT, FROM, WHERE
Readability	High (modular, named steps)	Low when deeply nested
Reusability	Can be referenced multiple times	Used once per inline location
Recursion	Supported	Not supported
Best Use Case	Complex logic, recursion, repeated logic	Simple filters, scalar values, single-use