Three-value Logic in SQL [SQL Server]

In SQL, the possible values of logical expressions include true, false, and unknown. They are called three-value logic. The three-value Logic is unique to SQL. Most logical expressions that change to a language have only true or false values. The unknown in SQL usually appears in the logical expression that contains null values (for example, the following logical values are all unknown: NULL> 42; null = NULL; x + null> Y ). Null usually indicates a lost or unrelated value. When comparing a lost value with another value (this value may also be null), the logical result is always unknown.

It is easy to confuse unknown logic results with null. Not true is equal to false, not false is equal to true, and the negative unknown (not unknown) or unknown.

The unknown logical result and null are treated differently in different language elements. For example, all query filters (on, where, and having) treat unknown as false. Rows that make the filter unknown are excluded from the result set. The unknown value in the check constraint is treated as true. If the table contains a check constraint, the value of the salary column must be greater than 0. It is acceptable to insert rows whose salary is null to the table because (null> 0) is equal to unknown and is considered the same as true in the check constraint.

When two null values are compared in the filter, unknown is obtained and treated as false, as if one null is not equal to the other null. The unique constraints, sorting operations, and grouping operations hold that the two null values are equal.

• If a column in the table defines the unique constraint, you cannot insert two rows whose column value is null to the table.
• The group by clause groups all null values in a group.
• The order by clause lists all null values.

In short, it is advantageous to know how unknown logical results and null are processed in different language elements, so as to avoid future troubles.