# 9.2. 比較函式及運算子

The usual comparison operators are available, as shown inTable 9.1.

Table 9.1. Comparison Operators

 Operator Description `<` less than `>` greater than `<=` less than or equal to `>=` greater than or equal to `=` equal `<>`or`!=` not equal

# Note

The`!=`operator is converted to`<>`in the parser stage. It is not possible to implement`!=`and`<>`operators that do different things.

Comparison operators are available for all relevant data types. All comparison operators are binary operators that return values of type`boolean`; expressions like`1 < 2 < 3`are not valid (because there is no`<`operator to compare a Boolean value with`3`).

There are also some comparison predicates, as shown inTable 9.2. These behave much like operators, but have special syntax mandated by the SQL standard.

Table 9.2. Comparison Predicates

 Predicate Description `aBETWEENxANDy` between `aNOT BETWEENxANDy` not between `aBETWEEN SYMMETRICxANDy` between, after sorting the comparison values `aNOT BETWEEN SYMMETRICxANDy` not between, after sorting the comparison values `aIS DISTINCT FROMb` not equal, treating null like an ordinary value `aIS NOT DISTINCT FROMb` equal, treating null like an ordinary value `expressionIS NULL` is null `expressionIS NOT NULL` is not null `expressionISNULL` is null (nonstandard syntax) `expressionNOTNULL` is not null (nonstandard syntax) `boolean_expressionIS TRUE` is true `boolean_expressionIS NOT TRUE` is false or unknown `boolean_expressionIS FALSE` is false `boolean_expressionIS NOT FALSE` is true or unknown `boolean_expressionIS UNKNOWN` is unknown `boolean_expressionIS NOT UNKNOWN` is true or false

The`BETWEEN`predicate simplifies range tests:

`a BETWEEN x AND y`

is equivalent to

`a>= x AND a<= y`

Notice that`BETWEEN`treats the endpoint values as included in the range.`NOT BETWEEN`does the opposite comparison:

`a NOT BETWEEN x AND y`

is equivalent to

`a<x OR a>y`

`BETWEEN SYMMETRIC`is like`BETWEEN`except there is no requirement that the argument to the left of`AND`be less than or equal to the argument on the right. If it is not, those two arguments are automatically swapped, so that a nonempty range is always implied.

Ordinary comparison operators yield null (signifying“unknown”), not true or false, when either input is null. For example,`7 = NULL`yields null, as does`7 <> NULL`. When this behavior is not suitable, use the`IS [NOT] DISTINCT FROM`predicates:

`a IS DISTINCT FROM ba IS NOT DISTINCT FROM b`

For non-null inputs,`IS DISTINCT FROM`is the same as the`<>`operator. However, if both inputs are null it returns false, and if only one input is null it returns true. Similarly,`IS NOT DISTINCT FROM`is identical to`=`for non-null inputs, but it returns true when both inputs are null, and false when only one input is null. Thus, these predicates effectively act as though null were a normal data value, rather than“unknown”.

To check whether a value is or is not null, use the predicates:

`expression IS NULL​expression IS NOT NULL`

or the equivalent, but nonstandard, predicates:

`expression ISNULL​expression NOTNULL`

Donot_write`expression`_= NULLbecause`NULL`is not“equal to”`NULL`. (The null value represents an unknown value, and it is not known whether two unknown values are equal.)

# Tip

Some applications might expect that`expression`= NULLreturns true if_`expression`_evaluates to the null value. It is highly recommended that these applications be modified to comply with the SQL standard. However, if that cannot be done thetransform_null_equalsconfiguration variable is available. If it is enabled,PostgreSQLwill convert`x = NULL`clauses to`x IS NULL`.

If the`expression`_is row-valued, then`IS NULL`is true when the row expression itself is null or when all the row's fields are null, while`IS NOT NULL`is true when the row expression itself is non-null and all the row's fields are non-null. Because of this behavior,`IS NULL`and`IS NOT NULL`do not always return inverse results for row-valued expressions; in particular, a row-valued expression that contains both null and non-null fields will return false for both tests. In some cases, it may be preferable to write`rowIS DISTINCT FROM NULL`or`row`_`IS NOT DISTINCT FROM NULL`, which will simply check whether the overall row value is null without any additional tests on the row fields.

Boolean values can also be tested using the predicates

`boolean_expression IS TRUE​boolean_expression IS NOT TRUE​boolean_expression IS FALSE​boolean_expression IS NOT FALSE​boolean_expression IS UNKNOWN​boolean_expression IS NOT UNKNOWN`

These will always return true or false, never a null value, even when the operand is null. A null input is treated as the logical value“unknown”. Notice that`IS UNKNOWN`and`IS NOT UNKNOWN`are effectively the same as`IS NULL`and`IS NOT NULL`, respectively, except that the input expression must be of Boolean type.

Some comparison-related functions are also available, as shown inTable 9.3.

Table 9.3. Comparison Functions

 Function Description Example Example Result `num_nonnulls(VARIADIC "any")` returns the number of non-null arguments `num_nonnulls(1, NULL, 2)` `2` `num_nulls(VARIADIC "any")` returns the number of null arguments `num_nulls(1, NULL, 2)` `1`