About the three-valued logic in the database (tree-value-logic)

In SQL, a logical expression (also called a predicate) can have three values: True, False, Unknown, which is called the three-value logic, which is a unique attribute of SQL.

In most programming languages, the logical expression has only two values, which is true and false, but in SQL, there are also unknown. Unknown generally appear in a logical expression with NULL, such as the following: Null>2, Null+x>y, Null=null. The results are unknown. Null this symbol represents a missing value, and when you compare a missing value to another value, you get the unknown result.

At some point, unknown and null can be easily confused on processing, such as the following: Not true equals false,not false equals True, but unknown's reverse is unknown.

In different language elements (that is, in SQL statements, different modules), unknown logical results and NULL are handled differently.

1, in the query filter (on, where, have) unknown as false to handle

2, in the check constraint, unknown is treated as true, such as a check constraint on a field in a table, then this table may not contain 2 rows or more of the field value is unknown row.

3. In a filter, if you compare two null values, the resulting value is unknown, but will still be treated as false.

4, in unique constraints, set operations (such as union and except), sorting, grouping operations, the two null values are considered equal.

⑴ this way, in a column with a unique constraint, you cannot insert two rows in the table that have null for the column value

The ⑵group by clause will divide all null values into a group, and the ORDER BY clause will arrange all data with null values together

⑶ when comparing rows in two sets, the set operator considers that the different null values are equal.

Basically read "Technical insider-t-sql" a book to get the notes, if there are errors please correct me, thank you.

About the three-valued logic in the database (tree-value-logic)