INTRODUCTION
As you advance beyond basic SQL queries, you’ll encounter scenarios where a single SELECT isn’t enough. To solve more complex problems, you need ways to structure queries in layers or break them into clear steps. Two powerful techniques make this possible; subqueries and Common Table Expressions (CTEs).
This article explains both concepts from first principles, demonstrates them using a real retail dataset containing Customers, Products, Sales, and Inventory tables, and provides a clear comparison so you always know which tool to reach for.
WHAT IS A SUBQUERY?
A subquery is an SQL query written inside another query. The outer query uses the result of the inner query as part of its logic whether as a filter, a value, or a derived table. Subqueries are always enclosed in parentheses ( ) and can appear in several parts of an outer query: inside WHERE, FROM, SELECT, or HAVING clauses.
See below basic syntax of a subquery
DIFFERENT TYPES OF SUBQUERIES
There are four main types of subqueries, classified by where they appear and what they return.
1. Scalar Subquery
This subquery returns exactly one row and one column. Used wherever a single value is expected in a WHERE clause, a SELECT list, or even a HAVING.
The subquery returns one number: 1599.98. The outer query finds which customer's sale matches that value.
2. Table Subquery
A table subquery is also called an inline view. It sits inside the 'FROM' clause and acts like a temporary table. You must give it an alias.
The above photo shows customers who spent more than average customer spending across all customers.
3. Correlated Subquery
A correlated subquery references a column from the outer query, meaning it runs once for every row the outer query processes.
The above table shows products priced higher than the average price within their own category.
4. Subquery with EXISTS / NOT EXISTS
The EXISTS operator checks whether a subquery returns any rows at all. It asks "Does at least one matching row exist?". Returns TRUE or FALSE.
When Should You Use Subqueries?
Use a subquery when:
• You need a single computed value to compare against (scalar subquery).
• You want to pre-filter or pre-aggregate data before the main query runs.
• You need row-by-row comparisons that depend on the outer query context
• You are doing an existence check with EXISTS or NOT EXISTS
• The logic is simple enough to read comfortably within parentheses.
What are CTEs? (Common Table Expressions)
A Common Table Expression (CTE) is a named, temporary result set that you define at the top of a query using the WITH keyword. They allow you to break a complex query into logical, readable steps, transforming an intimidating wall of SQL into something understandable.
See below basic syntax of a CTE.
TYPES OF CTEs
1. Simple CTE
A simple CTE defines one named result that is used once. Best for extracting one logical step out of a complex query.
Example; products that sold more than the average quantity sold.
2. Multiple / Chained CTE.
This is used to define multiple CTEs in a single WITH clause, separated by commas before the main query. Each CTE can reference the ones defined before it creating a clean, step-by-step sequence.
3. Recursive CTE
References itself to handle hierarchical or iterative data like org charts, category trees, or sequences. It has two parts: an anchor (base case) and a recursive member.
When to Use CTEs
- The logic has multiple distinct steps that build on each other.
- You need to reuse the same intermediate result more than once.
- You want your code to be easily reviewed, maintained, or explained.
- You are working with window functions that produce derived results.
- You need recursive queries (hierarchies, trees, graphs).
- You are building reports or analytical queries that others will read.
SUBQUERIES VS CTEs
The below tables shows a compariosn of the two
An example of a query that can be expressed using either a subquery or a CTE.
Question: Which customers spent more than the average spending across all customers
Approach A (Using a Subquery).
Approach B (Using a CTE).
Key Observation
Both queries produce identical results however the CTE version is significantly easier to understand and maintain.
The subquery version nests two levels deep inside a HAVING clause — it takes a moment to parse.
The CTE version reads top-to-bottom in plain steps: (1) calculate totals, (2) calculate average, (3) filter above average.
CONCLUSION
Subqueries and Common Table Expressions (CTEs) are both essential tools for structuring complex SQL logic. Subqueries let you embed one query inside another, while CTEs provide a clearer, step‑by‑step way to build queries that are easier to read and maintain. Each has its place: subqueries excel in quick, inline checks, and CTEs shine when queries grow more layered or need to be reused. By understanding their differences and strengths, you’ll be able to choose the right approach for readability, performance, and long‑term maintainability—making your SQL both powerful and elegant.
