SQL Subquery and CTEs( Common Table Expressions)

What is a subquery?
Subquery is a query nested inside another query statement. It allows the query to be modular which would otherwise require multiple queries to achieve the same result. It runs first and the result is used as input by another query.
When to use it?
It is an essential tool when used in the following scenarios:

• Filtering row data using the results from another query

• Applying aggregation functions such as SUM or AVG dynamically

• Simplify queries by breaking them into smaller, readable and maintainable queries instead of using joins or complex external code.

• Compare data between tables to fetch specific insights

Types of subqueries

Scalar subquery
It returns a single value,such as one row and column. It is commonly used with comparison operators such as >,<, =
Use case: Find books that cause more than average

SELECT title,price
FROM books
WHERE price > (SELECT AVG(price) FROM books);

Multi-row subquery
It returns more than one value, i.e. multiple rows as result. It is often used with operators such as ANY, ALL or IN.
Use case: Books ordered by Customer John

SELECT title, author
FROM books
WHERE book_id IN (
SELECT book_id
FROM orders
WHERE customer_id IN (
SELECT customer_id
FROM customers
WHERE first_name = 'John'
));

Correlated subquery
It is dependent on the outer query for its values, i.e. it references columns from the outer query. It is executed once for each row of the outer query making it slower for larger datasets.
Use case: Customer who placed atleast 1 order of books

SELECT first_name, last_name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id

Table subquery( derived tables)
It returns a complete table. It is commonly used in the from clause.
Use case: Top 3 most expensive books

SELECT title,author,price
FROM (
SELECT title,author,price,
RANK() OVER (ORDER BY price DESC) AS price_rank
FROM books
) AS ranked_books
WHERE price_rank <= 3;

What are Common Table Expressions(CTEs)
CTEs are temporary, named results in SQL that can be referenced in a single statement. It is allowed to simplify complex queries making them reusable, readable and maintainable, and helps work with hierarchical data using recursions. It is distinctly created using the WITH clause.

Types and Use Cases of CTEs

Non-Recursive CTEs
These are used once within a single query to simplify logic. Ideal for breaking down complex joins, aggregations, or temporary calculations.
Use case: Find the list of customers and their total orders

WITH customer_order_count AS (
SELECT
customer_id,
COUNT(order_id) AS total_orders
FROM orders
GROUP BY customer_id)
SELECT
c.first_name,
c.last_name,
co.total_orders
FROM customers c
JOIN customer_order_count co
ON c.customer_id = co.customer_id;

Recursive CTEs
Used when working with hierarchical data (e.g., organization charts, folder structures, family trees). A recursive CTE repeatedly references itself until a termination condition is met.
Use case: Generates a sequence of dates starting from the earliest order date upto 5 days ahead

-- Recursive CTE: Generate sequential dates
WITH RECURSIVE date_sequence AS (
-- Anchor member: start from the earliest order date
SELECT MIN(order_date) AS order_date
FROM orders
UNION ALL
-- Recursive member: add 1 day until 5 days ahead
SELECT order_date + INTERVAL '1 day'
FROM date_sequence
WHERE order_date < (SELECT MIN(order_date) + INTERVAL '5 days' FROM orders)
)
SELECT order_date
FROM date_sequence;

Common Use Cases:

• Breaking complex queries into readable, logical parts.

• Creating temporary result sets reused within a query.

• Traversing hierarchical or self-referential data structures.

• Simplifying code compared to nested subqueries or procedural loops.

Comparison between subqueries and CTEs

Conclusion
Subqueries and CTEs are essential tools for writing efficient and maintainable SQL code. Subqueries shine in simple filtering and dynamic aggregation, while CTEs offer superior readability and flexibility for complex, multi-step, or recursive logic. Choosing between them depends on the problem: use subqueries for straightforward tasks, but prefer CTEs when your query requires clarity, modularity, or repeated logic. Together, they form the foundation for clean, powerful, and scalable SQL development.