The usual comparison operators are available, as shown in Table 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 |

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**

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:

BETWEEN`a`

AND`x`

`y`

is equivalent to

>=`a`

AND`x`

<=`a`

`y`

Notice that `BETWEEN`

treats the endpoint values as included
in the range.
`NOT BETWEEN`

does the opposite comparison:

NOT BETWEEN`a`

AND`x`

`y`

is equivalent to

<`a`

OR`x`

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

IS DISTINCT FROM`a`

`b`

IS NOT DISTINCT FROM`a`

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

IS NULL`expression`

IS NOT NULL`expression`

or the equivalent, but nonstandard, predicates:

ISNULL`expression`

NOTNULL`expression`

Do *not* write

because * expression* = NULL

`NULL`

is not “equal to”
`NULL`

. (The null value represents an unknown value,
and it is not known whether two unknown values are equal.)
Some applications might expect that

returns true if * expression* = NULL

`expression`

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

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

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**