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.

Practice

1. What does a scalar subquery return in MySQL?

easy

A. A list of tables

B. A single value that can be used in another query

C. Only column names

D. Multiple rows and columns

Solution

1. Step 1: Understand scalar subquery definition

   A scalar subquery returns exactly one value, not multiple rows or columns.

2. Step 2: Identify usage in queries

   This single value can be used inside another query, like in WHERE or SELECT clauses.

3. Final Answer:

   A single value that can be used in another query -> Option B

4. Quick Check:

   Scalar subquery = single value [OK]

Hint: Scalar subqueries always return one value only [OK]

Common Mistakes:

• Thinking scalar subqueries return multiple rows
• Confusing scalar subqueries with table subqueries
• Assuming scalar subqueries return column names only

2. Which of the following is the correct syntax for a scalar subquery in a SELECT statement?

easy

A. SELECT name, MAX(score) FROM scores FROM students;

B. SELECT name, MAX(score) AS max_score WHERE score > 50;

C. SELECT name, (SELECT MAX(score) FROM scores) AS max_score FROM students;

D. SELECT name, (MAX(score) FROM scores) AS max_score FROM students;

Solution

1. Step 1: Analyze correct scalar subquery syntax

   The scalar subquery must be enclosed in parentheses and return a single value, as in (SELECT MAX(score) FROM scores).

2. Step 2: Check placement in SELECT clause

   SELECT name, (SELECT MAX(score) FROM scores) AS max_score FROM students; correctly places the scalar subquery inside the SELECT list with an alias.

3. Final Answer:

   SELECT name, (SELECT MAX(score) FROM scores) AS max_score FROM students; -> Option C

4. Quick Check:

   Scalar subquery syntax = SELECT name, (SELECT MAX(score) FROM scores) AS max_score FROM students; [OK]

Hint: Scalar subqueries must be in parentheses inside SELECT or WHERE [OK]

Common Mistakes:

• Missing parentheses around subquery
• Incorrect FROM clause placement
• Using aggregate functions without subquery

3. Given the tables:
students(id, name)
scores(student_id, score)
What will this query return?

SELECT name, (SELECT AVG(score) FROM scores WHERE student_id = students.id) AS avg_score FROM students;

medium

A. Each student's name with their average score

B. The overall average score for all students repeated for each name

C. Syntax error due to subquery

D. List of student names only without scores

Solution

1. Step 1: Understand the subquery correlation

   The subquery calculates the average score for each student by matching student_id with students.id.

2. Step 2: Result of the main query

   The main query returns each student's name along with their individual average score from the subquery.

3. Final Answer:

   Each student's name with their average score -> Option A

4. Quick Check:

   Scalar subquery returns per-student average [OK]

Hint: Correlated scalar subqueries return values per row [OK]

Common Mistakes:

• Assuming subquery returns overall average for all rows
• Confusing correlated and uncorrelated subqueries
• Expecting syntax error when none exists

4. Identify the error in this query using a scalar subquery:

SELECT name, (SELECT score FROM scores) AS score FROM students;

medium

A. No error, query runs fine

B. Missing alias for subquery

C. Incorrect table name in subquery

D. Subquery returns multiple rows causing an error

Solution

1. Step 1: Analyze the subquery result

   The subquery SELECT score FROM scores returns multiple rows because no WHERE clause limits it.

2. Step 2: Understand scalar subquery requirement

   Scalar subqueries must return exactly one value; multiple rows cause an error.

3. Final Answer:

   Subquery returns multiple rows causing an error -> Option D

4. Quick Check:

   Scalar subquery must return one value [OK]

Hint: Scalar subqueries must return one value, not many [OK]

Common Mistakes:

• Ignoring multiple rows returned by subquery
• Assuming alias is mandatory for subquery
• Thinking query runs without error

5. You want to find students whose score is above the average score of all students. Which query correctly uses a scalar subquery to achieve this?

hard

A. SELECT name FROM students WHERE score > (SELECT AVG(score) FROM scores);

B. SELECT name FROM students WHERE score > SELECT AVG(score) FROM scores;

C. SELECT name FROM students WHERE (SELECT AVG(score) FROM scores) < (SELECT score FROM scores WHERE student_id = students.id);

D. SELECT name FROM students WHERE score > (SELECT score FROM scores);

Solution

1. Step 1: Understand the goal

   We want students whose individual score is greater than the average score of all students.

2. Step 2: Analyze each option

   SELECT name FROM students WHERE score > (SELECT AVG(score) FROM scores); correctly compares each student's score with the average score using a scalar subquery.

3. Final Answer:

   SELECT name FROM students WHERE score > (SELECT AVG(score) FROM scores); -> Option A

4. Quick Check:

   Scalar subquery compares average with individual score [OK]

Hint: Use scalar subquery for average, correlate for individual score [OK]

Common Mistakes:

• Missing parentheses around subqueries
• Using subquery without correlation for individual scores
• Syntax errors from missing SELECT in WHERE