-- Scalar subquery returning multiple rows

SELECT

name,

salary,

(SELECT salary FROM employees) AS avg_salary -- Returns multiple rows!

FROM employees;

-- Error: Subquery returns more than one row

-- Use aggregate function to return single value

SELECT

name,

salary,

(SELECT AVG(salary) FROM employees) AS avg_salary -- Returns one value

FROM employees;

-- AVG() ensures single value returned

Scalar subquery must return exactly one value. Without aggregate function, SELECT salary returns multiple rows (one per employee). Use AVG(), MAX(), MIN(), COUNT(), or SUM() to get single value. Or add WHERE clause to ensure single row: (SELECT salary FROM employees WHERE id = 1).

Scalar subqueries must return single value. Use aggregates or ensure single row with WHERE.

Critical

Subquery returns more than one row

graph TB

A["Wrong:<br/>(SELECT salary)"] --> B["Returns multiple rows<br/>ERROR"]

C["Correct:<br/>(SELECT AVG(salary))"] --> D["Returns one value<br/>75000"]

style B fill:#ffcccc

style D fill:#90EE90

-- Missing alias for derived table

SELECT

department,

AVG(salary) AS avg_salary

FROM (

SELECT * FROM employees WHERE hire_date > '2023-01-01'

) -- Missing alias!

GROUP BY department;

-- Syntax error: Every derived table must have its own alias

-- Add alias to derived table

SELECT

department,

AVG(salary) AS avg_salary

FROM (

SELECT * FROM employees WHERE hire_date > '2023-01-01'

) AS recent_hires -- Alias required

GROUP BY department;