Why Does `A B` Suffice for Inequality Comparisons with Nullable Columns in SQL?

Null Inequality Paradox Resolved

In SQL, testing equality between two nullable columns requires additional checks to handle the case where both columns are NULL. This is because NULL, representing an unknown value, cannot be compared directly to another NULL. However, when testing inequality, a simpler approach can be used.

Inequality with Nullable Columns

Initially, it was assumed that testing inequality between nullable columns would require an intricate condition:

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

However, in many SQL implementations, such as Informix 11.5, a simpler condition suffices:

WHERE (A <> B)

Understanding the Behavior

This behavior arises from ternary logic, where NULL is treated as an unknown value. Consider the following cases:

• If both A and B are known (non-NULL), the inequality test is straightforward.
• If either A or B is NULL, the result is indeterminate. In ternary logic, an unknown value is not equal to any other value, including itself. Therefore, the expression (A = B) returns unknown when either operand is NULL.
• If both A and B are NULL, the inequality test is also unknown. This is because NULL is neither equal nor non-equal to itself.

Simplified Inequality Condition

Thus, the simplified (A <> B) condition works correctly because it does not attempt to compare NULL values directly. Instead, it relies on the ternary logic principle that unknown values are not equal to anything.

Conclusion

When testing inequality between nullable columns, it is sufficient to use the simple condition (A <> B). This is because NULL, as an unknown value, cannot be directly compared to other values, including itself. Employing the ternary logic approach where NULL is considered unknown simplifies the expression and ensures accurate results.

The above is the detailed content of Why Does `A B` Suffice for Inequality Comparisons with Nullable Columns in SQL?. For more information, please follow other related articles on the PHP Chinese website!