CODEWITHSUNDEEP
sqlnull
Mark Hurd
Mark Hurd

Reputation: 10931

When is `x IS NOT NULL` not the same as `NOT(x IS NULL)`

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.

Upvotes: 11

Views: 1240

Answers (2)

Thomas
Thomas

Reputation: 64674

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.

Upvotes: 15

Daniel DiPaolo
Daniel DiPaolo

Reputation: 56418

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)

Upvotes: 10

Related Questions