T-SQL: Compare NULL by value, with EXCEPT

In this technical blogpost one of our developers, Peter de Heer, demonstrates how equality matches, that treat NULL values as if they were an actual value, are typically handled. He then shows a not well known, but much better technique for doing this and explains where and why to use it. We hope to give you some valuable insight and help you understand SQL a bit more!

Standard SQL behaviour is for NULL to be incomparable to every possible value and NULL itself. To properly detect NULL, the standard defines the specialized IS NULL and IS NOT NULL comparison operators.

Other comparison operators, like =, <>, !=, >, <, >=, <= always fail to match a NULL!

Executing this code demonstrates = operator behaviour for different situations.

This is what the SQL server responds with.

When in the select statements any of the compared variables contained a NULL, no match happened in the where clause and the selected count aggregate function returned 0 as a result.

To make NULL match another NULL, the code must be rewritten as follows:

This adds a second/alternative comparison that explicitly handles the NULL matching. This introduces the or operator for considering the alternative and several round brackets to make the logic work properly.

This well known technique quickly results in hard to read code, especially when multiple such comparisons happen simultaneously and mistakes can easily be made.

Using or operators in queries can also slow them down quite a bit.

The EXCEPT operator was designer for comparing sets in literal way. It thus treats NULL as a distinct value and NULL thus equals another NULL. Exactly what is needed here. It also handles multiple comparisons in one expression, keeping code nice, clean and compact!

The original code comparing NULL values can be rewritten to this.

A match is counted when there are no differences in the outcome of select@v1 and select @v2. When the need is to instead count differences, just change not exists into exists.

Another benefit of using the except operator here is that code can de extended, just by adding columns to the select statements surrounding the except operator.

This technique leads to improved query execution times compared to the traditional way.

The primary use is in conditional updating of large datasets. Because conditional updating of a large number of records reduces the load on the database transaction log and it eliminates needles writes and other associated costly database operations (like constraint checks).

Expect to see/use this in merge statements and manually coded equivalents.