SQL Data Analytics • Lesson 16

Subqueries & CTEs

Learn how to use a query inside another query and how Common Table Expressions make complex analytical SQL easier to understand.

Your progress0%

0 of 8 concepts completed

Complete the activity and related MCQ to unlock the next concept.

Concept 1SUBQUERY

What Is a Subquery?

A subquery is a SQL query written inside another query. The inner query produces a value or a list that the outer query uses to filter, compare or join.

Interactive Data

Customers & Orders

Two related tables — enough to demonstrate scalar, IN, correlated subqueries and CTEs.

CUSTOMERS

ORDERS

₹4,867

AVG ORDER

Customers

customer_id	name	city
101	Rahul	Delhi
102	Priya	Mumbai
103	Amit	Pune
104	Neha	Delhi
105	Arjun	Jaipur

Orders

order_id	customer_id	amount
5001	101	₹2,500
5002	102	₹7,000
5003	101	₹4,500
5004	103	₹3,200
5005	104	₹9,000
5006	102	₹3,000

Per-customer sales totals

name	city	total_sales	> 8000?
Rahul	Delhi	₹7,000	no
Priya	Mumbai	₹10,000	yes
Amit	Pune	₹3,200	no
Neha	Delhi	₹9,000	yes

Learn by Seeing

Two nested queries

Outer Query
     ↓ uses result of
Inner Query

The inner query runs first, produces a result, and the outer query uses that result.

Two nested queries — click to explore each layer:

Inner query

Runs first. Produces a value or a list that the outer query can use.

Interactive Activity

What is a subquery?

Hint: It lives inside parentheses and feeds data to the outer query.

SQL Analyst Mindset

Break big problems into small queries

Analysts rarely write one giant query. They build analysis in layers — a subquery for a scalar value, a CTE for a named intermediate result — and then combine those layers into the final answer.

Decompose

What is the inner step?

Name it

Wrap it in a CTE for readability.

Combine

Join or filter against the inner result.

Answer

Only the final SELECT returns rows.

Frequently Asked Questions

Subqueries & CTEs — FAQs

Subquery vs CTE — when to use which?

Use a subquery for a short, one-off inner value. Use a CTE when the logic has multiple steps or you want a named, reusable block.

How many values can a scalar subquery return?

Exactly one — one row and one column. Anything else causes a runtime error in most databases.

Are correlated subqueries slow?

Often slower than equivalent joins because they run once per outer row. Many can be rewritten as joins for performance.

Can I use multiple CTEs in one query?

Yes. Use WITH cte1 AS (...), cte2 AS (...) to chain as many named steps as you need.

Lesson Summary

What you learned

Scalar subquery

Returns a single value the outer query can compare against.

IN subquery

Returns a list the outer query matches against.

Correlated subquery

References outer columns — evaluated per row.

CTE (WITH)

Named temporary result for readable, multi-step queries.