0

I guess I don't fully understand the meaning of NULL in SQL. I ran this SQL and I expected to see 1 as the output but I didn't see that:

select 1 where NULL <> -1;

Isn't NULL and -1 different? Can anyone explain why this clause of "NULL <> -1" is FALSE?

kee
  • 10,969
  • 24
  • 107
  • 168

2 Answers2

0

NULL Is not a value and therefore cannot be compared with any other value and get any other result than null. Docs. And how to compare nulls.

Joe
  • 3,337
  • 1
  • 14
  • 11
  • I don't know what RDBMS the OP is using, but I don't think it's Oracle. Oracle shouldn't be accepting the OP's syntax, IIRC, it requires `from`. –  Sep 17 '16 at 18:38
  • @hvd: the explanation on how to deal with NULL values in expressions is still valid for any SQL dialect –  Sep 17 '16 at 19:34
  • @a_horse_with_no_name Generally yes, but the linked documentation contains Oracle-specific details as well, at least the warning about zero-length strings and the mention of some non-standard functions. That won't be useful to the OP. –  Sep 17 '16 at 19:38
0

NULL means unknown. So with where NULL <> -1 you want to know whether the unknown value equals -1. The DBMS does not know (of course), so the result of the expression is neither TRUE nor FALSE; it is NULL.

Only rows for which the WHERE clause results in TRUE are selected. As your expression doesn't result in TRUE but in NULL, there is no row selected.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • In fact the result of a comparison with NULL results in UNKNOWN, not NULL. – dnoeth Sep 17 '16 at 20:55
  • @dnoeth: No, NULL *is* the database name for unknown. Try `select 1 where (null = -1) is null`. (Here is a test on PostgreSQL: http://sqlfiddle.com/#!15/9eecb7db59d16c80417c72d1e1f4fbf1/11585.) – Thorsten Kettner Sep 17 '16 at 20:59
  • A column can be `NULL`, but never the result of a comparison, that's why there's `IS UNKNOWN` in Standard SQL (and PostgreSQL, too). See the pages around https://books.google.de/books?id=efe2wB_DfxYC&pg=PA219&lpg=PA219&dq=%22Truth+table+for+the+AND+boolean+operator%22&source=bl&ots=DfT9W4xLGF&sig=Yee-tSw5oQjTfoazWtRYEzuXBMM&hl=de&sa=X&ved=0ahUKEwjQ6ICmrpfPAhWBpiwKHVNQBo0Q6AEIJTAB#v=onepage&q=%22Truth%20table%20for%20the%20AND%20boolean%20operator%22&f=false – dnoeth Sep 17 '16 at 21:53
  • @dnoeth: Thank you, I didn't know that. What I knew was that an expression results in a value, for instance in a string value (e.g. a || b) or a numeric value (e.g. a + b) or a boolean value (e.g. a or b). I didn't know that the boolean datatype is thus supposed to hold either `TRUE` or `FALSE` or `UNKNOWN`; I thought it were `TRUE` or `FALSE` or `NULL`. But I've read it up in a SQL 2003 draft; and it shows that it really shall be `UNKNOWN`, but it is allowed for a DBMS to use `NULL` instead. I suppose that `UNKNOWN` is supposed to behave exactly like `NULL` anyway. – Thorsten Kettner Sep 17 '16 at 23:51