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