This section describes several specialized constructs for making
multiple comparisons between groups of values. These forms are
syntactically related to the subquery forms of the previous section,
but do not involve subqueries.
The forms involving array subexpressions are
PostgreSQL extensions; the rest are
SQL-compliant.
All of the expression forms documented in this section return
Boolean (true/false) results.
expression IN (value[, ...])
The right-hand side is a parenthesized list
of scalar expressions. The result is "true" if the left-hand expression's
result is equal to any of the right-hand expressions. This is a shorthand
notation for
expression = value1
OR
expression = value2
OR
...
Note that if the left-hand expression yields null, or if there are
no equal right-hand values and at least one right-hand expression yields
null, the result of the IN construct will be null, not false.
This is in accordance with SQL's normal rules for Boolean combinations
of null values.
expression NOT IN (value[, ...])
The right-hand side is a parenthesized list
of scalar expressions. The result is "true" if the left-hand expression's
result is unequal to all of the right-hand expressions. This is a shorthand
notation for
expression <> value1
AND
expression <> value2
AND
...
Note that if the left-hand expression yields null, or if there are
no equal right-hand values and at least one right-hand expression yields
null, the result of the NOT IN construct will be null, not true
as one might naively expect.
This is in accordance with SQL's normal rules for Boolean combinations
of null values.
Tip: x NOT IN y is equivalent to NOT (x IN y) in all
cases. However, null values are much more likely to trip up the novice when
working with NOT IN than when working with IN.
It's best to express your condition positively if possible.
expression operator ANY (array expression)
expression operator SOME (array expression)
The right-hand side is a parenthesized expression, which must yield an
array value.
The left-hand expression
is evaluated and compared to each element of the array using the
given operator, which must yield a Boolean
result.
The result of ANY is "true" if any true result is obtained.
The result is "false" if no true result is found (including the special
case where the array has zero elements).
SOME is a synonym for ANY.
expression operator ALL (array expression)
The right-hand side is a parenthesized expression, which must yield an
array value.
The left-hand expression
is evaluated and compared to each element of the array using the
given operator, which must yield a Boolean
result.
The result of ALL is "true" if all comparisons yield true
(including the special case where the array has zero elements).
The result is "false" if any false result is found.
row_constructor operator row_constructor
Each side is a row constructor,
as described in Section 4.2.11.
The two row values must have the same number of fields.
Each side is evaluated and they are compared row-wise.
Presently, only = and <> operators are allowed
in row-wise comparisons.
The result is "true" if the two rows are equal or unequal, respectively.
As usual, null values in the rows are combined per
the normal rules of SQL Boolean expressions. Two rows are considered
equal if all their corresponding members are non-null and equal; the rows
are unequal if any corresponding members are non-null and unequal;
otherwise the result of the row comparison is unknown (null).
row_constructor IS DISTINCT FROM row_constructor
This construct is similar to a <> row comparison,
but it does not yield null for null inputs. Instead, any null value is
considered unequal to (distinct from) any non-null value, and any two
nulls are considered equal (not distinct). Thus the result will always
be either true or false, never null.
row_constructor IS NULL
row_constructor IS NOT NULL
These constructs test a row value for null or not null. A row value
is considered not null if it has at least one field that is not null.
