5

I have a table with a nullable varchar column. When selecting rows and specifying that I want rows with a value not equal to a given string, it does NOT return the rows where the value is null.

For example:

## if `value` is null, that row is ignored
SELECT * FROM test_table WHERE value != 'some string'

I'd like to understand why that's happening.

Example: http://sqlfiddle.com/#!2/83f0d/1

just.another.newbie
  • 313
  • 2
  • 13

1 Answers1

8

In ANSI SQL, NULL is neither equal to nor unequal to any value, including itself.

NULL = 'foo'
NULL != 'foo'
NULL = NULL
NULL != NULL

all evaluate to NULL. To test for nullness you must use is null or is not null in your query.

dg99
  • 5,456
  • 3
  • 37
  • 49
  • Arguably, none of the above expressions evaluates to `FALSE`. They evaluate to `NULL`, which is "untrue" but is equally "unfalse." – Michael - sqlbot Jan 15 '14 at 11:37
  • @Michael-sqlbot: I'd still say it evaluates to `FALSE` because comparison must return a `BOOLEAN`. If it's not `TRUE`, it has to be `FALSE`. – Fr0zenFyr Oct 06 '17 at 12:25
  • @Fr0zenFyr Your statement *comparison must return a `BOOLEAN`* is not true in ANSI SQL. – dg99 Oct 06 '17 at 20:56
  • Well, then interestingly, it seems the standard (behind the scene) uses a `strict` comparison which has three predicates - `TRUE`, `FALSE` and `NULL` (equivalent to `void`/`null`/`undefined` in various programming languages - I know it sounds silly to compare SQL to programming language but SQL is the back-end model for such languages, so it might make sense in some cases). Found [this post (SO answer)](https://stackoverflow.com/a/9825992/1369473) which illustrates the difference with nice truth tables to help understand the simple comparison vs `IS` operator in (`ANSI`) SQL. – Fr0zenFyr Oct 07 '17 at 12:22
  • Thanks for the simple explanation. Helped me debug an stored procedure and get it to work as expected. – Giacomo1968 May 24 '18 at 20:48