Array Expressions

IN

expression IN (value [, ...])

The parentheses on the right contain a list of expressions. The result of the expression on the left is compared with the contents of the expression list. If the contents of the list match the result of the expression on the left, the result of IN is true. If there is no matching result, the result of IN is false.

Example:

1
2
3
4
5

SELECT 8000+500 IN (10000, 9000) AS RESULT;
  result 
----------
 f
(1 row)

NOT IN

expression NOT IN (value [, ...])

The parentheses on the right contain a list of expressions. The result of the expression on the left is compared with the contents of the expression list. If none of the contents in the list matches the result of the expression on the left, the result of NOT IN is true. If there is a matching content, the result of NOT IN is false.

Example:

1
2
3
4
5

SELECT 8000+500 NOT IN (10000, 9000) AS RESULT;
  result 
----------
 t
(1 row)

If the query returns a null result, or the expression list does not satisfy the condition of the expression and at least one result from the expression list on the right is null, then the result of NOT IN is null instead of false. This handling aligns with the Boolean combination rules for SQL returning null values.

Hint: In all cases, X NOT IN Y is equivalent to NOT (X IN Y).

ANY/SOME (array)

expression operator ANY (array expression)

expression operator SOME (array expression)

1
2
3
4
5

SELECT 8000+500 < SOME (array[10000,9000]) AS RESULT;
  result 
----------
 t
(1 row)

1
2
3
4
5

SELECT 8000+500 < ANY (array[10000,9000]) AS RESULT;
  result 
----------
 t
(1 row)

The parentheses on the right contain an array expression, which must yield an array value. The result of the expression on the left is computed and compared using operators against each row's result of the array expression. The comparison result must be a Boolean value.

• If at least one comparison yields a true value, the result of ANY is true.
• If no comparison yields a true value, the result of ANY is false.

If there is no true value in the results and the array expression produces at least one null value, the value of ANY is NULL instead of false. This handling aligns with the Boolean combination rules for SQL returning null values.

SOME is synonymous with ANY.

ALL (array)

expression operator ALL (array expression)

The parentheses on the right contain an array expression, which must yield an array value. The result of the expression on the left is computed and compared using operators against each row's result of the array expression. The comparison result must be a Boolean value.

• If all comparisons yield true values (including when the array contains no elements), the result of ALL is true.
• If any comparison yields a false value, the result of ALL is false.

If the array expression produces a NULL array, the result of ALL is NULL. If the value of the expression on the left is NULL, the result of ALL is typically also NULL (though some non-strict comparison operators might yield different results). Additionally, if the array expression on the right includes a null element and the comparison does not yield a false value, the result of ALL will be NULL (again, some non-strict comparison operators might yield different results) instead of true. This handling aligns with the Boolean combination rules for SQL returning null values.

1
2
3
4
5

SELECT 8000+500 < ALL (array[10000,9000]) AS RESULT;
  result
----------
 t
(1 row)