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