Impact of SQL composite query and null on query results

In expert_details, retrieve the records whose modifier (type varchar2 (20) can be empty) is null, because the field type is varchar2 (20 ), the code used for copying the SQL statement is as follows: * fromexpert_detailstwheret.modifier does not retrieve a record, which is inconsistent with the records stored in the table. Later I thought that even if the modifier (type varchar2 (20) can be empty) is retrieved from the expert_details of the empty struct storage, because the field type is varchar2 (20 ), the SQL statement I use is
The Code is as follows:
* From expert_details twhere t. modifier =''

No record is retrieved, which is inconsistent with the records stored in the table. Later I thought that even the empty sequence storage should be null instead ''.

Then I used the following SQL statement and still did not retrieve a record.
The Code is as follows:
Select * from expert_details t
Where t. modifier = null

Finally, I thought of testing the null value in SQL. Use the following statement to finally retrieve the desired result.
The Code is as follows:
Select * from expert_details t
Where t. modifier is null

In SQL statements, where clause: where t. modifier = null. The null keyword cannot be used here because it is not a real value. It is just a symbol because its value is unknown. When t. when modifier itself is null, that is, the where clause is: where null = null. When the values on both sides of the equal sign are unknown, the result is true or false. SQL cannot provide a clear result, therefore, the query result is null.

Therefore, the null value test must be explicitly used, that is, the field is null or its negative form field is not null, to detect the null value.

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.

A between B and c 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, a in (B, c, d) is equivalent to (a = B) or (a = c) or (a = d). According to the truth table, if one of the three expressions is null, the return value must be null.

Therefore, the between clause and the in clause do not increase the SQL statement expression capability.

SQL has a null value test, that is, the field is (not) null, But it returns only two results: true or false.