Analysis of the effect of SQL compound query and null on query results

The Expert_details modifier (type VARCHAR2 (20), nullable) are retrieved for those records that are empty because the field is of type VARCHAR2 (20), and the SQL statement I use is
Copy code code as follows:
SELECT * from expert_details twhere t.modifier = '

A record was not retrieved, which does not match the records stored in the table. It was later thought that even empty character storage should be null instead of ' in the database tutorial.

I then used the following SQL statement and still did not retrieve a single record.
Copy code code as follows:
SELECT * FROM Expert_details t
where t.modifier = null

Finally I thought of the null value test in SQL. Use the following statement to finally retrieve the desired result.
Copy code code as follows:
SELECT * FROM Expert_details t
where t.modifier is null

In the SQL statement, WHERE clause: where t.modifier = null, the NULL keyword cannot be used here because it is not a true value, it is just a symbol because its value is unknown. When the t.modifier itself is NULL, the WHERE clause is: where null= null, when the values on both sides of the equals sign are unknown, the result is true or false,sql cannot give a definite result, so the result of the query is null.

Therefore, it is necessary to explicitly use a null value test that is null or a negative form field is not NULL to detect null values.

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

Table 1 Truth-Tables of and

	True	False	Null
True	True	False	Null
False	False	False	False
Null	Null	False	Null

Table 2 Truth-Tables of OR

	True	False	Null
True	True	True	True
False	True	False	Null
Null	True	Null	Null

Table 3 The truth truth of 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 one of these three expressions is null, the result return is definitely null.

Therefore, the between 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 its return result has only two conditions: TRUE or false.