Why Inequality with NULL Doesn't Always Return True

When comparing NULL values with the inequality operator ("!="), it's often assumed that the result should be true, since NULL is not equal to itself. However, in certain contexts like SQL, this assumption is not valid.

SQL's Ternary Logic

In SQL, NULL represents an unknown value. This introduces a third logical state beyond true and false, known as "unknown." As a result, inequality comparisons with NULL follow ternary logic, where the outcome can be one of three options:

• True
• False
• Unknown

Example:

Consider the following statement:

WHERE (A  B)

If A and B are both NULL, the above expression returns "unknown" because there's no way to determine if they are truly unequal or if they are both unknown.

Implications for Inequality Checking

This ternary logic dictates that a simple inequality check with NULL may not always return true. To ensure accurate results, explicitly checking for NULL conditions using "IS NULL" and "IS NOT NULL" is necessary.

For example, the following expression correctly handles inequality comparisons with NULL:

WHERE ((A  B) OR (A IS NOT NULL AND B IS NULL) OR (A IS NULL AND B IS NOT NULL))

By incorporating these explicit checks, the database can determine the correct logical outcome (true, false, or unknown) for all possible cases involving NULL values.