Why Does `NULL != NULL` Return FALSE in SQL?

Why NULL Inequality is False

The application of SQL's ternary logic plays a crucial role in understanding why NULL is not equal to NULL. In ternary logic, NULL represents an unknown value, rather than a specific value. This concept guides the evaluation of equality and inequality tests.

When evaluating equality, two NULL values are considered neither equal nor unequal. This is because the state of being unknown cannot be definitively compared to another unknown state. Thus, the expression (NULL = NULL) returns NULL, signifying the lack of a clear answer.

In the case of inequality tests, (NULL != NULL) also returns FALSE. This is because ternary logic dictates that an unknown value cannot be definitively stated to be unequal to another unknown value. Hence, it remains unknown whether they are equal or not.

Implication for Inequality Checks

The above discussion clarifies that the expression (A != B) is sufficient for testing inequality, even if either A or B is NULL. This is because the ternary logic rules dictate that any operation involving a NULL value will remain unknown. Therefore, the expression (A != B) returns FALSE if both A and B are NULL, indicating the lack of a definitive answer.

If you explicitly require a TRUE result when both A and B are NULL, you can use the more verbose expression:

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

However, it's important to note that this condition caters to a specific use case where a TRUE result is desired for NULL values. In most cases, the simpler expression (A != B) suffices as it follows ternary logic principles.

The above is the detailed content of Why Does `NULL != NULL` Return FALSE in SQL?. For more information, please follow other related articles on the PHP Chinese website!