Usually getting queries right with SQL Server comes down to handling NULL correctly.
For instance, on this table:
| ID | LAST_NAME |
|---|---|
| 6 | COUNT(*) |
| 1 | FOO |
| 2 | BAR |
| 3 | FOO |
| 4 | **NULL** |
| 5 | BAR |
| 6 | FOO |
What are the results of these SELECT statements
SELECT COUNT (*) FROM MYTABLE SELECT COUNT (*) FROM MYTABLE WHERE (LAST_NAME = 'FOO') SELECT COUNT (*) FROM MYTABLE WHERE (LAST_NAME <> 'FOO') SELECT COUNT (*) FROM MYTABLE WHERE NOT (LAST_NAME = 'FOO')
You might think they are like these, as LASTNAME <> ‘FOO’ looks like the inverse of LASTNAME = ‘FOO’:
- 6
- 3
- 3
- 3
But in fact the results are these:
- 6
- 3
- 2
- 2
When you look closely to the SQL statements below, you will see that the inverse of EQUALS contains an IS NULL, the same for the inverse of NOT EQUALS:
SELECT COUNT (*) FROM MYTABLE -- inverse of NOT EQUALS SELECT COUNT (*) FROM MYTABLE WHERE (LAST_NAME = 'FOO') OR (LAST_NAME IS NULL) SELECT COUNT (*) FROM MYTABLE WHERE (LAST_NAME <> 'FOO') SELECT COUNT (*) FROM MYTABLE WHERE (LAST_NAME = 'FOO') -- inverse of EQUALS SELECT COUNT (*) FROM MYTABLE WHERE (LAST_NAME <> 'FOO') OR (LAST_NAME IS NULL) -- inverse of EQUALS SELECT COUNT (*) FROM MYTABLE WHERE (NOT (LAST_NAME = 'FOO')) OR (LAST_NAME IS NULL)
- 6
- 4
- 2
- 3
- 3
- 3
Lesson learned: always take into account NULL when trying to formulate your SQL comparisons well.
–jeroen
The Wiert Corner - irregular stream of stuff 