Scalar subquery in SELECT in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a scalar subquery in SQL?

A scalar subquery is a subquery that returns exactly one value (one row and one column). It can be used in places where a single value is expected, such as in the SELECT list.

beginner

Can a scalar subquery return multiple rows or columns?

No, a scalar subquery must return exactly one row and one column. Returning more than one row or column causes an error.

intermediate

How is a scalar subquery used in a SELECT statement?

It is placed inside the SELECT clause to compute a value for each row, for example: SELECT name, (SELECT MAX(score) FROM scores WHERE scores.user_id = users.id) AS max_score FROM users;

intermediate

What happens if a scalar subquery returns no rows?

If a scalar subquery returns no rows, it returns NULL in the SELECT statement.

intermediate

Why use a scalar subquery instead of a JOIN?

Scalar subqueries can be simpler to write and understand when you need a single value related to each row, especially for aggregation or lookup. JOINs are better for multiple related rows.

What does a scalar subquery return?

A Exactly one value (one row, one column)

B Multiple rows and columns

C Only multiple rows

D Only multiple columns

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

Where can you use a scalar subquery in SQL?

A Only in the GROUP BY clause

B Only in the WHERE clause

C Only in the FROM clause

D In the SELECT clause

Scalar subqueries are often used in the SELECT clause to compute a single value per row.

What happens if a scalar subquery returns no rows?

A It returns zero

B It returns NULL

C It causes an error

D It returns an empty string

If no rows are returned, the scalar subquery returns NULL.

What error occurs if a scalar subquery returns more than one row?

A Division by zero error

B Syntax error

C Subquery returns more than one row error

D No error, it works fine

Scalar subqueries must return one row; more than one row causes an error.

Which is a good use case for scalar subqueries?

A Getting a single aggregated value per row

B Joining multiple tables with many rows

C Updating multiple rows at once

D Creating indexes

Scalar subqueries are useful for getting one value per row, like an aggregate.

Explain what a scalar subquery is and how it is used in a SELECT statement.

Think about a small query inside the SELECT that gives one value per row.

What errors or results can happen if a scalar subquery returns zero or multiple rows?

Consider what SQL expects from a scalar subquery's output.

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