9.2. Comparison Functions and Operators

## 9.2. Comparison Functions and Operators

The usual comparison operators are available, as shown in Table 9.1.

Table 9.1. Comparison Operators

OperatorDescription
`<` 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. These behave much like operators, but have special syntax mandated by the SQL standard.

Table 9.2. Comparison Predicates

PredicateDescription
`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
```

Do not write `expression = NULL` because `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 = NULL` returns 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 configuration variable is available. If it is enabled, PostgreSQL will 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 in Table 9.3.

Table 9.3. Comparison Functions

FunctionDescriptionExampleExample 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`