Usually getting queries right with SQL Server comes down to handling NULL correctly.
For instance, on this table:
MYTABLE
| 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’:
But in fact the results are these:
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)
Lesson learned: always take into account NULL when trying to formulate your SQL comparisons well.
–jeroen
The Wiert Corner - irregular stream of stuff 