Skip to content Skip to main navigation Skip to footer

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
Single-row subquery returns more than row.
Query Comparison
  • 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?
Query Comparison

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.
IN operator

THE EXTRACT function can be used to extract YEAR, MONTH or DAY fields from a DATE data type.

  • 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.
The ANY operator

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.
The ALL operator

=ALL: How can one value equal every one of a set of values? For this reason, =ALL is rarely used.

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.
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.
NULL Values

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?
GROUP BY and HAVING
  • 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;
SQL Multiple-Row Subqueries
GROUP BY and HAVING with IN Operator

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.
Multiple-Column Subqueries
  • 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.
Multiple-Column 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.
Multiple-Column Subqueries

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.
One Last Point About Subqueries
Some subqueries may return a single row or multiple rows
  • 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!
A multiple-row subquery.
One Last Point About Subqueries

When we use IN, ANY, or ALL to compare with a list of values, they will still work even if there is only one value in the list.

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

Was This Article Helpful?

0
Related Articles
0 Comments

There are no comments yet

Leave a comment

Your email address will not be published. Required fields are marked *