Scalar subqueries in PostgreSQL - Step-by-Step Execution

Concept Flow - Scalar subqueries

Start Outer Query

↓

Encounter Scalar Subquery

↓

Execute Scalar Subquery

↓

Return Single Value

↓

Use Value in Outer Query

↓

Complete Outer Query

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

Execution Sample

PostgreSQL

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

For each user, find their name and their highest score from the scores table.

Execution Table

Step	Action	Subquery Execution	Value Returned	Outer Query Use
1	Start outer query, first user	Run scalar subquery for user_id=1	MAX(score) = 95	Use 95 as max_score for user 1
2	Output row: name='Alice', max_score=95	N/A	N/A	Row ready for output
3	Next user, user_id=2	Run scalar subquery for user_id=2	MAX(score) = 88	Use 88 as max_score for user 2
4	Output row: name='Bob', max_score=88	N/A	N/A	Row ready for output
5	Next user, user_id=3	Run scalar subquery for user_id=3	MAX(score) = 100	Use 100 as max_score for user 3
6	Output row: name='Carol', max_score=100	N/A	N/A	Row ready for output
7	No more users	N/A	N/A	Query complete

💡 All users processed, scalar subquery executed once per user, query ends

Variable Tracker

Variable	Start	After 1	After 2	After 3	Final
user_id	N/A	1	2	3	N/A
max_score (scalar subquery result)	N/A	95	88	100	N/A

Key Moments - 2 Insights

Why does the scalar subquery run multiple times?

Because it depends on each user_id from the outer query, it runs once per user to get that user's max score (see execution_table rows 1, 3, 5).

What 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. Here, MAX() ensures one value (see execution_table column 'Value Returned').

Visual Quiz - 3 Questions

Look at the execution_table, what is the max_score for user_id=2 at step 3?

A 95

B 88

C 100

D NULL

💡 Hint

Check the 'Value Returned' column at step 3 in execution_table.

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

A Step 4

B Step 6

C Step 7

D Step 1

💡 Hint

Look for the step with 'Query complete' in the 'Outer Query Use' column.

If the scalar subquery returned multiple values for a user, what would happen?

A The query would raise an error.

B The query would return multiple rows for that user.

C The query would return NULL for that user.

D The query would ignore extra values.

💡 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.
Must return exactly one value (one row, one column).
Commonly used for calculations like MAX(), COUNT(), or simple lookups.
Syntax: (SELECT expression FROM table WHERE condition)
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 it processes. The scalar subquery returns one value, which the outer query uses. For example, to find each user's highest score, the outer query lists users, and the scalar subquery finds the max score for that user. This subquery runs once per user. If the scalar subquery returns more than one value, the database gives an error. Scalar subqueries are useful for embedding calculations or lookups inside bigger queries.