SQL Multiple-Row Subqueries
Objectives
This lesson covers the following objectives:
- Correctly use the comparison operators IN, ANY, and ALL in multiple-row subqueries
- Construct and execute a multiple-row subquery in the WHERE clause or HAVING clause
- Describe what happens if a multiple-row subquery returns a null value
- Understand when multiple-row subqueries should be used, and when it is safe to use a single-row subquery
- Distinguish between pair-wise and non-pair-wise subqueries
Purpose of SQL Multiple-Row Subqueries
- A subquery is designed to find information you don’t know so that you can find information you want to know.
- However, single-row subqueries can return only one row. What if you need to find information based on several rows and several values?
- The subquery will need to return several rows.
- We achieve this using multiple-row subqueries and the three comparison operators: IN, ANY, and ALL.
Query Comparison
- Whose salary is equal to the salary of an employee in department 20 ?
- This example returns an error because more than one employee exists in department 20, the subquery returns multiple rows.
- We call this a multiple-row subquery


- The problem is the equal sign (=) in the WHERE clause of the outer query.
- How can one value be equal to (or not equal to) more than one value at a time?
- It’s a silly question, isn’t it?

For the same reason, we cannot use <, > or <> in the WHERE clause condition.
It doesn’t make sense to compare one value with several values. Is 10000 less than (13000,6000)?
IN, ANY, and ALL
- Subqueries that return more than one value are called multiple-row subqueries.
- Because we cannot use the single-row comparison operators (=, <, and so on), we need different comparison operators for multiple-row subqueries.
- The multiple-row operators are:
- IN,
- ANY
- ALL
- The NOT operator can be used with any of these three operators.
IN
- The IN operator is used within the outer query WHERE clause to select only those rows which are IN the list of values returned from the inner query.
- For example, we are interested in all the employees that were hired the same year as an employee in department 90.

- The inner query will return a list of the years that employees in department 90 were hired.
- The outer query will then return any employee that was hired the same year as any year in the inner query list.
ANY
- The ANY operator is used when we want the outerquery WHERE clause to select the rows which match the criteria (<, >, =, etc.) of at least one value in the subquery result set.

ALL
- The ALL operator is used when we want the outer-query WHERE clause to select the rows which match the criteria ( <, >, =, etc.) of all of the values in the subquery result set.
- The ALL operator compares a value to every value returned by the inner query.
- As no employee was hired before 1987, no rows are returned.

NULL Values
- Suppose that one of the values returned by a multiple-row subquery is null, but other values are not.
- If IN or ANY are used, the outer query will return rows which match the non-null values.

- If ALL is used, the outer query returns no rows because ALL compares the outer query row with every value returned by the subquery, including the null.
- And comparing anything with null results in null.

GROUP BY and HAVING
- As you might suspect, the GROUP BY clause and the HAVING clause can also be used with multiple-row subqueries.
- What if you wanted to find the departments whose minimum salary is less than the salary of any employee who works in department 10 or 20?

- We need a multiple-row subquery which returns the salaries of employees in departments 10 and 20.
- The outer query will use a group function (MIN) so we need to GROUP the outer query BY department_id.
- Here is the SQL statement:
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) < ANY
(SELECT salary
FROM employees
WHERE department_id IN (10,20))
ORDER BY department_id;


Multiple-Column Subqueries
- Subqueries can use one or more columns.
- If they use more than one column, they are called multiple-column subqueries.
- A multiple-column subquery can be either pair-wise comparisons or non-pair-wise comparisons.

- The example above shows a multiple-column pair-wise subquery with the subquery highlighted in red and the result in the table below.
- The query lists the employees whose manager and departments are the same as the manager and department of employees 149 or 174.
First, the subquery to retrieve the MANAGER_ID and DEPARTMENT_ID values for the employees with EMPLOYEE_ID 149 or 174 is executed.
These values are compared with the MANAGER_ID column and the DEPARTMENT_ID column of each row in the EMPLOYEES table. If the values match, the row is displayed.
In the output, the records of the employees with the EMPLOYEE_ID 149 or 174 will not be displayed.
- A non-pair-wise multiple-column subquery also uses more than one column in the subquery, but it compares them one at a time, so the comparisons take place in different subqueries.

- You will need to write one subquery per column you want to compare against when performing non-pair-wise multiple column subqueries.
- The example on the right shows a multiple-column non-pair-wise subquery with the subqueries highlighted in red.
- This query is listing the employees who have the same manager_id and department_id as employees 149 or 174.

First, the subquery to retrieve the MANAGER_ID values for the employees with the EMPLOYEE_ID 149 or 174 is executed. Similarly, the second subquery to retrieve the DEPARTMENT_ID values for the employees with the EMPLOYEE_ID 149 or 174 is executed.
The retrieved values are compared with the MANAGER_ID and DEPARTMENT_ID column for each row in the EMPLOYEES table.
One Last Point About Subqueries
- Some subqueries may return a single row or multiple rows, depending on the data values in the rows.
- If even the slightest possibility exists of returning multiple rows, make sure you write a multiple-row subquery.


- For example: Who has the same job_id as Ernst?
- This single-row subquery works correctly because there is only one Ernst in the table.
- But what if later, the business hires a new employee named Susan Ernst?
- It would be better to write a multiple-row subquery.
- The multiple-row subquery syntax will still work even if the subquery returns a single row.
- If in doubt, write a multiple-row subquery!


Terminology
Key terms used in this lesson included:
- Non-pair-wise multiple column subquery
- Pair-wise multiple column subquery
Summary
In this lesson, you should have learned how to:
- Correctly use the comparison operators IN, ANY, and ALL in multiple-row subqueries
- Construct and execute a multiple-row subquery in the WHERE clause or HAVING clause
- Describe what happens if a multiple-row subquery returns a null value
- Understand when multiple-row subqueries should be used, and when it is safe to use a single-row subquery
- Distinguish between pair-wise and non-pair-wise subqueries
0 Comments