Array Expressions

IN

expression IN (value [, ...])

The parentheses on the right contain an expression list. The expression result on the left is compared with the content in the expression list. If the content in the list meets the expression result on the left, the result of IN is true. If no result meets the requirements, the result of IN is false.

For example:

1
2
3
4
5

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

NOT IN

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

The parentheses on the right contain an expression list. The expression result on the left is compared with the content in the expression list. If the content in the list does not meet the expression result on the left, the result of NOT IN is true. If any content meets the expression result, the result of NOT IN is false.

Example:

1
2
3
4
5

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

• If the query statement result is null or the expression list does not meet the expression conditions and at least one empty value is returned for the expression list on the right, the result of NOT IN is null rather than false. This method is consistent with the Boolean rules used when SQL statements return empty values.
• In all situations, X NOT IN Y equals to NOT(X IN Y).
• Operations on XML data are not supported.

ANY/SOME (array)

expression operator ANY (array expression)

expression operator SOME (array expression)

The right side is a parenthesized expression, which must yield an array value. The result of the expression on the left uses operators to compute and compare the results in each row of the array expression. The comparison result must be a Boolean value.

Example:

1
2
3
4
5

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

1
2
3
4
5

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

• If at least one comparison result is true, the result of ANY is true.
• If no comparison result is true, the result of ANY is false.
• If no comparison result is true and the array expression generates at least one null value, the value of ANY is NULL rather than false. This method is consistent with the Boolean rules used when SQL statements return empty values.
• SOME is a synonym of ANY.
• Operations on XML data are not supported.

ALL (array)

expression operator ALL (array expression)

The right-hand side is a parenthesized expression, which must yield an array value. The result of the expression on the left uses operators to compute and compare the results in each row of the array expression. The comparison result must be a Boolean value.

• The result of ALL is true if all comparisons yield true (including the case where the array has zero elements).
• If one or more comparison results are false, the result of ALL is false.
• If the array expression generates a null array, the result of ALL is NULL. If the expression on the left yields NULL, the result of ALL is generally NULL (though a non-strict comparison operator could possibly yield a different result). If the array on the right contains any null elements and no false comparison result is found, the result of ALL is NULL, not true (again, assuming a strict comparison operator). This method is consistent with the Boolean rules used when SQL statements return empty values.
• Operations on XML data are not supported.

1
2
3
4
5

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