Subqueries Returning Multiple Values

If a subquery is likely to return more than one value, a direct comparison cannot be used. There are three possible methods of handling such subqueries, each with its own syntax:

1. WHERE column_name comparison ALL (SELECT statement)

   The comparison must be one of the comparison conditions listed here. The column (expression or function) that is the object of the condition must satisfy the comparison for ALL values returned by the subquery.

   For example:

   SELECT c0002title
   FROM t0002film
   WHERE c0002id != ALL (SELECT UNIQUE c0002id FROM t0003rental);

2. WHERE column_name comparison {ANY SOME} (SELECT statement)

   The comparison must be one of the comparison conditions listed here. The keywords ANY and SOME are synonymous. The column (expression or function) that is the object of the condition must satisfy the comparison for at least one of the values returned by the subquery.

   For example:

   SELECT c0002title
   FROM t0002film
   WHERE c0002id = SOME (SELECT UNIQUE c0002id FROM t0003rental);

3. WHERE column_name [NOT] IN (SELECT statement)

   For example:

   SELECT c0002title
   FROM t0002film
   WHERE c0002id IN (SELECT UNIQUE c0002id FROM t0003rental);

   The keyword IN is equivalent to = ANY.

   The keywords NOT IN is equivalent to ! = ALL.

   Note: Subqueries are often used when a table's data is required for cross-reference purposes, but its data is not required for output. Embedding the cross-reference table in a subquery saves the DBMS having to consider the Cartesian Joins between it and any other tables whose data is required for output. For example, consider the above example and compare it with:

   SELECT UNIQUE c0002title
   FROM t0002film t0002, t0003rental t0003
   WHERE t0002.c0002id = t0003.c0002id;

   The latter example has to consider the Cartesian Joins between the film and rental tables, because both appear in the From clause. The former example processes the row from each table entirely separately.