SQL 2005+

SQL Server Comparison Operators (2)

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