Introduction
Some questions can't be answered with a single, flat SQL query — they require an intermediate step first. Subqueries and CTEs (Common Table Expressions) both solve this by letting one query's result feed into another, but they read and behave differently.
What Are Subqueries?
A subquery is a query nested inside another query, usually inside parentheses. It runs first, and its result is used by the outer query.
Here, the inner query (SELECT AVG(rides_completed) FROM drivers) calculates the average first. The outer query then uses that single number to filter drivers above it. What I understood here: the subquery is evaluated before the outer query can run, since the outer query depends on its result.
Subqueries can also appear in the FROM clause, acting like a temporary table:
What Are CTEs?
A CTE does the same job — precomputing an intermediate result — but is defined upfront using WITH, giving it a name that can be reused.
This produces the exact same result as the nested subquery example above, but the logic reads top-to-bottom instead of inside-out.
What I Learned
I learned that subqueries are queries placed inside another SQL query to help retrieve or filter data before the main query runs. They are useful when I need to use the result of one query to answer another question.
I also learned that CTEs (Common Table Expressions) allow me to create a temporary named result using the "WITH" keyword and then use it in another query. CTEs queries easier to read, understand and organize.
CTEs helps break down complex SQL problems into smaller and more manageable steps.
Top comments (0)