SQL 2005+

SQL Server Comparison Operators

by Richard Carr, published at http://www.blackwasp.co.uk/SQLComparisonOperators.aspx

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 >=.

Comparison Operator Examples

The examples in this section are queries that may be executed against the JoBS database. This is the database that has been generated and populated with data throughout the course of the tutorial. If you do not have an up to date version of the JoBS database, you can create one by downloading and executing the script available via the link at the top of this page.

Example Queries

The first example query uses the equality operator in the WHERE clause. This returns the rows from the Contracts table where the contract is to be renewed automatically.

SELECT * FROM Contracts WHERE RenewAutomatically = 1

The above query can be reversed by selecting all of the rows that do not represent automatically renewing contracts. This is achieved by either of the following queries, which use the standard and non-standard "not equal to" operators.

SELECT * FROM Contracts WHERE RenewAutomatically <> 1
SELECT * FROM Contracts WHERE RenewAutomatically != 1

The next six queries perform a comparison on the ContractValue column. The first query returns contracts with a value lower that £280. Compare this to the third and fourth queries, each of which returns contracts of £280 or less. Similarly, the second query returns contracts worth more than £280 and the fifth and sixth return contracts worth £280 or more.

SELECT * FROM Contracts WHERE ContractValue < 280
SELECT * FROM Contracts WHERE ContractValue > 280
SELECT * FROM Contracts WHERE ContractValue <= 280
SELECT * FROM Contracts WHERE ContractValue !> 280
SELECT * FROM Contracts WHERE ContractValue >= 280
SELECT * FROM Contracts WHERE ContractValue !< 280

Comparison Operators and NULL Values

The results returned from a query that is filtered using a comparison operator contain only row where the expression returns true. This excludes both false results and unknown results. Unknown results are often generated because one of the values being compared is NULL. As we have seen previously, NULL indicates an unknown value, so it is logical that comparisons such as "15 > NULL" or "NULL !< NULL" would return an unknown result.

When comparing two NULL values using the equality operator, the result is usually unknown. However, it is possible to change the ANSI_NULLS setting so that "NULL = NULL" returns true. Generally you should only use this setting for backwards-compatibility. For new databases, it is advisable to leave this setting disabled and use the IS NULL comparison to check for NULL values.

One of the effects of the unknown outcome for comparisons is that executing two apparently opposing queries and combining the results does not necessarily provide all of the available results from a table. To demonstrate, execute the following three queries. The first returns all of the rows from the Contracts table. You should see twenty-one results. The second query returns all of the rows where the contract is marked to be renewed automatically and the third query returns those that are marked as "not for automatic renewal". These queries return thirteen and six rows respectively for a total of nineteen results. The two missing rows are those with a NULL value in the RenewAutomatically column.

SELECT count(*) FROM Contracts
SELECT count(*) FROM Contracts WHERE RenewAutomatically = 1
SELECT count(*) FROM Contracts WHERE RenewAutomatically != 1

Operator Precedence

We can now add the comparison operators to the table of precedence.