SQL Server compound query condition (and,or,not) method for handling null values

In SQL 3 value logic, a query condition can produce the following three scenarios: True,false,null. Only records that satisfy the value of the WHERE clause are true appear in the results table.

A null value affects the results of the query condition, and the result is subtle.

The following is the truth table for And,or,not in SQL.

Table 1 and Truth tables

	TRUE	FALSE	Null
TRUE	TRUE	FALSE	Null
FALSE	FALSE	FALSE	FALSE
Null	Null	FALSE	Null

Table 2 or of the truth tables

	TRUE	FALSE	Null
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	Null
Null	TRUE	Null	Null

Table 3 Truth tables for not

TRUE	FALSE	Null
FALSE	TRUE	Null

When more than two query conditions are combined with and, or, not, the precedence of not is the highest, followed by and, and finally, OR. Parentheses are best used to avoid ambiguity and to ensure portability.

A between B and C are equivalent to (a>=b ) and (a<=c), so the rules for handling null values in between clauses can be derived from the truth table.

Similarly, a in (b,c,d) is equivalent to (a=b)or(a=c)or(A=d), according to the truth table, as long as there is one of these three expressions is null, the result returned is definitely null.

Therefore, thebetween clause and the in clause do not increase the expressive power of the SQL statement.

There is a null value test in SQL, that is: the field is (not) null , but it returns only two cases: TRUE or false.

SQL Server compound query condition (and,or,not) method for handling null values