11

For what x is

The expression x IS NOT NULL is not equal to NOT(x IS NULL), as is the case in 2VL

(quote from this answer, which is quoting Fabian Pascal Practical Issues in Database Management - A Reference for the Thinking Practitioner -- near the end of that answer)

My guess is when x IS NULL is NULL, but I cannot guess when that would be (i.e. I haven't checked the SQL standard). This guess was incorrect.

Community
  • 1
  • 1
Mark Hurd
  • 10,665
  • 10
  • 68
  • 101

2 Answers2

15

From what I've read, Fabian Pascal is not referring to a scalar value in comparison to Null but the rarely implemented ROW type. In the standard, the idea was that you could compare a table (of rows) using IS NULL to determine if all values were set to the NULL value. Thus, X IS NULL would imply all values were set to the NULL value, X IS NOT NULL would mean no values were set to the NULL value and NOT (X IS NULL) would mean that not all values were set to the NULL value or, said another way, there existed at least one value not set to NULL. Granted, I'm treading lightly on the shoulders of giants here, but that's how I interpret his statement.

Thomas
  • 63,911
  • 12
  • 95
  • 141
  • 1
    Both Date and Pascal can be faulted for not spelling this out really clearly. If x is a row with a mixture of nulls and non-nulls, it has a weird mixed up status. – Jonathan Leffler May 19 '10 at 05:01
10

x IS NULL will never be NULL so they are the same for all x

Truth table ahoy:

+--------+-------------+---------+--------------+
|   x    |x IS NOT NULL|x IS NULL|NOT(x IS NULL)|
+--------+-------------+---------+--------------+
|NULL    |    FALSE    |  TRUE   |    FALSE     |
|NOT NULL|    TRUE     |  FALSE  |    TRUE      |
+--------+-------------+---------+--------------+

Note that columns two and four are identical for all potential values of x (either NULL or NOT NULL)

Daniel DiPaolo
  • 55,313
  • 14
  • 116
  • 115
  • Given Thomas's answer, it looks like my `x IS NULL` *is* NULL is a red herring. Sorry to lead you astray. – Mark Hurd May 18 '10 at 17:28