Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

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.

like image 564
Mark Hurd Avatar asked May 18 '10 16:05

Mark Hurd


People also ask

Is is NOT NULL same as <> null?

The IS NULL condition is satisfied if the column contains a null value or if the expression cannot be evaluated because it contains one or more null values. If you use the IS NOT NULL operator, the condition is satisfied when the operand is column value that is not null, or an expression that does not evaluate to null.

Is != And is NOT NULL the same in SQL?

<> is Standard SQL-92; != is its equivalent. Both evaluate for values, which NULL is not -- NULL is a placeholder to say there is the absence of a value. Which is why you can only use IS NULL / IS NOT NULL as predicates for such situations.

What is null and is NOT NULL?

NULL means you do not have to provide a value for the field... NOT NULL means you must provide a value for the fields.

Is null equal to null?

Because null represents a lack of data, a null cannot be equal or unequal to any value or to another null. However, Oracle considers two nulls to be equal when evaluating a DECODE function.


2 Answers

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.

like image 171
Thomas Avatar answered Oct 10 '22 09:10

Thomas


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)

like image 41
Daniel DiPaolo Avatar answered Oct 10 '22 10:10

Daniel DiPaolo