PostgreSQL 8.0.26 Documentation | ||||
---|---|---|---|---|

Prev | Fast Backward | Chapter 9. Functions and Operators | Fast Forward | 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's 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 special case where the array has zero elements).

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

row_constructoroperatorrow_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_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
always be either true or false, never null.

row_constructorIS NULLrow_constructorIS 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.

May 22, 2005, 2:11 p.m.

= ANY(array expression) doesn't work in the obvious way when the array expression is a subselect. For example:

select * from stat3 where stat3.id = any ('{4,5,6,7}');

works (and returns the expected tuples). However,

select * from stat3 where stat3.id = any (select stat3 from helix_request where id=11);

DOESN'T work and complains with an error: operator does not exist: integer = integer[]. The inner select returns EXACTLY ONE value, namely the same array as the literal in the first example.

The solution is:

select * from stat3 where (select stat3.id = any (stat3) from helix_request where id=11);

I thank to Chris Kings-Lynne ("KL") for helping me out with this over IRC.

Dec. 6, 2006, 11:13 p.m.

select * from stat3 where stat3.id = any (select stat3 from helix_request where id=11);

Remeber any takes an array argument

So this will work

select * from stat3 where stat3.id = any (ARRAY(select stat3 from helix_request where id=11));