Scalar subquery in SELECT in SQL - Step-by-Step Execution

Concept Flow - Scalar subquery in SELECT

Start Query

↓

Execute Outer SELECT

↓

For each row in Outer Table

↓

Run Scalar Subquery

↓

Return single value

↓

Replace Subquery with value

↓

Complete Row Result

↓

Repeat for all rows

↓

Return Final Result Set

The query runs the outer SELECT, and for each row, it runs the scalar subquery to get one value, then uses that value in the result.

Execution Sample

SQL

SELECT e.name,
       (SELECT d.name FROM departments d WHERE d.id = e.department_id) AS dept_name
FROM employees e;

For each employee, get their name and the name of their department using a scalar subquery.

Execution Table

Step	Outer Row (e.name, e.department_id)	Scalar Subquery SQL	Scalar Subquery Result	Final Output Row
1	('Alice', 1)	SELECT d.name FROM departments d WHERE d.id = 1	'HR'	('Alice', 'HR')
2	('Bob', 2)	SELECT d.name FROM departments d WHERE d.id = 2	'Finance'	('Bob', 'Finance')
3	('Charlie', 3)	SELECT d.name FROM departments d WHERE d.id = 3	'IT'	('Charlie', 'IT')
4	End of employees	No more rows	No more subqueries	Query complete

💡 All employee rows processed, scalar subquery executed once per row, final result set returned.

Variable Tracker

Variable	Start	After 1	After 2	After 3	Final
e.name	N/A	Alice	Bob	Charlie	End
e.department_id	N/A	1	2	3	End
Scalar Subquery Result	N/A	HR	Finance	IT	End

Key Moments - 3 Insights

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

Because the scalar subquery depends on the current outer row's department_id, it must run separately for each row to get the correct department name (see execution_table rows 1-3).

What happens if the scalar subquery returns more than one value?

A scalar subquery must return exactly one value. If it returns more, the query will error. This example returns one department name per employee, ensuring one value (see scalar subquery result column).

Can the scalar subquery return no rows?

Yes, if no matching department exists, the scalar subquery returns NULL for that row. This is allowed and will show NULL in the output.

Visual Quiz - 3 Questions

Look at the execution_table, what is the scalar subquery result for the second employee?

A 'IT'

B 'HR'

C 'Finance'

D NULL

💡 Hint

Check execution_table row 2 under 'Scalar Subquery Result'

At which step does the query finish processing all employees?

A Step 2

B Step 4

C Step 3

D Step 1

💡 Hint

Look at execution_table row 4 'End of employees' and 'Query complete'

If the scalar subquery returned multiple rows for an employee, what would happen?

A The query would raise an error

B The query would return the first value only

C The query would return NULL for that employee

D The query would ignore the subquery

💡 Hint

Refer to key_moments about scalar subquery returning more than one value

Concept Snapshot

Scalar subquery in SELECT:
- Runs once per outer row
- Returns exactly one value
- Used like a column in SELECT
- Returns NULL if no match
- Errors if multiple values returned

Full Transcript

This example shows how a scalar subquery works inside a SELECT statement. For each employee row, the query runs a subquery to find the department name matching the employee's department_id. The subquery returns one value, which is used as a column in the output. The execution table shows each step: the outer row, the subquery run, the subquery result, and the final output row. The variable tracker shows how variables change per step. Key moments clarify common confusions like why the subquery runs multiple times and what happens if it returns multiple or no rows. The quiz tests understanding of these steps and outcomes.