23rd May 2019: PostgreSQL 12 Beta 1 Released!

PostgreSQL 9.3.25 Documentation | ||||
---|---|---|---|---|

Prev | Up | Chapter 9. Functions and Operators | Next |

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.

expressionIN (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=value1ORexpression=value2OR ...

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.

expressionNOT 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<>value1ANDexpression<>value2AND ...

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 yis equivalent toNOT (x IN y)in all cases. However, null values are much more likely to trip up the novice when working withNOT INthan when working withIN. It is best to express your condition positively if possible.

expressionoperatorANY (array expression)expressionoperatorSOME (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 case where the array has zero elements).

If the array expression yields a null array, the result of
`ANY` will be null. If the left-hand
expression yields null, the result of `ANY` is
ordinarily null (though a non-strict comparison operator could
possibly yield a different result). Also, if the right-hand array
contains any null elements and no true comparison result is
obtained, the result of `ANY` will be null,
not false (again, assuming a strict comparison operator). This is
in accordance with SQL's normal rules for Boolean combinations of
null values.

`SOME` is a synonym for `ANY`.

expressionoperatorALL (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
case where the array has zero elements). The result is "false" if any false result is found.

If the array expression yields a null array, the result of
`ALL` will be null. If the left-hand
expression yields null, the result of `ALL` is
ordinarily null (though a non-strict comparison operator could
possibly yield a different result). Also, if the right-hand array
contains any null elements and no false comparison result is
obtained, the result of `ALL` will be null,
not true (again, assuming a strict comparison operator). This is in
accordance with SQL's normal rules for Boolean combinations of null
values.

row_constructoroperatorrow_constructor

Each side is a row constructor, as described in Section
4.2.13. The two row values must have the same number of fields.
Each side is evaluated and they are compared row-wise. Row
comparisons are allowed when the `operator` is `=`,
`<>`, `<`,
`<=`, `>` or
`>=`, or has semantics similar to one of
these. (To be specific, an operator can be a row comparison
operator if it is a member of a B-tree operator class, or is the
negator of the `=` member of a B-tree
operator class.)

The `=` and `<>` cases work slightly differently from the
others. 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).

For the `<`, `<=`, `>` and `>=` cases, the row elements are compared
left-to-right, stopping as soon as an unequal or null pair of
elements is found. If either of this pair of elements is null, the
result of the row comparison is unknown (null); otherwise
comparison of this pair of elements determines the result. For
example, `ROW(1,2,NULL) < ROW(1,3,0)`
yields true, not null, because the third pair of elements are not
considered.

Note:Prior to PostgreSQL 8.2, the<,<=,>and>=cases were not handled per SQL specification. A comparison likeROW(a,b) < ROW(c,d)was implemented asa < c AND b < dwhereas the correct behavior is equivalent toa < c OR (a = c AND b < d).

row_constructorIS DISTINCT FROMrow_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 either be true or false, never
null.

row_constructorIS NOT DISTINCT FROMrow_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.

Note:The SQL specification requires row-wise comparison to return NULL if the result depends on comparing two NULL values or a NULL and a non-NULL. PostgreSQL does this only when comparing the results of two row constructors or comparing a row constructor to the output of a subquery (as in Section 9.22). In other contexts where two composite-type values are compared, two NULL field values are considered equal, and a NULL is considered larger than a non-NULL. This is necessary in order to have consistent sorting and indexing behavior for composite types.