Why SQL comparison with <> doesn't return the row with NULL value

Question

Assume a table X like so:

   A   |   B
----------------
   2       pqr
   3       xyz
 *NULL*    abc

When I execute a query like:

SELECT * 
FROM X 
WHERE A <> 2

I expect a result set like this:

   A   |   B
----------------
   3       xyz
 *NULL*    abc

But to my surprise, I get a result set like this :

   A   |   B
----------------
   3      xyz

Why does the row with NULL value not appear in the result set?

Can somebody explain this behavior ?

Answer 1

The ANSI-92 SQL Standard states that if one of the operands is NULL, the result of the comparison is "UNKNOWN" - not true or false.

For a good look at how NULLs work in SQL, see 4 Simple Rules for Handling SQL NULLs

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE X
    ([A] int, [B] varchar(3))
;

INSERT INTO X
    ([A], [B])
VALUES
    (2, 'pqr'),
    (3, 'xyz'),
    (NULL, 'abc')
;

Query 1:

SELECT * 
FROM X 
WHERE A IS NULL OR A <> 2

Results:

|      A |   B |
|--------|-----|
|      3 | xyz |
| (null) | abc |

Answer 2

Because of the null <> 2 return unknown in three-state logic which in predicates treats as false.

Answer 3

NULL is not compared using = or <>. Check this NULL COMPARISON article. NULLS are compared as IS NULL or IS NOT NULL.

Answer 4

You need to handle NULL ... if any operand is NULL, the result of a comparison is NULL.

if you try something like this:

SELECT * FROM X WHERE isnull(A,'NULL') <> 2

you may get the results you are expecting (if A is a varchar field) ...

If A is a number field, you could try this:

SELECT * FROM X WHERE isnull(A,0) <> 2

Answer 5

If you are a visual learner, run this query:

SELECT
  a,
  b,
  CASE
    WHEN a <> b THEN 'a <> b'
    WHEN a  = b THEN 'a = b'
    ELSE             'neither'
  END
FROM
  (VALUES (0),(1),(NULL)) a(a),
  (VALUES (0),(1),(NULL)) a(b)