SQL 2005+

SQL Server Comparison Operators

The thirty-fourth part of the SQL Server Programming Fundamentals tutorial examines the comparison operators. These allow the creation of Boolean expressions that determine if two values are equal or, if not, which is the larger or smaller.

Comparison Operators

This article continues the examination of operators in Transact-SQL (T-SQL). In this instalment we will examine the nine comparison operators. These operators can be used to create a Boolean expression that compares the two values. A Boolean expression in T-SQL is one that returns either true or false, or unknown in some situations. This makes such expressions useful in WHERE clauses or conditional statements.

The comparison operators compare two values to check if they are equal or to determine whether one value is larger, or smaller, than the other. The actual comparison is based upon the operator that is selected. The operators can be used with most types of data, with the exception of text, ntext or image types.

ISO Standard Operators

The comparison operators can be grouped into ISO standardised operators and non-standard variations. There are six ISO standard operators, as follows:

• Equality (a = b). The equality operator returns true if the two compared values (a and b) are equal.
• Less Than (a < b). This operator returns true when 'a' is smaller than 'b'.
• Greater Than (a > b). This operator returns true when 'a' is larger than 'b'.
• Less Than Or Equal (a <= b). This operator returns true when 'a' is smaller than 'b' or the two values are equal.
• Greater Than Or Equal (a >= b). This operator returns true when 'a' is larger than 'b' or the two values are equal.
• Not Equal (a <> b). This operator returns true if the two compared values (a and b) are not equal.

Non-Standard Operators

The second set of operators contains the non-standard variations. It is acceptable to use these operators but you should consider that statements that you create might not be supported on other ISO-compliant database management systems.

• Not Equal (a != b). This operator returns true if the two compared values (a and b) are not equal. It provides the same functionality as <>.
• Not Greater Than (a !> b). This operator returns true when 'a' is smaller than 'b' or the two values are equal. It provides the same functionality as <=.
• Not Less Than (a !< b). This operator returns true when 'a' is larger than 'b' or the two values are equal. It provides the same functionality as >=.