SQL Server Where [column] <>

Question

This is probably something daft, so I'll apologise in advance.

Say I have two tables:

|    TABLE 1    |    |      TABLE 2      |
-----------------     ---------------------
|RESULT | REASON|    |ID    | DESCRIPTION|
-----------------    ---------------------
|  1    | NULL  |    | A    | NO INTEREST|
|  2    |   A   |    | B    | CALL CLOSED|
|  2    |   B   |    | C    | DECEASED   |
|  1    | NULL  |    ---------------------
-----------------

So, REASON can only have a value if the RESULT is 2. Otherwise it is NULL.

Now suppose I run the following:

SELECT
    t1.RESULT, t2.DESCRIPTION
FROM
    [TABLE 1] t1 LEFT OUTER JOIN
    [TABLE 2] t2 ON t1.RESULT = t2.ID

We would get the following:

------------------------
| RESULT | DESCRIPTION |
------------------------
|   1    |     NULL    |
|   2    | NO INTEREST |
|   2    | CALL CLOSED |
|   1    |     NULL    | 
------------------------

Now what I have found is that if I then add a WHERE clause like so:

SELECT
    t1.RESULT, t2.DESCRIPTION
FROM
    [TABLE 1] t1 LEFT OUTER JOIN
    [TABLE 2] t2 ON t1.RESULT = t2.ID
WHERE
    t2.DESCRIPTION <> 'CALL CLOSED'

I for some reason end up with the following:

------------------------
| RESULT | DESCRIPTION |
------------------------   
|   2    | NO INTEREST |
------------------------

Adding the where clause would seem to exclude all the records with result 1 ergo, all the records with NULL reason values/description values.

Am I missing something daft here?

Answer 1

In SQL, NULL is not an "empty value" or something like that, it is a lack of a value - any operation on it which expects a value will return unknown. Since unknown is not true, every row evaluated for a condition returning unknown in the where clause will not be returned in the query.

Think of your condition using the <> operator as asking "does description have a value that is different than 'CALL CLOSED'" - the answer is no, since it doesn't have a value at all.

Once we've established that, handling it is simple - just check for NULL explicitly:

SELECT
    t1.RESULT, t2.DESCRIPTION
FROM
    [TABLE 1] t1 LEFT OUTER JOIN
    [TABLE 2] t2 ON t1.RESULT = t2.ID
WHERE
    t2.DESCRIPTION IS NULL OR t2.DESCRIPTION <> 'CALL CLOSED'