# 9.2. 比較函式及運算子 The usual comparison operators are available, as shown in[Table 9.1](https://www.postgresql.org/docs/10/static/functions-comparison.html#functions-comparison-op-table). **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 in[Table 9.2](https://www.postgresql.org/docs/10/static/functions-comparison.html#functions-comparison-pred-table). 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 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 ``` Do*not\_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 the[transform\_null\_equals](https://www.postgresql.org/docs/10/static/runtime-config-compatible.html#guc-transform-null-equals)configuration 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 in[Table 9.3](https://www.postgresql.org/docs/10/static/functions-comparison.html#functions-comparison-func-table). **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` |