Subquery expressions

The following operators can be used with a subquery to form a subquery expression that results in a boolean value (true/false) or null.

Note

The used subquery has to be uncorrelated which means that the subquery does not contain references to relations in a parent statement.

These Comparison operators are supported for subquery expressions.

To compare a list of values other than subqueries, see Array comparisons.

Table of contents

IN (subquery)

Syntax:

expression IN (subquery)

The binary operator IN, which allows you to verify the membership of left-hand operand in the right-hand side subquery that returns exactly one column.

Returns true if any equal subquery row equals the left-hand operand. Returns false otherwise (including the case where the subquery returns no rows).

For example:

cr> select name, surname, sex from employees
... where dept_id in (select id from departments where name = 'Marketing')
... order by name, surname;
+--------+----------+-----+
| name   | surname  | sex |
+--------+----------+-----+
| David  | Bowe     | M   |
| David  | Limb     | M   |
| Sarrah | Mcmillan | F   |
| Smith  | Clark    | M   |
+--------+----------+-----+
SELECT 4 rows in set (... sec)

The result of the IN construct yields to null if:

  • The left-hand expression evaluates to null
  • There are no equal right-hand values and at least one right-hand value yields null

Note

IN (subquery) is an alias for = ANY (subquery) and therefore their results are equivalent.

ANY/SOME (subquery)

Syntax:

expression operator ANY | SOME (subquery)

The ANY construct returns true if the defined comparison is true for any of the values in the column that is returned by the subquery.

It returns false if the subquery does not match with the provided comparison or the subquery returns no rows:

cr> select name, population from countries
... where population > any (select * from unnest([8000000, 22000000, null]))
... order by population, name;
+--------------+------------+
| name         | population |
+--------------+------------+
| Austria      |    8747000 |
| South Africa |   55910000 |
| France       |   66900000 |
| Turkey       |   79510000 |
| Germany      |   82670000 |
+--------------+------------+
SELECT 5 rows in set (... sec)

The result of the ANY construct yields null if:

  • Either the expression or the array is null, and
  • No true comparison is obtained and any element of the array is null

Note

The following is not supported by the ANY operator:

  • is null and is not null as operator

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

    Only single-column subqueries are supported

ALL (subquery)

Syntax:

value operator ALL (subquery)

This is like ALL for array comparisons except for subqueries.

The left-hand expression is evaluated and compared against each row of the right-hand subquery using the supplied operator. The result of ALL is true if all comparisons yield true. The result is false if the comparison of at least one element does not match.

The result is NULL if either the value or the array is NULL or if no comparison is false and at least one comparison returns NULL.

The subquery must return a single column.

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

Supported operators are:

  • =
  • >=
  • >
  • <=
  • <
  • <>