Law of the Excluded Middle - Null (SQL) - Wikipedia
Law of the Excluded Middle
SQL allows three logical choices, true, false, and unknown, which means that SQL necessarily ignores the law of the excluded middle. Put simply the Law of the Excluded Middle essentially states that when given any Boolean result, the opposite of the result can be obtained by applying the logical “not” operator. This does not apply to SQL nulls, however. Under the precepts of the law of the excluded middle, a Boolean expression like the following can be simplified:
SELECT * FROM stuff WHERE ( x = 10 ) OR NOT ( x = 10 );
The law of the excluded middle allows for simplification of the WHERE clause predicate, which would result in a statement like the following:
SELECT * FROM stuff;
This will not work in SQL, since the x column could contain nulls which would result in some rows not being returned.
Actually:
SELECT * FROM stuff;
-- is (because of 3VL) equivalent to:
SELECT * FROM stuff WHERE ( x = 10 ) OR NOT ( x = 10 ) OR x IS NULL;
Thus, to correctly simplify the first statement in SQL requires that we return all rows in which x is not null.
SELECT * FROM stuff WHERE x IS NOT NULL;
While ignoring the law of the excluded middle does introduce additional complexity to SQL logic, attempts to apply this rule to SQL’s 3VL results in a false dichotomy.