Introduction
Understanding how Subqueries & CTEs work and when to use in a query become powerful tools for breaking down complex problems and writing cleaner SQL.
At some point, every SQL query outgrows a single SELECT ... FROM ... WHERE. You need to filter based on the result of another query, or break it step by step. That's where subqueries and CTEs (Common Table Expressions) come in.
They usually solve the same problem but in different approach. In this article, I will walk through what each is, how they differ, and where each one actually fits its place in a query.
What are Subqueries?
A subquery is a query nested inside another query. It is embedded inside another sql statement. It first runs the inner query and its result is used by the outer query as a filter value, a calaculated column.
Subqueries show up in one of three places:
In a WHERE clause, filters rows based on a computed value:
---subquerry
select score_id,member_id,score
from study_group.quiz_scores
where score >(select avg(score)
from study_group.quiz_scores);
This finds students with scores above average mark. The inner query runs once, produces a single number and the outer query compares each row score against the average mark.
In a FROM clause, acting as a temporary, unnamed table:
SELECT *
FROM (
SELECT member_id, AVG(score) AS avg_score
FROM study_group.quiz_scores
GROUP BY member_id
) AS member_scores;
This query calculates the average score for each member, creating a temporary result set that the outer query can treat like a table.
In a SELECT clause, computing a value per row:
SELECT
member_id,
score,
score * 2 AS doubled_score
FROM study_group.quiz_scores;
What are CTEs?
A CTE(Common Table Expression) is a temporary named result set defined at the top of your query using WITH, and referenced later as a table for the rest of your query.
It is temporary ---> it does not get saved anywhere. It exists only while that one query runs.
An example of a CTE:
----CTE----
with group_avg as (
select avg(score) as avg_score
from study_group.quiz_scores
)
select score_id, member_id,score
from study_group.quiz_scores
cross join group_avg
where score > avg_score;
This defines the logic once, give it a name, and reference it below.
This query uses a CTE to calculate the overall average score once, then uses CROSS JOIN to make that value available to every row. TheWHERE clause then filters the results to show only scores that are above the overall average.
Conclusion
Learning how to use subqueries and CTEs has made it easier for me to break down complex SQL queries into smaller, more manageable pieces.
CTEs and subqueries are powerful SQL tools for breaking down complex queries and making data analysis easier to understand. Subqueries are useful when you need to perform a calculation or filtering operation within another query, while CTEs provides a cleaner and more readable way to organize multi-step logic
Top comments (1)
It's interesting how you've highlighted the trade-offs between subqueries and CTEs, especially regarding readability and performance. I've found that while CTEs often enhance clarity in complex queries, subqueries can sometimes be more efficient in specific contexts. An additional tip is to always analyze the execution plan to see which approach suits your use case better. If you're considering enhancements to this area of your project, I’d be glad to explore a paid collaboration to help optimize your SQL queries further. What have been some of your experiences with performance differences between the two?