Subqueries for temp results

Concepts covered: Scalar Subquery

Before CTEs became standard, all intermediate results had to be written inline as nested queries: a query inside another query's WHERE clause or FROM clause. Subqueries still appear constantly in production SQL and in interview questions, and they produce exactly the same results as an equivalent CTE. Understanding subqueries means you can read any SQL in the wild, write the embedded style when a full CTE block would be overkill, and recognize when a nested query is slowing things down enough to warrant refactoring.

Where subqueries go

Subqueries appear in 3 positions: inside WHERE, inside FROM, and inside SELECT.

Table subquery

SELECT *
FROM orders
WHERE total > (SELECT AVG(total) FROM orders)

orders
order_id	customer	total
O1	Alice	200
O2	Bob	350
O3	Carol	180
O4	Dave	400
O5	Eve	170

A table subquery replaces a table name in the FROM clause with a complete SELECT statement. The inner query runs first to produce an intermediate result, then the outer query filters from that result.

Scalar subquery

A scalar subquery returns a single value that can be used in the SELECT list or WHERE clause.

SELECT id, (SELECT val FROM config WHERE config_key = 'default') AS default_val FROM logs;

config
config_key	val
default	standard
timeout	30
retries	3

logs
id	message	level
101	User login	info
102	Page viewed	info
103	File uploaded	info

Result
id	default_val
101	standard
102	standard
103	standard

The subquery retrieves a single value. Each row in the outer query shows the same default name.

Multi-row subquery

When a subquery returns multiple rows, use IN to check if a value matches any result.

SELECT id FROM orders WHERE user_id IN (SELECT id FROM users WHERE active = 1);

users
id	name	active
1	Ana	1
2	Ben	0
3	Chloe	1
4	Dev	1
5	Elena	0

orders
id	user_id	total
1001	1	25
1002	2	40
1005	3	15.5
1006	5	60
1008	4	32
1010	2	18
1012	1	22.5

Result
id
1001
1005
1008
1012

The subquery lists all active user IDs. The outer query selects orders linked to those users.

Practice: subquery filter

Example: devices over an error threshold

Suppose you need to monitor which devices have many error events. First, count the number of errors per device, then select devices exceeding a certain threshold.

Subqueries allow you to separate counting from selection, so each step is easy to check. You can reuse this approach for any dataset where part of the data must be computed before filtering.

Do

Use subqueries for one-off filtering or transformations
Test subquery performance separately before nesting
Keep subqueries simple and focused on a single task

Avoid

Avoid deeply nested subqueries; use CTEs for readability
Don't use subqueries when a simple join would suffice

Correlation decides how a subquery runs

The database processes a subquery differently depending on whether it references the outer query, and that changes how fast the query runs.

Correlated vs. uncorrelated

A non-correlated subquery runs once and shares its result with every row in the outer query. A correlated subquery references the outer query's current row, so it must re-execute for each row the outer query processes. On a table with 1 million rows, a correlated subquery runs 1 million times. This is why correlated subqueries can be slow and are often worth rewriting as a JOIN or CTE.

Inner First: Subqueries execute before the outer query
Performance: Large or repeated subqueries can slow things down
Temporary: Datasets exist only during query execution
Best Use: Small, one-off computations and filtering

About this lesson

This section is part of the Query Structure: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.