-- Subquery returns multiple rows - ERROR!

SELECT

department_name,

(SELECT employee_name

FROM employees

WHERE department_id = d.id) AS employee

FROM departments d;

-- Error: "Subquery returns more than one row"

-- If department has 5 employees, subquery returns 5 rows

-- Scalar subquery can only return 1 value

-- Solution 1: Use aggregate to get single value

SELECT

department_name,

(SELECT COUNT(*)

FROM employees

WHERE department_id = d.id) AS employee_count

FROM departments d;

-- Solution 2: Use LIMIT 1 to get just one employee

SELECT

department_name,

(SELECT employee_name

FROM employees

WHERE department_id = d.id

LIMIT 1) AS sample_employee

FROM departments d;

-- Solution 3: Use JOIN if you need all employees

SELECT

d.department_name,

e.employee_name

FROM departments d

LEFT JOIN employees e ON e.department_id = d.id;

Scalar subquery must return exactly one value. If a department has multiple employees, the subquery returns multiple rows and fails. Fix by using aggregate (COUNT, AVG, MAX), adding LIMIT 1 to get any one value, or using JOIN if you need all rows.

Always test your subquery independently. Run it alone to verify it returns single value. Add LIMIT 1 or use aggregate functions to guarantee single result.

Critical

Subquery returns more than one row

graph LR

A["Department: Sales<br/>5 employees"] --> B["Subquery executes<br/>SELECT employee_name<br/>WHERE dept = Sales"]

B --> C["Returns 5 rows:<br/>Alice<br/>Bob<br/>Carol<br/>David<br/>Eve"]

C --> D["ERROR!<br/>Expected 1 value<br/>Got 5 values"]

E["Fix: Use COUNT"] --> F["SELECT COUNT(*)<br/>WHERE dept = Sales"]