Multiple-Row Subquery
Learn how to use a subquery that returns multiple rows or multiple values.
What is a Multiple-Row Subquery?
A Multiple-Row Subquery is a subquery that returns more than one row or value.
Since the subquery can return multiple values, operators such as IN, ANY and ALL are commonly used with it.
Basic Syntax
SELECT column_name
FROM table_name
WHERE column_name IN
(
SELECT column_name
FROM another_table
WHERE condition
);
The inner query returns multiple values and the outer query uses those values to filter records.
Example Tables
Consider the following Employees table:
| EmployeeId | EmployeeName | DepartmentId | Salary |
|---|---|---|---|
| 1 | Rahul | 101 | 30000 |
| 2 | Priya | 102 | 45000 |
| 3 | Amit | 101 | 50000 |
| 4 | Sneha | 103 | 40000 |
| 5 | Neha | 102 | 55000 |
And consider the following Departments table:
| DepartmentId | DepartmentName | Location |
|---|---|---|
| 101 | IT | Pune |
| 102 | HR | Pune |
| 103 | Finance | Mumbai |
Example 1: Multiple-Row Subquery with IN
Suppose we want to find employees who work in departments located in Pune.
SELECT EmployeeName, DepartmentId
FROM Employees
WHERE DepartmentId IN
(
SELECT DepartmentId
FROM Departments
WHERE Location = 'Pune'
);
How it works
- The inner query finds department IDs where the location is Pune.
- The inner query can return multiple department IDs.
- The outer query checks whether Employee DepartmentId exists in those returned values.
Example 2: Multiple-Row Subquery with NOT IN
The NOT IN operator can be used when we want records that do not match any value returned by the subquery.
SELECT EmployeeName, DepartmentId
FROM Employees
WHERE DepartmentId NOT IN
(
SELECT DepartmentId
FROM Departments
WHERE Location = 'Pune'
);
This query returns employees who do not belong to departments located in Pune.
Example 3: Using ANY
The ANY operator compares a value with the values returned by a subquery.
For example, find employees whose salary is greater than the salary of ANY employee in department 101.
SELECT EmployeeName, Salary
FROM Employees
WHERE Salary > ANY
(
SELECT Salary
FROM Employees
WHERE DepartmentId = 101
);
The condition is true when the comparison is true for at least one value returned by the subquery.
Example 4: Using ALL
The ALL operator compares a value with every value returned by the subquery.
Find employees whose salary is greater than the salary of ALL employees in department 101.
SELECT EmployeeName, Salary
FROM Employees
WHERE Salary > ALL
(
SELECT Salary
FROM Employees
WHERE DepartmentId = 101
);
The condition must be true for every value returned by the subquery.
IN vs ANY vs ALL
| Operator | Purpose |
|---|---|
| IN | Checks whether a value matches any value returned by the subquery. |
| ANY | The comparison must be true for at least one value returned by the subquery. |
| ALL | The comparison must be true for every value returned by the subquery. |
Important Points
- A multiple-row subquery can return more than one row.
- The IN operator is commonly used with multiple-row subqueries.
- NOT IN can be used to exclude matching values.
- ANY checks whether the comparison is true for at least one returned value.
- ALL checks whether the comparison is true for every returned value.
- Using = with a subquery that returns multiple rows can cause an error.
Summary
A Multiple-Row Subquery returns multiple values or rows. Operators such as IN, NOT IN, ANY and ALL can be used to work with the returned values. Multiple-row subqueries are useful when the result of one query needs to be compared with a set of values.