# Comparison Operators

Comparison operators compare a value against another value.

## Usual comparison operators

The usual comparison operators are available:

Operator | Description |
---|---|

`<` | less than |

`>` | greater than |

`<=` | less than or equal to |

`>=` | greater than or equal to |

`=` | equal |

`<>` or `!=` | not equal |

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`

).

`BETWEEN`

The `BETWEEN`

predicate simplifies range tests

`<a> BETWEEN <x> AND <y>`

<a> NOT BETWEEN <x> AND <y>

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

.

## NULL-sensitive comparison operators

While the normal comparison operators and `BETWEEN`

ignore NULLs, we
sometimes want different NULL behavior. For this purpose, the SQL standard
provides the following comparison functions. These behave much like operators
but have special syntax mandated by the SQL standard.

`<a> IS DISTINCT FROM <b>`

<a> IS NOT DISTINCT FROM <b>

<expression> IS NULL

<expression> IS NOT NULL

<expression> ISNULL -- nonstandard syntax

<expression> NOTNULL -- nonstandard syntax

<boolean_expression> IS TRUE

<boolean_expression> IS NOT TRUE -- is false or unknown

<boolean_expression> IS FALSE

<boolean_expression> IS NOT FALSE -- is true or unknown

<boolean_expression> IS UNKNOWN

<boolean_expression> IS NOT UNKNOWN -- is true or false

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.)

Boolean values can also be tested using the predicates `IS TRUE`

,
`IS NOT TRUE`

, `IS FALSE`

, `IS NOT FALSE`

, `IS UNKNOWN`

, and `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.

`IN`

and `NOT IN`

`IN`

and `NOT IN`

compare an expression against multiple values. They use
the syntax

`<expression> IN ( <value>, ... )`

<expression> NOT IN ( <value>, ... )

The right-hand side is a parenthesized list of scalar expressions. The value from the left-hand side is compared against each of the expressions from the right-hand side.

For `IN`

, the result is "true" if the left-hand expression's result is
equal to any of the right-hand expressions. This is a shorthand notation
for `expression = value1 OR expression = value2 OR ...`

For `NOT IN`

the result is "true" if the left-hand expression's result is
unequal to all of the right-hand expressions. This is a shorthand notation
for `expression <> value1 AND expression <> value2 AND ...`

Note that if the left-hand expression yields null, or if there are no
equal right-hand values and at least one right-hand expression yields
null, the result of the `IN`

/`NOT IN`

construct will be null, not false.
This is in accordance with SQL's normal rules for Boolean combinations
of null values.

`IN`

and `NOT IN`

are syntactically related to the subquery forms
but do not involve subqueries.

`x NOT IN y`

is equivalent to `NOT (x IN y)`

in all cases. However, null
values are much more likely to trip up the novice when working with NOT
IN than when working with IN. It is best to express your condition
positively if possible.