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.

OperatorDescription and CommentsExample
datatype < datatypeLess than8 < 7
⇒ false
datatype > datatypeGreater than8 > 7
⇒ true
datatype <= datatypeLess than or equal to8 <= 8
⇒ true
datatype >= datatypeGreater than or equal to8 >= 8
⇒ true
datatype = datatypeEqual7 = 7
⇒ true
datatype <> datatypeNot equal7 <> 7
⇒ false
datatype != datatypeNot equal7 != 7
⇒ false
datatype BETWEEN datatype AND datatypeBetween (inclusive) the range endpoints.
See notes.
2 BETWEEN 1 AND 3
⇒ true
2 BETWEEN 3 AND 1
⇒ false
datatype NOT BETWEEN datatype AND datatypeNot between (inclusive) the range endpoints.
See notes.
2 NOT BETWEEN 1 AND 3
⇒ false
datatype BETWEEN SYMMETRIC datatype AND datatypeBetween, after sorting the two endpoint values2 BETWEEN SYMMETRIC 3 AND 1
⇒ true
datatype NOT BETWEEN SYMMETRIC datatype AND datatypeNot between, after sorting the two endpoint values2 NOT BETWEEN SYMMETRIC 3 AND 1
⇒ false
datatype IS NULLCheck whether value is NULL.
See notes.
5 IS NULL
⇒ false
datatype IS NOT NULLCheck whether value is not NULL.
See notes.
5 IS NOT NULL
⇒ true
boolean IS TRUECheck whether boolean expression produces true5 < 6 IS TRUE
⇒ false
boolean IS NOT TRUECheck whether boolean expression produces false or unknownNULL::boolean IS NOT TRUE
⇒ true
boolean IS FALSECheck whether boolean expression produces falseNULL::boolean IS FALSE
⇒ false
boolean IS NOT FALSECheck whether boolean expression produces true or unknownNULL::boolean IS NOT FALSE
⇒ true
boolean IS UNKNOWNCheck whether boolean expression produces unknownNULL::boolean IS UNKNOWN
⇒ true
boolean IS NOT UNKNOWNCheck whether boolean expression produces true or falseNULL::boolean IS NOT UNKNOWN
⇒ false

Comparison Functions and Operators Notes

  1. 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:

SELECT o_date FROM orders WHERE (o_order_id BETWEEN SYMMETRIC 10 AND 1);
  1. NULL is not “equal to” NULL, therefore, avoid using the expression = NULL and use IS [ NOT ] NULL instead.