Functions and operators
Comparison functions and operators
Functions and operators
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 <br/>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 Not equal (null comparable). 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 Equal (null comparable). 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 |
| ANY() | expression operator ANY (array expression) Compares a value with the element in a set or array of values. The right-hand side is a parenthesized expression and must yield an array value. The left-hand expression is evaluated and compared to each element of the array using the given operator, which must yield a Boolean result. TRUE if any true result is obtained. FALSE if no true result is found (including the case where the array has zero elements). NULL if the array expression yields a null array, or if the left-hand expression yields null (assuming a strict comparison operator), or if the right-hand array contains any null elements and no true comparison result is obtained (assuming a strict comparison operator). | 5 = ANY(ARRAY[1, 3, 5, 7]) → t |
| SOME() | expression operator SOME (array expression) SOME is a synonym for ANY. | 5 = SOME(ARRAY[1, 3, 5, 7]) → t |
| ALL() | expression operator ALL (array expression) Compares a value with the element in a set or array of values. The right-hand side is a parenthesized expression and must yield an array value. The left-hand expression is evaluated and compared to each element of the array using the given operator, which must yield a Boolean result. TRUE if all comparisons yield true (including the case where the array has zero elements). FALSE if any false result is found. NULL if the array expression yields a null array, or if the left-hand expression yields null (assuming a strict comparison operator), or if the right-hand array contains any null elements and no false comparison result is obtained(assuming a strict comparison operator). | 5 > ALL(ARRAY[1, 3, 5, 7]) → 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 |
Was this page helpful?