Comparison operators v16

The usual comparison operators are shown in the table.

OperatorDescription
<Less than
>Greater than
<=Less than or equal to
>=Greater than or equal to
=Equal
<>Not equal
!=Not equal

Comparison operators are available for all data types where they makes sense. All comparison operators are binary operators that return values of type BOOLEAN. Expressions like 1 < 2 < 3 aren't valid because there is no < operator to compare a Boolean value with 3.

In addition to the comparison operators, the special BETWEEN construct is available.

a BETWEEN x AND y

is equivalent to:

a >= x AND a <= y

Similarly:

a NOT BETWEEN x AND y

is equivalent to:

a < x OR a > y

There's no difference between the two forms apart from the CPU cycles required to rewrite the first one into the second one internally.

To check whether a value is null, use the constructs:

expression IS NULL

expression IS NOT NULL

Don't write expression = NULL because NULL isn't "equal to" NULL. The null value represents an unknown value, and it isn't known whether two unknown values are equal. This behavior conforms to the SQL standard.

Some applications might expect that expression = NULL returns true if expression evaluates to the null value. We highly recommend that you modify these applications to comply with the SQL standard.