SQL comparison operators

Comparison operators evaluate the relationship between the left and right operands and returns true or false.

=

The = operator compares the left and right operands and, if equal, returns true. Otherwise returns false.

SELECT 123 = 123

!=, <>

The != and <> operators compare the left and right operands and, if not equal, returns true. Otherwise returns false.

SELECT 123 != 456

SELECT 123 <> 456

>

The > operator compares the left and right operands and, if the left operand is greater than the right operand, returns true. Otherwise returns false.

SELECT 3 > 2

>=

The >= operator compares the left and right operands and, if the left operand is greater than or equal to the right operand, returns true. Otherwise returns false.

SELECT 3 >= 2

<

The < operator compares the left and right operands and, if the left operand is less than the right operand, returns true. Otherwise returns false.

SELECT 1 < 2

<=

The <= operator compares the left and right operands and, if the left operand is less than or equal to the right operand, returns true. Otherwise returns false.

SELECT 1 <= 2

IS DISTINCT FROM

The IS DISTINCT FROM operator is a NULL-safe operator that returns true if both operands are not equal; otherwise, it returns false. This operator guarantees the result of a comparison is true or false and not an empty set.

SELECT 0 IS DISTINCT FROM NULL

IS NOT DISTINCT FROM

The IS NOT DISTINCT FROM operator is a NULL-safe operator that returns true if both operands are equal or NULL; otherwise, it returns false. This operator negates IS DISTINCT FROM.

SELECT NULL IS NOT DISTINCT FROM NULL

~

The ~ operator compares the left string operand to the right regular expression operand and, if it matches (case-sensitive), returns true. Otherwise returns false.

SELECT 'abc' ~ 'a.*'

~*

The ~* operator compares the left string operand to the right regular expression operand and, if it matches (case-insensitive), returns true. Otherwise returns false.

SELECT 'Abc' ~* 'A.*'

!~

The !~ operator compares the left string operand to the right regular expression operand and, if it does not match (case-sensitive), returns true. Otherwise returns false.

SELECT 'abc' !~ 'd.*'

!~*

The !~* operator compares the left string operand to the right regular expression operand and, if it does not match (case-insensitive), returns true. Otherwise returns false.

SELECT 'Abc' !~* 'a.*'