A scalar subquery returns exactly one value: one row and one column. It can go anywhere a single value can: in a comparison, in the select list, in HAVING, in ORDER BY.
query.sql
SQL
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
department VARCHAR(50) NOT NULL,
salary DECIMAL(10,2) NOT NULL,
manager_id INT NULL,
hired_on DATE NOT NULL
);
INSERT INTO employees (name, department, salary, manager_id, hired_on) VALUES
('Asha Rao', 'management', 150000.00, NULL, '2020-01-15'),
('Ben Carter', 'engineering', 120000.00, 1, '2021-03-01'),
('Chitra Nair', 'engineering', 95000.00, 2, '2022-07-11'),
('Dev Malhotra', 'engineering', 88000.00, 2, '2023-01-09'),
('Esha Gupta', 'sales', 70000.00, 1, '2021-09-20'),
('Farid Khan', 'sales', 64000.00, 5, '2022-11-02'),
('Gita Menon', 'sales', 64000.00, 5, '2024-02-14'),
('Hari Iyer', 'support', 52000.00, 1, '2023-05-30');Compare each row with a summary
query.sql
SQL
SELECT name, department, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;
Output
OUTPUT
+--------------+-------------+-----------+
| name | department | salary |
+--------------+-------------+-----------+
| Asha Rao | management | 150000.00 |
| Ben Carter | engineering | 120000.00 |
| Chitra Nair | engineering | 95000.00 |
| Dev Malhotra | engineering | 88000.00 |
+--------------+-------------+-----------+
4 rows in setThe average salary is 87,875. Three people earn more.
Show the summary beside each row
query.sql
SQL
SELECT name, salary,
(SELECT ROUND(AVG(salary), 0) FROM employees) AS company_average,
salary - (SELECT ROUND(AVG(salary), 0) FROM employees) AS difference
FROM employees
ORDER BY salary DESC
LIMIT 4;
Output
OUTPUT
+--------------+-----------+-----------------+------------+
| name | salary | company_average | difference |
+--------------+-----------+-----------------+------------+
| Asha Rao | 150000.00 | 87875 | 62125.00 |
| Ben Carter | 120000.00 | 87875 | 32125.00 |
| Chitra Nair | 95000.00 | 87875 | 7125.00 |
| Dev Malhotra | 88000.00 | 87875 | 125.00 |
+--------------+-----------+-----------------+------------+
4 rows in setAn uncorrelated scalar subquery (one that does not use the outer row) is evaluated once, and its result is reused for every row. That is cheap.
Percent of total
query.sql
SQL
SELECT name, salary, ROUND(100 * salary / (SELECT SUM(salary) FROM employees), 1) AS percent_of_payroll
FROM employees
ORDER BY salary DESC
LIMIT 3;
Output
OUTPUT
+-------------+-----------+--------------------+
| name | salary | percent_of_payroll |
+-------------+-----------+--------------------+
| Asha Rao | 150000.00 | 21.3 |
| Ben Carter | 120000.00 | 17.1 |
| Chitra Nair | 95000.00 | 13.5 |
+-------------+-----------+--------------------+
3 rows in setThe highest and lowest
query.sql
SQL
SELECT name, salary FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);
SELECT name, hired_on FROM employees WHERE hired_on = (SELECT MIN(hired_on) FROM employees);
Output
OUTPUT
+----------+-----------+
| name | salary |
+----------+-----------+
| Asha Rao | 150000.00 |
+----------+-----------+
1 row in set
+----------+------------+
| name | hired_on |
+----------+------------+
| Asha Rao | 2020-01-15 |
+----------+------------+
1 row in setA subquery that finds a row by key
Use another table’s value without a join:
query.sql
SQL
CREATE TABLE departments (id INT PRIMARY KEY, name VARCHAR(20), budget INT);
INSERT INTO departments VALUES (1, 'engineering', 400000), (2, 'sales', 150000);
SELECT e.name, e.salary,
(SELECT d.budget FROM departments d WHERE d.name = e.department) AS department_budget
FROM employees e
WHERE e.department IN ('engineering', 'sales')
ORDER BY e.id
LIMIT 4;
Output
OUTPUT
+--------------+-----------+-------------------+
| name | salary | department_budget |
+--------------+-----------+-------------------+
| Ben Carter | 120000.00 | 400000 |
| Chitra Nair | 95000.00 | 400000 |
| Dev Malhotra | 88000.00 | 400000 |
| Esha Gupta | 70000.00 | 150000 |
+--------------+-----------+-------------------+
4 rows in setThis one is correlated: it uses e.department from the outer row, so MySQL runs it for each row. A join is usually cleaner (Correlated Subqueries).
An empty result is NULL
If the subquery returns no row, a scalar subquery gives NULL, not an error:
query.sql
SQL
SELECT name, (SELECT budget FROM departments WHERE name = 'support') AS support_budget FROM employees WHERE id = 8;
Output
OUTPUT
+-----------+----------------+
| name | support_budget |
+-----------+----------------+
| Hari Iyer | NULL |
+-----------+----------------+
1 row in setColumn subqueries with IN
A subquery that returns one column and many rows is a column subquery. Use it with IN:
query.sql
SQL
SELECT name, department FROM employees
WHERE department IN (SELECT name FROM departments WHERE budget > 200000)
ORDER BY id;
Output
OUTPUT
+--------------+-------------+
| name | department |
+--------------+-------------+
| Ben Carter | engineering |
| Chitra Nair | engineering |
| Dev Malhotra | engineering |
+--------------+-------------+
3 rows in setWhen another tool is better
| Goal | Better tool |
|---|---|
| Every row compared with an average | A scalar subquery is fine |
| A running or per-group average beside each row | Window function: AVG(salary) OVER (PARTITION BY department) |
| A value from another table for each row | A join |
| Several values from the same other row | A join (a scalar subquery returns only one column) |
query.sql
SQL
SELECT name, department, salary, ROUND(AVG(salary) OVER (PARTITION BY department), 0) AS department_average FROM employees ORDER BY id LIMIT 4;
Output
OUTPUT
+--------------+-------------+-----------+--------------------+
| name | department | salary | department_average |
+--------------+-------------+-----------+--------------------+
| Asha Rao | management | 150000.00 | 150000 |
| Ben Carter | engineering | 120000.00 | 101000 |
| Chitra Nair | engineering | 95000.00 | 101000 |
| Dev Malhotra | engineering | 88000.00 | 101000 |
+--------------+-------------+-----------+--------------------+
4 rows in set
lightbulb
Return exactly one
If you are not sure that the subquery gives a single row, add an aggregate (
MAX) or a unique condition. Otherwise one day the data will change and error 1242 will appear in production.