Do you really play with SQL? Three-valued logic

Let's take a look at the question: a not in (B,c,null), return what?

Is it sometimes hard to write a query, but not show the answer you want? Let's take a look at one of the pitfalls.

We filter to a row with a column value of NULL, typically in the following way:select * from TB where col=null

But that doesn't get the result we want, the right way is that col is null for what? This involves three value logic.

Three-valued logic

Possible values for logical expressions in SQL include TRUE, false, and unknown. They are called three-valued logic.

The three-valued logic is unique to SQL. Most programming languages have logical expressions that have only true or false values of two.

Unknown logical values in SQL typically appear in logical expressions that contain null values, for example, the following three expression values are unknown:

NULL <  the ; NULL = NULL ; X+NULL>Y;

Unknown value is also determined, but sometimes true sometimes false, a general principle is: Unknown value is not true or FALSE, False is true, unknown can only take true and false one, but unknown the opposite or unknown

When filtering conditions are made in on\where and having, unknown is treated as false, and is considered true in the check, where two null comparisons result in a condition of unknown. (The construction table contains a CHECK constraint that requires that the value of the salary column must be greater than 0, which can be accepted when inserting salary to the table, because (null>0) equals unknown and is treated as true in a check constraint)

Comparing two null values in a filter will get unknown, which will be treated as false, as if one of the null is not equal to another null.

and UNIQUE constraints. The sort operation and grouping operations consider two null values to be equal.

If there is a column in the table that defines a unique constraint, it is not possible to insert two rows in the table with NULL for that column value.

The GROUP BY clause groups all of the null values.

The ORDER BY clause arranges all the null values together.

You know why it is null to filter for NULL in a query, or is to not NULL, but a regular conditional expression cannot be filtered out?

Practice

Which three values will be returned in the following pairs?

Answer

View Code

Do you really play with SQL? Three-valued logic