Testing for Null Values
From Wiki
How do you test for NULL values? First create this table
- CREATE TABLE testnulls (ID INT)
- INSERT INTO testnulls VALUES (1)
- INSERT INTO testnulls VALUES (2)
- INSERT INTO testnulls VALUES (null)
People new to SQL will usually try to run the following query
- SELECT * FROM testnulls WHERE ID = NULL
As you can see nothing is returned. However there is a setting that you can use to make it work
Run this
- SET ANSI_NULLS OFF
Now run the query again
- SELECT * FROM testnulls WHERE ID = NULL
See, it works. However this is NOT recommended and as a matter of fact this has been deprecated So let's set it back to the default.
- SET ANSI_NULLS ON
This is how you properly test for NULLS
- SELECT * FROM testnulls WHERE ID IS NULL
You could also use the functions ISNULL or COALESCE with a non existing value in the table, this however is not recommended because a function on the left side of the operator will result in non optimized query (scan)
- SELECT * FROM testnulls WHERE ISNULL(ID,-66666) = -66666
- SELECT * FROM testnulls WHERE COALESCE(ID,-66666) = -66666
Contributed by: --SQLDenis 16:23, 30 May 2008 (GMT)
Part of SQL Server Programming Hacks
Section NULLS
