What happens when a subquery used with = returns more than one row?
SHORT ANSWER
The database raises an error such as "subquery returns more than 1 row", because = expects a single value. Use IN, ANY or ALL for multiple values, or make the subquery return one row with an aggregate or LIMIT.
Explain it simply
"Is your mark equal to the topper's mark?" makes sense if there is one topper. If three students tie, the question "equal to which one?" has no single answer, and the database stops with an error.
Example
sql
SELECT name FROM employees
WHERE salary = (SELECT salary FROM employees WHERE dept_id = 2);
-- error if dept 2 has more than one employeeFixes, depending on the intent:
sql
-- any of the salaries in dept 2
SELECT name FROM employees
WHERE salary IN (SELECT salary FROM employees WHERE dept_id = 2);
-- higher than every salary in dept 2
SELECT name FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE dept_id = 2);
-- force a single value
SELECT name FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees WHERE dept_id = 2);If the subquery returns zero rows, a scalar subquery gives NULL and the comparison is not true, so no rows match. Only more than one row causes the error.
What to say in the interview
"A scalar subquery with =, <, > must return at most one row. For several rows I use IN, ANY or ALL, or an aggregate to make it single-valued."
Common mistakes
- Using LIMIT 1 to silence the error without checking which row is actually wanted.