Scalar subqueries in MySQL - Step-by-Step Execution

Concept Flow - Scalar subqueries

Start Outer Query

↓

Encounter Scalar Subquery

↓

Execute Subquery

↓

Return Single Value

↓

Use Value in Outer Query

↓

Complete Outer Query Execution

The outer query pauses to run the scalar subquery, which returns one value, then uses that value to continue.

Execution Sample

MySQL

SELECT name, (SELECT MAX(score) FROM scores WHERE scores.player_id = players.id) AS max_score
FROM players;

For each player, find their name and their highest score using a scalar subquery.

Execution Table

Step	Outer Query Row	Subquery Execution	Subquery Result	Outer Query Output
1	players.id=1, name='Alice'	SELECT MAX(score) FROM scores WHERE player_id=1	95	('Alice', 95)
2	players.id=2, name='Bob'	SELECT MAX(score) FROM scores WHERE player_id=2	88	('Bob', 88)
3	players.id=3, name='Carol'	SELECT MAX(score) FROM scores WHERE player_id=3	NULL	('Carol', NULL)
4	No more rows	No subquery	No subquery	Query ends

💡 All player rows processed; scalar subquery executed once per row; query completes.

Variable Tracker

Variable	Start	After 1	After 2	After 3	Final
players.id	N/A	1	2	3	N/A
players.name	N/A	Alice	Bob	Carol	N/A
subquery MAX(score)	N/A	95	88	NULL	N/A
outer query output row	N/A	('Alice', 95)	('Bob', 88)	('Carol', NULL)	N/A

Key Moments - 2 Insights

Why does the scalar subquery run once for each row in the outer query?

Because the subquery depends on the current outer row's player_id, it must run separately for each player to get their specific max score, as shown in execution_table rows 1-3.

What happens if the subquery returns no rows, like for Carol?

The subquery returns NULL, which is used in the outer query output. This is shown in execution_table row 3 where subquery result is NULL.

Visual Quiz - 3 Questions

Look at the execution_table, what is the subquery result when players.id = 2?

A 88

B 95

C NULL

D No result

💡 Hint

Check execution_table row 2 under 'Subquery Result' column.

At which step does the outer query finish processing all players?

A Step 3

B Step 4

C Step 2

D Step 1

💡 Hint

Look at execution_table row 4 where it says 'No more rows' and 'Query ends'.

If the subquery returned multiple values instead of one, what would happen?

A The query would run normally

B The scalar subquery would return the first value only

C An error would occur because scalar subqueries must return one value

D The outer query would ignore the subquery

💡 Hint

Scalar subqueries must return exactly one value; multiple values cause errors.

Concept Snapshot

Scalar subqueries return a single value used inside an outer query.
They run once per outer row if correlated.
Syntax: (SELECT single_value FROM ... WHERE ...)
Must return exactly one value or NULL.
Used in SELECT, WHERE, or other clauses.

Full Transcript

Scalar subqueries are small queries inside a bigger query that return exactly one value. When the outer query runs, it pauses to run the scalar subquery for each row if the subquery depends on that row. The subquery result is then used in the outer query's output or condition. If the subquery returns no rows, the result is NULL. If it returns more than one value, an error occurs. This example shows a players table and a scores table. For each player, the query finds their highest score using a scalar subquery. The execution table shows each step: the outer query row, the subquery run with the current player_id, the subquery result, and the final output row. This step-by-step helps understand how scalar subqueries work in practice.