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