# 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` 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 |