SQL Subqueries

A subquery is a query nested inside another query.

Most examples use this employees table:

id	name	dept	salary	city
1	Asha	IT	65000	Pune
2	Ravi	HR	42000	Mumbai
3	Meera	IT	78000	Pune
4	Karan	Sales	51000	Delhi
5	Neha	HR	47000	Pune

Single-Row Subquery

SQL

-- Employees earning more than the average
SELECT name, salary FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

+-------+--------+ | name | salary | +-------+--------+ | Asha | 65000 | | Meera | 78000 | +-------+--------+

Second-Highest Salary (Classic Interview Question)

SQL

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

+----------------+ | second_highest | +----------------+ | 65000 | +----------------+

Correlated Subquery

The inner query refers to the outer row, so it runs once for each outer row.

SQL

-- Highest-paid employee in each department
SELECT name, dept, salary FROM employees e
WHERE salary = (SELECT MAX(salary) FROM employees WHERE dept = e.dept);

+-------+-------+--------+ | name | dept | salary | +-------+-------+--------+ | Meera | IT | 78000 | | Karan | Sales | 51000 | | Neha | HR | 47000 | +-------+-------+--------+

EXISTS

SQL

SELECT dept_name FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM staff s WHERE s.dept_id = d.dept_id);

+-----------+ | dept_name | +-----------+ | Finance | +-----------+

Track your progress through the SQL course

← Prev Next →