SQL Functions and Operators
Comparison Functions and Operators
All the following operators return BOOLEAN
.
The right arrow (⇒ ) indicates the result.
datatype
refers to an operand of any supported compatible data type (see Data Types).
boolean
refers to an operand of BOOLEAN
data type.
Operator | Description and Comments | Example |
---|---|---|
datatype < datatype | Less than | 8 < 7 ⇒ false |
datatype > datatype | Greater than | 8 > 7 ⇒ true |
datatype <= datatype | Less than or equal to | 8 <= 8 ⇒ true |
datatype >= datatype | Greater than or equal to | 8 >= 8 ⇒ true |
datatype = datatype | Equal | 7 = 7 ⇒ true |
datatype <> datatype | Not equal | 7 <> 7 ⇒ false |
datatype != datatype | Not equal | 7 != 7 ⇒ false |
datatype BETWEEN datatype AND datatype | Between (inclusive) the range endpoints. See notes. | 2 BETWEEN 1 AND 3 ⇒ true 2 BETWEEN 3 AND 1 ⇒ false |
datatype NOT BETWEEN datatype AND datatype | Not between (inclusive) the range endpoints. See notes. | 2 NOT BETWEEN 1 AND 3 ⇒ false |
datatype BETWEEN SYMMETRIC datatype AND datatype | Between, after sorting the two endpoint values | 2 BETWEEN SYMMETRIC 3 AND 1 ⇒ true |
datatype NOT BETWEEN SYMMETRIC datatype AND datatype | Not between, after sorting the two endpoint values | 2 NOT BETWEEN SYMMETRIC 3 AND 1 ⇒ false |
datatype IS NULL | Check whether value is NULL. See notes. | 5 IS NULL ⇒ false |
datatype IS NOT NULL | Check whether value is not NULL. See notes. | 5 IS NOT NULL ⇒ true |
boolean IS TRUE | Check whether boolean expression produces true | 5 < 6 IS TRUE ⇒ false |
boolean IS NOT TRUE | Check whether boolean expression produces false or unknown | NULL::boolean IS NOT TRUE ⇒ true |
boolean IS FALSE | Check whether boolean expression produces false | NULL::boolean IS FALSE ⇒ false |
boolean IS NOT FALSE | Check whether boolean expression produces true or unknown | NULL::boolean IS NOT FALSE ⇒ true |
boolean IS UNKNOWN | Check whether boolean expression produces unknown | NULL::boolean IS UNKNOWN ⇒ true |
boolean IS NOT UNKNOWN | Check whether boolean expression produces true or false | NULL::boolean IS NOT UNKNOWN ⇒ false |
Comparison Functions and Operators Notes
-
BETWEEN
operator: it is expected that the first value is lower than the second one. If the first value is greater than the second one the result is an empty result set.To avoid the need to consider which of the two values is greater than the other, use the keyword
SYMMETRIC
.For example:
NULL
is not “equal to”NULL
, therefore, avoid using the expression= NULL
and useIS [ NOT ] NULL
instead.