Writing Multiple Row Subqueries

You use a multiple row subquery to return one or more rows to the outer SQL statement. To handle a subquery that returns multiple rows, your outer query may use the IN , ANY , or ALL operator. As you saw in Chapter 2, you can use these operators to check and compare values supplied in a list of literal values. As you ll see in this section, you can also supply this list of values from a subquery.

Note  

You can also use the EXISTS operator to check if a value is in a list returned by a correlated subquery, which you ll learn about later in the section Writing Correlated Subqueries.

Using IN with a Multiple Row Subquery

As you saw in Chapter 2, you use IN to check if a value is in a specified list of values. The list of values may come from the results returned by a subquery. You can also use NOT IN to perform the logical opposite of IN : you use NOT IN to check if a value is not in a specified list of values.

The following simple example uses IN to check if a product_id is in the list of product_id values returned by the subquery; the subquery returns the product_id column values for the products whose name contains the letter e :

  SELECT product_id, name   FROM products   WHERE product_id IN   (SELECT product_id   FROM products   WHERE name LIKE '%e%');  PRODUCT_ID NAME ---------- -------------------  1 Modern Science  2 Chemistry  3 Supernova  5 Z Files  6 2412: The Return  7 Space Force 9  8 From Another Planet  11 Creative Yell  12 My Front Line 

The next example uses NOT IN to check if a product_id is not in the list of product_id values in the purchases table:

  SELECT product_id, name   FROM products   WHERE product_id NOT IN   (SELECT product_id   FROM purchases);  PRODUCT_ID NAME ---------- -------------------  4 Tank War  5 Z Files  6 2412: The Return  7 Space Force 9  8 From Another Planet  9 Classical Music  10 Pop 3  11 Creative Yell  12 My Front Line 

Using ANY with a Multiple Row Subquery

You use the ANY operator to compare a value with any value in a list. You must place an = , <>, <, >, < = , or > = operator before ANY in your query. The following example uses ANY to check if any of the employees has a salary less than any of the lowest salaries in the salary_grades table:

  SELECT employee_id, last_name   FROM employees   WHERE salary  <  ANY   (SELECT low_salary   FROM salary_grades);  EMPLOYEE_ID LAST_NAME ----------- ----------  2 Johnson  3 Hobbs  4 Jones 

Using ALL with a Multiple Row Subquery

You use the ALL operator to compare a value with any value in a list. You must place an = , <>, <, >, < = , or > = operator before ALL in your query. The following example uses ALL to check if any of the employees has a salary greater than all of the highest salaries in the salary_grades table:

  SELECT employee_id, last_name   FROM employees   WHERE salary  >  ALL   (SELECT high_salary   FROM salary_grades);  no rows selected 

As you can see from this result, no employee has a salary greater than the highest salary. This is probably a good thing, since you don t want an employee earning more than the highest salary!