SQL compound query condition (AND, OR, NOT) Processing of NULL values

The NULL value affects the query condition results, and the results are very subtle.

The following table lists the true values of AND, OR, and not in SQL.

Truth Table of table 1 AND

	TRUE	FALSE	NULL
TRUE	TRUE	FALSE	NULL
FALSE	FALSE	FALSE	FALSE
NULL	NULL	FALSE	NULL

Table 2 OR truth table

	TRUE	FALSE	NULL
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	NULL
NULL	TRUE	NULL	NULL

Truth Table of table 3 NOT

TRUE	FALSE	NULL
FALSE	TRUE	NULL

When two OR more query conditions are combined with AND, OR, and not, NOT has the highest priority, followed by AND, AND finally OR. Brackets are recommended to avoid ambiguity and ensure portability.

ABETWEENBANDC is equivalent to (A> = B)AND(A <= C). Therefore, based on the truth table, we can obtain the rule for processing NULL values in the BETWEEN clause.

Similarly,IN(B, C, D) is equivalent to (A = B)OR(A = C)OR(A = D), according to the truth table, as long as one of the three expressions is NULL, the returned result must be NULL.

Therefore,BETWEEN clause andIN clauseDoes not increase the expression capability of SQL statements.

SQL hasNULLValue test, namely:Field IS (NOT) NULLHowever, the returned results can only be TRUE or FALSE.