Comparison functions and operators

Comparison operators

Operator	Expression & Description	Example
=	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

Operator	Expression & Description	Example
IS DISTINCT FROM	operand1 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 FROM	operand1 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 TRUE	boolean IS TRUE Whether a boolean expression is true.	true IS TRUE → t null::boolean IS TRUE → f
IS NOT TRUE	boolean IS NOT TRUE Whether a boolean expression is false or unknown.	true IS NOT TRUE → f null::boolean IS NOT TRUE → t
IS FALSE	boolean IS FALSE Whether a boolean expression is false.	true IS FALSE → f null::boolean IS FALSE → f
IS NOT FALSE	boolean IS NOT FALSE Whether a boolean expression is true or unknown.	true IS NOT FALSE → t null::boolean IS NOT FALSE → t
IS NULL	value IS NULL Whether a value is null.	1 IS NULL → f
IS NOT NULL	value IS NOT NULL Whether a value is not null.	1 IS NOT NULL → t
IS UNKNOWN	boolean 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 UNKNOWN	boolean IS NOT UNKNOWN Whether a boolean expression returns true or false.	true IS NOT UNKNOWN → t null IS NOT UNKNOWN → f