SQL Server inverse of (equals) is ((not equals) or (is NULL)
Posted by jpluimers on 2011/12/07
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
[sourcecode language=”sql”]
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’)[/sourcecode]
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:
[sourcecode language=”sql”]
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)[/sourcecode]
- 6
- 4
- 2
- 3
- 3
- 3
Lesson learned: always take into account NULL when trying to formulate your SQL comparisons well.
–jeroen
Leave a Comment