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.

Practice

1. What does a scalar subquery in the SELECT clause return?

easy

A. Only column names

B. Multiple rows and columns

C. A single value (one row, one column)

D. Only table names

Solution

1. Step 1: Understand scalar subquery definition

   A scalar subquery returns exactly one value, meaning one row and one column.

2. Step 2: Compare with other subquery types

   Unlike table subqueries, scalar subqueries cannot return multiple rows or columns.

3. Final Answer:

   A single value (one row, one column) -> Option C

4. Quick Check:

   Scalar subquery = single value [OK]

Hint: Scalar subquery returns one value only, not a table [OK]

Common Mistakes:

• Thinking scalar subquery returns multiple rows
• Confusing scalar subquery with table subquery
• Assuming scalar subquery returns column names only

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

easy

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

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

C. SELECT name, 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: Identify correct scalar subquery syntax

   The scalar subquery must be enclosed in parentheses and used inside the SELECT clause.

2. Step 2: Check each option

   SELECT name, (SELECT MAX(score) FROM scores) AS max_score FROM students; correctly uses parentheses around the subquery. Others miss parentheses or have wrong placement.

3. Final Answer:

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

4. Quick Check:

   Scalar subquery syntax = parentheses [OK]

Hint: Always put scalar subquery inside parentheses in SELECT [OK]

Common Mistakes:

• Omitting parentheses around subquery
• Placing SELECT keyword incorrectly
• Using aggregate functions without subquery

3. Given tables employees(id, name, dept_id) and departments(id, dept_name), what is the output of this query?

SELECT name, (SELECT dept_name FROM departments WHERE id = employees.dept_id) AS department FROM employees ORDER BY name;

medium

A. Syntax error due to subquery

B. [{"name": "Alice", "department": null}, {"name": "Bob", "department": null}]

C. [{"name": "Alice", "department": "IT"}, {"name": "Bob", "department": "HR"}]

D. [{"name": "Alice", "department": "HR"}, {"name": "Bob", "department": "IT"}]

Solution

1. Step 1: Understand query logic

   For each employee, the scalar subquery fetches the department name matching their dept_id.

2. Step 2: Match employees to departments

   Alice's dept_id matches HR, Bob's matches IT, so the output shows correct department names.

3. Final Answer:

   [{"name": "Alice", "department": "HR"}, {"name": "Bob", "department": "IT"}] -> Option D

4. Quick Check:

   Scalar subquery returns matching department name per employee [OK]

Hint: Scalar subquery returns one value per row, matching join logic [OK]

Common Mistakes:

• Assuming subquery returns multiple rows causing error
• Mixing up department names for employees
• Expecting nulls when matching keys exist

4. Identify the error in this query:

SELECT name, (SELECT dept_name FROM departments WHERE id = employees.dept_id) AS department FROM employees WHERE (SELECT COUNT(*) FROM departments) > 0;

medium

A. Scalar subquery in WHERE is valid but inefficient

B. Scalar subquery in WHERE returns multiple rows

C. Missing alias for subquery in SELECT

D. Subquery in WHERE must return a single value

Solution

1. Step 1: Analyze subquery in WHERE clause

   The subquery in WHERE returns COUNT(*), which is a single value, so it's valid.

2. Step 2: Consider efficiency and logic

   Using a scalar subquery in WHERE like this works but is inefficient; better to check existence differently.

3. Final Answer:

   Scalar subquery in WHERE is valid but inefficient -> Option A

4. Quick Check:

   Scalar subquery in WHERE can be valid but watch efficiency [OK]

Hint: Scalar subquery in WHERE must return one value; check efficiency [OK]

Common Mistakes:

• Thinking scalar subquery in WHERE always causes error
• Confusing alias requirement in SELECT with WHERE
• Assuming subquery returns multiple rows here

5. You want to list all products with their category name, but some products have no category assigned (category_id is NULL). Which query correctly uses a scalar subquery in SELECT to show category names or 'Uncategorized' if none?

Options:
A) SELECT product_name, IFNULL((SELECT category_name FROM categories WHERE id = products.category_id), 'Uncategorized') AS category FROM products WHERE category_id IS NOT NULL;
B) SELECT product_name, (SELECT category_name FROM categories WHERE id = products.category_id) OR 'Uncategorized' AS category FROM products;
C) SELECT product_name, (SELECT category_name FROM categories WHERE id = products.category_id) AS category FROM products WHERE category_id IS NOT NULL;
D) SELECT product_name, COALESCE((SELECT category_name FROM categories WHERE id = products.category_id), 'Uncategorized') AS category FROM products;

hard

A. SELECT product_name, COALESCE((SELECT category_name FROM categories WHERE id = products.category_id), 'Uncategorized') AS category FROM products;

B. SELECT product_name, (SELECT category_name FROM categories WHERE id = products.category_id) OR 'Uncategorized' AS category FROM products;

C. SELECT product_name, (SELECT category_name FROM categories WHERE id = products.category_id) AS category FROM products WHERE category_id IS NOT NULL;

D. SELECT product_name, IFNULL((SELECT category_name FROM categories WHERE id = products.category_id), 'Uncategorized') AS category FROM products WHERE category_id IS NOT NULL;

Solution

1. Step 1: Handle NULL category_id with scalar subquery

   Use COALESCE to replace NULL result from subquery with 'Uncategorized'.

2. Step 2: Check each option's correctness

   The query with COALESCE((SELECT category_name FROM categories WHERE id = products.category_id), 'Uncategorized') AS category FROM products; correctly uses COALESCE and includes all products. The query with (SELECT category_name FROM categories WHERE id = products.category_id) OR 'Uncategorized' uses invalid OR syntax. The queries with WHERE category_id IS NOT NULL exclude products with NULL category_id.

3. Final Answer:

   SELECT product_name, COALESCE((SELECT category_name FROM categories WHERE id = products.category_id), 'Uncategorized') AS category FROM products; -> Option A

4. Quick Check:

   Use COALESCE with scalar subquery for NULL handling [OK]

Hint: Use COALESCE to handle NULL from scalar subquery [OK]

Common Mistakes:

• Using OR instead of COALESCE or IFNULL
• Filtering out NULL category_id rows
• Not handling NULL results from subquery