Scalar subqueries in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of scalar subquery in SELECT clause

Consider the following tables:

employees
id | name | department_id
1 | Alice | 10
2 | Bob | 20
3 | Carol | 10

departments
id | name
10 | Sales
20 | Marketing

What is the output of this query?

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

MySQL

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

A [{"name": "Alice", "dept_name": "Sales"}, {"name": "Bob", "dept_name": "Marketing"}, {"name": "Carol", "dept_name": "Sales"}]

B [{"name": "Alice", "dept_name": null}, {"name": "Bob", "dept_name": null}, {"name": "Carol", "dept_name": null}]

C [{"name": "Alice", "dept_name": "Marketing"}, {"name": "Bob", "dept_name": "Sales"}, {"name": "Carol", "dept_name": "Marketing"}]

D [{"name": "Alice", "dept_name": "Sales"}, {"name": "Bob", "dept_name": "Sales"}, {"name": "Carol", "dept_name": "Sales"}]

💡 Hint

Think about how the subquery uses the outer query's department_id to find the matching department name.

The scalar subquery selects the department name where the department id matches the employee's department_id. So Alice and Carol belong to Sales (id 10), Bob belongs to Marketing (id 20).

🧠 Conceptual

intermediate

Understanding scalar subquery behavior

Which of the following statements about scalar subqueries is true?

A A scalar subquery returns a table that can be joined directly in the FROM clause.

B A scalar subquery can return multiple columns but only one row without error.

C A scalar subquery can return multiple rows and columns and still be used in a SELECT clause.

D A scalar subquery must return exactly one row and one column; otherwise, it causes an error.

💡 Hint

Think about what happens if the subquery returns more than one value when used as a single value.

Scalar subqueries must return exactly one value (one row, one column). Returning more causes an error; returning zero rows returns NULL.

📝 Syntax

advanced

Identify the syntax error in scalar subquery usage

Which option contains a syntax error when using a scalar subquery in the WHERE clause?

MySQL

SELECT * FROM employees WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales');

A SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments WHERE name = 'Sales');

B SELECT * FROM employees WHERE department_id = (SELECT id, name FROM departments WHERE name = 'Sales');

C SELECT * FROM employees WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales');

D SELECT * FROM employees WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales' LIMIT 1);

💡 Hint

Remember scalar subqueries must return only one column.

Option B tries to return two columns (id, name) in a scalar subquery used with '=', which expects one value. This causes a syntax error.

❓ optimization

advanced

Optimizing scalar subqueries in SELECT

Given this query:

SELECT name, (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id) AS avg_dept_salary FROM employees e;

Which optimization improves performance without changing the output?

A Rewrite using a JOIN with a GROUP BY to precompute average salaries per department.

B Add DISTINCT inside the scalar subquery to reduce duplicates.

C Replace the scalar subquery with a correlated subquery that uses LIMIT 1.

D Use a scalar subquery that selects salary instead of AVG(salary).

💡 Hint

Think about how many times the subquery runs and how to reduce repeated calculations.

Using a JOIN with GROUP BY computes average salaries once per department, avoiding repeated scalar subquery execution for each employee.

🔧 Debug

expert

Debugging unexpected NULL from scalar subquery

Given these tables:

employees
id | name | department_id
1 | Alice | 10
2 | Bob | 20
3 | Carol | 30

departments
id | name
10 | Sales
20 | Marketing

What is the output of this query?

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

Why does Carol's dept_name show NULL?

A Because the subquery syntax is incorrect and always returns NULL.

B Because the scalar subquery returns multiple rows for Carol causing NULL to be assigned.

C Because there is no department with id 30 in the departments table, so the subquery returns no rows, resulting in NULL.

D Because the employees table has a NULL department_id for Carol.

💡 Hint

Check if the department_id for Carol exists in the departments table.

Carol's department_id is 30, which does not exist in departments, so the scalar subquery returns zero rows, which SQL treats as NULL.