Expressions（3）
Tags: opengauss
Published: 2023-03-06 06:49:39

**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 content meets the expression result, the result of IN is false.

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3vzyglfrz7wvpocm3mws.png)

If the expression result is null or the expression list does not meet the expression conditions and at least one null value is returned for the expression list on the right, the result of **IN** is **null** rather than **false**. This method is consistent with the Boolean rules used when SQL statements return null values.

· 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:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/amgv721km7raumjjua2c.png)

If the query statement result is null or the expression list does not meet the expression conditions and at least one null 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 null values.

 NOTE: In all situations, X NOT IN Y equals to NOT(X IN Y).

· 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 calculate and compare the results in each row of the array expression. The comparison result must be a Boolean value.

· 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 null **values**.
· **SOME** is a synonym of **ANY**.
Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m5ufueonetbiczzc8kg9.png)

ALL(array)

*expression operator **ALL ***(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 calculate 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 comparison results are true (including the case where the array has zero elements).
· The result of **ALL** is **false** if one or multiple comparison results are false.
· If the array expression yields a null array, the result of **ALL** is **NULL**. If the left-hand expression yields **NULL**, the result of **ALL** is generally **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** is **NULL**, not **true** (again, assuming a strict comparison operator). This method is consistent with the Boolean rules used when SQL statements return null values.
Example

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6ysdskfq0ngfacajb172.png)

**Row Expressions**

The syntax is as follows:

**row_constructor operator row_constructor**

Both sides of the row expression are row constructors. The values of both rows must have the same number of columns and they are compared with each other. Row comparison allows operators including =, <>, <, <=, and >= or a similar operator.

The use of operators = and <> is slightly different from the other operators. If all columns of two rows are not null and equal, the two rows are equal. If any column in two rows is not null and not equal, the two rows are not equal. Otherwise, the comparison result is null.

For operators <, <=, >, and > =, the columns in rows are compared from left to right until a pair of columns that are not equal or are null are detected. If this pair of columns contains at least one null value, the comparison result is null. Otherwise, the comparison result of this pair of columns is the final result.

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bf52cvbhme2zb8l39sai.png)