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.

Practice

1. What does a scalar subquery return in PostgreSQL?

easy

A. A single value used inside another query

B. Multiple rows and columns

C. Only table names

D. A list of table columns

Solution

1. Step 1: Understand scalar subquery definition

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

2. Step 2: Compare options with definition

   Only A single value used inside another query describes a single value used inside another query, matching the scalar subquery concept.

3. Final Answer:

   A single value used inside another query -> Option A

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 names
• Assuming scalar subqueries return lists

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

easy

A. SELECT (SELECT MAX(price) FROM products) AS max_price;

B. SELECT MAX(price) FROM products WHERE price = (SELECT);

C. SELECT MAX(price) FROM products SELECT price;

D. SELECT MAX(price) FROM (SELECT price FROM products);

Solution

1. Step 1: Identify correct scalar subquery syntax

   The scalar subquery must be enclosed in parentheses and return a single value, used inside the main SELECT.

2. Step 2: Check each option

   SELECT (SELECT MAX(price) FROM products) AS max_price; correctly uses (SELECT MAX(price) FROM products) as a scalar subquery. Options A, B, and D have syntax errors or misuse subqueries.

3. Final Answer:

   SELECT (SELECT MAX(price) FROM products) AS max_price; -> Option A

4. Quick Check:

   Scalar subquery syntax = SELECT (SELECT MAX(price) FROM products) AS max_price; [OK]

Hint: Scalar subqueries are enclosed in parentheses inside SELECT [OK]

Common Mistakes:

• Omitting parentheses around subquery
• Using incomplete or invalid subquery syntax
• Confusing subqueries with JOINs

3. Given the tables:
products(id, name, price)
and
sales(product_id, quantity)
What will this query return?

SELECT name, (SELECT SUM(quantity) FROM sales WHERE product_id = products.id) AS total_sold FROM products;

medium

A. Syntax error due to subquery

B. Only product names without sales data

C. Sum of all sales quantities for all products

D. Each product's name with total quantity sold

Solution

1. Step 1: Understand the subquery role

   The subquery sums quantity from sales where product_id matches the current product's id, returning total sold per product.

2. Step 2: Analyze the main query output

   The main query selects product name and the scalar subquery result as total_sold for each product row.

3. Final Answer:

   Each product's name with total quantity sold -> Option D

4. Quick Check:

   Scalar subquery sums sales per product = Each product's name with total quantity sold [OK]

Hint: Scalar subquery calculates per-row related value [OK]

Common Mistakes:

• Thinking subquery sums all sales once
• Ignoring WHERE clause linking tables
• Assuming syntax error without checking carefully

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

SELECT name, (SELECT price FROM products) AS product_price FROM products;

medium

A. Missing alias for subquery

B. Incorrect table name in subquery

C. Subquery returns multiple rows causing error

D. No error, query runs fine

Solution

1. Step 1: Check subquery result cardinality

   The subquery SELECT price FROM products returns multiple prices (rows), not a single value.

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 error -> Option C

4. Quick Check:

   Scalar subquery must return one value [OK]

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

Common Mistakes:

• Ignoring multiple rows returned by subquery
• Assuming alias fixes multiple rows error
• Confusing syntax error with runtime error

5. You want to list each product's name and its price difference from the average price of all products. Which query correctly uses a scalar subquery to achieve this?

hard

A. SELECT name, price - AVG(price) FROM products;

B. SELECT name, price - (SELECT AVG(price) FROM products) AS price_diff FROM products;

C. SELECT name, price - (SELECT price FROM products) AS price_diff FROM products;

D. SELECT name, price - (SELECT SUM(price) FROM products) AS price_diff FROM products;

Solution

1. Step 1: Understand the goal

   We want each product's price minus the average price of all products.

2. Step 2: Analyze each option

   SELECT name, price - (SELECT AVG(price) FROM products) AS price_diff FROM products; correctly uses a scalar subquery to get AVG(price) once and subtracts it from each product's price. SELECT name, price - AVG(price) FROM products; lacks GROUP BY and will error. SELECT name, price - (SELECT price FROM products) AS price_diff FROM products; returns multiple prices, causing error. SELECT name, price - (SELECT SUM(price) FROM products) AS price_diff FROM products; subtracts sum, not average, which is incorrect.

3. Final Answer:

   SELECT name, price - (SELECT AVG(price) FROM products) AS price_diff FROM products; -> Option B

4. Quick Check:

   Scalar subquery for AVG(price) = SELECT name, price - (SELECT AVG(price) FROM products) AS price_diff FROM products; [OK]

Hint: Use scalar subquery for single aggregate value [OK]

Common Mistakes:

• Using aggregate without GROUP BY causing error
• Subquery returning multiple rows instead of one
• Confusing SUM with AVG in calculation