SQL and NULL

Yesterday this came up again: a client was using a query with something like:

SELECT xxx FROM yyy WHERE zzz=NULL

This does not work as expected. This query will always return zero rows. The correct way is:

SELECT xxx FROM yyy WHERE zzz IS NULL

Similarly “WHERE zzz<>NULL” should be written as “WHERE zzz IS NOT NULL.” This mistake happens very often; there must be a lot of queries being used that are just wrong and giving wrong results as a consequence of this SQL quirk. This is of course a language issue: the expression looks harmless and does not give an error message.  

Notes:

• In Access there is also an alternative: use the function isnull()
• In SQL Server there is an option ANSI_NULLS that can be set to off to allow comparisons against NULL. This feature will be removed from a future version of SQL Server (http://msdn.microsoft.com/en-us/library/ms188048.aspx).