1

I have a table with the following data:

id | value
 1 | 1
 2 | NULL
 3 | NULL

My query:

SELECT * FROM table WHERE value != '1'

Result is empty.

Why does the query do not return row 2 and 3?

Roman
  • 2,530
  • 2
  • 27
  • 50
  • 1
    NULL is equal to nothing, not even itself. – Alex K. Mar 04 '16 at 14:26
  • 1
    [SQL Fiddle](http://sqlfiddle.com/#!9/b5718/2/0) 2 and 3 are not returned in this example... My understanding was NULL will always evaluate to false when a comparison is made to it unless the null is evaluated using is null logic – xQbert Mar 04 '16 at 14:28

1 Answers1

7

When value is equal to NULL the predicate becomes: NULL != '1'. This evaluates to NULL, not true, as one might expect.

This is the so-called three valued logic of SQL: an expression evaluates to either true, or false, or NULL.

To correctly check against NULL you need to use IS NULL: 

WHERE (value != '1') OR (value IS NULL)
Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98