Skip to main content

Comparison functions and operators

Comparison operators

OperatorExpression & DescriptionExample
=operand1 = operand2
Equal.
TRUE if the operands separated by = have the same value.
1 = 1 → t
'1' = 1 → t
'a' = 'b' → f
(1, 0) = (1, 1) → f
('a', 'b') = ('a', 'b') → t
<>
!=
operand1 <> operand2 or operand1 != operand2
Not equal.
TRUE if the operands separated by <> or != have different values.
1 <> 1 → f
'1' != 1 → f
'a' != 'b' → t
(1, 0) <> (1, 1) → t
('a', 'b') != ('a', 'b') → f
<operand1 < operand2
Less than.
TRUE if operand1 is less than operand2.
0 < 1 → t
1 < 1 → f
<=operand1 <= operand2
Less than or equal to.
TRUE if operand1 is less than or equal to operand2.
1 <= 1 → t
1 <= 0 → f
>operand1 > operand2
Greater than.
TRUE if operand1 is greater than operand2.
1 > 0 → t
1 > 1 → f
>=operand1 >= operand2
Greater than or equal to.
TRUE if operand1 is greater than or equal to operand2.
1 >= 1 → t
0 >= 1 → f

Comparison predicate

OperatorExpression & DescriptionExample
IS DISTINCT FROMoperand1 IS DISTINCT FROM operand2
Equal (null comparible).
TRUE if operand1 is not equal to operand2.
1 IS DISTINCT FROM NULL → t
1 IS DISTINCT FROM 1 → f
IS NOT DISTINCT FROMoperand1 IS NOT DISTINCT FROM operand2
Not equal (null comparible).
TRUE if operand1 is equal to operand2.
1 IS NOT DISTINCT FROM NULL → f
BETWEEN ... AND ...operand BETWEEN min AND max
Between (inclusive range).
TRUE if the operand is greater than or equal to min and less than or equal to max.
1 BETWEEN 0 AND 1 → t
'c' BETWEEN 'a' AND 'b' → f
NOT BETWEEN ... AND ...operand NOT BETWEEN min AND max
Not between (inclusive range).
TRUE if the operand is less than min and greater than max.
1 NOT BETWEEN 0 AND 1 → f
IN()operand IN (value,...)
Whether a value is equal to any of the values you specify.
TRUE if the operand is equal to one of the specified expressions/values. NULL if the operand is null or if the operand is not in the specified expressions/values that contain a null.
1 IN (0,1,2,3) → t
'a' IN ('ab','b','c','d') → f
null IN (null, 3, 0.5*2, min(v1)) → NULL
99 IN (null, 3, 2) → NULL
NOT IN()operand NOT IN (value,...)
Whether a value is not equal to any of the values you specify.
TRUE if the operand is not equal to any specified expressions/values.
1 NOT IN (0,1,2,3) → f
IS TRUEboolean IS TRUE
Whether a boolean expression is true.
true IS TRUE → t
null::boolean IS TRUE → f
IS NOT TRUEboolean IS NOT TRUE
Whether a boolean expression is false or unknown.
true IS NOT TRUE → f
null::boolean IS NOT TRUE → t
IS FALSEboolean IS FALSE
Whether a boolean expression is false.
true IS FALSE → f
null::boolean IS FALSE → f
IS NOT FALSEboolean IS NOT FALSE
Whether a boolean expression is true or unknown.
true IS NOT FALSE → t
null::boolean IS NOT FALSE → t
IS NULLvalue IS NULL
Whether a value is null.
1 IS NULL → f
IS NOT NULLvalue IS NOT NULL
Whether a value is not null.
1 IS NOT NULL → t
IS UNKNOWNboolean IS UNKNOWN
Whether a boolean expression returns an unknown value (typically represented by a null).
null IS UNKNOWN → t
false IS UNKNOWN → f
IS NOT UNKNOWNboolean IS NOT UNKNOWN
Whether a boolean expression returns true or false.
true IS NOT UNKNOWN → t
null IS NOT UNKNOWN → f

Help us make this doc better!