# 9.2. 比較函式及運算子1

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
`a``BETWEEN``x``AND``y` between
`a``NOT BETWEEN``x``AND``y` not between
`a``BETWEEN SYMMETRIC``x``AND``y` between, after sorting the comparison values
`a``NOT BETWEEN SYMMETRIC``x``AND``y` not between, after sorting the comparison values
`a``IS DISTINCT FROM``b` not equal, treating null like an ordinary value
`a``IS NOT DISTINCT FROM``b` equal, treating null like an ordinary value
`expression``IS NULL` is null
`expression``IS NOT NULL` is not null
`expression``ISNULL` is null (nonstandard syntax)
`expression``NOTNULL` is not null (nonstandard syntax)
`boolean_expression``IS TRUE` is true
`boolean_expression``IS NOT TRUE` is false or unknown
`boolean_expression``IS FALSE` is false
`boolean_expression``IS NOT FALSE` is true or unknown
`boolean_expression``IS UNKNOWN` is unknown
`boolean_expression``IS 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
b
a
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`row``IS 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`

1. PostgreSQL: Documentation: 10: 9.2. Comparison Functions and Operators