Subquery expressions

Some operators can be used with an uncorrelated subquery to form a subquery expression that returns a boolean value (i.e., true or false) or NULL.

See also

SQL: Value expressions

IN (subquery)

Syntax:

  1. expression IN (subquery)

The subquery must produce result rows with a single column only.

Here’s an example:

  1. cr> select name, surname, sex from employees
  2. ... where dept_id in (select id from departments where name = 'Marketing')
  3. ... order by name, surname;
  4. +--------+----------+-----+
  5. | name   | surname  | sex |
  6. +--------+----------+-----+
  7. | David  | Bowe     | M   |
  8. | David  | Limb     | M   |
  9. | Sarrah | Mcmillan | F   |
  10. | Smith  | Clark    | M   |
  11. +--------+----------+-----+
  12. SELECT 4 rows in set (... sec)

The IN operator returns true if any subquery row equals the left-hand operand. Otherwise, it returns false (including the case where the subquery returns no rows).

The operator returns NULL if:

  • The left-hand expression evaluates to NULL

  • There are no matching right-hand values and at least one right-hand value is NULL

Note

IN (subquery) is an alias for = ANY (subquery)

ANY/SOME (subquery)

Syntax:

  1. expression comparison ANY | SOME (subquery)

Here, comparison can be any basic comparison operator. The subquery must produce result rows with a single column only.

Here’s an example:

  1. cr> select name, population from countries
  2. ... where population > any (select * from unnest([8000000, 22000000, NULL]))
  3. ... order by population, name;
  4. +--------------+------------+
  5. | name         | population |
  6. +--------------+------------+
  7. | Austria      |    8747000 |
  8. | South Africa |   55910000 |
  9. | France       |   66900000 |
  10. | Turkey       |   79510000 |
  11. | Germany      |   82670000 |
  12. +--------------+------------+
  13. SELECT 5 rows in set (... sec)

The ANY operator returns true if the defined comparison is true for any of the result rows of the right-hand subquery.

The operator returns false if the comparison returns false for all result rows of the subquery or if the subquery returns no rows.

The operator returns NULL if:

  • The left-hand expression evaluates to NULL

  • There are no matching right-hand values and at least one right-hand value is NULL

Note

The following is not supported:

  • IS NULL or IS NOT NULL as comparison

  • Matching as many columns as there are expressions on the left-hand row e.g. (x,y) = ANY (select x, y from t)

ALL (subquery)

Syntax:

  1. value comparison ALL (subquery)

Here, comparison can be any basic comparison operator. The subquery must produce result rows with a single column only.

Here’s an example:

  1. cr> select 100 <> ALL (select height from sys.summits) AS x;
  2. +------+
  3. | x    |
  4. +------+
  5. | TRUE |
  6. +------+
  7. SELECT 1 row in set (... sec)

The ALL operator returns true if the defined comparison is true for all of the result rows of the right-hand subquery.

The operator returns false if the comparison returns false for any result rows of the subquery.

The operator returns NULL if:

  • The left-hand expression evaluates to NULL

  • No comparison returns false and at least one right-hand value is NULL