Shulou(Shulou.com)06/03 Report--

This article mainly explains "how to understand ternary logic and NULL". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to understand ternary logic and NULL".

What is NULL?

NULL is used to represent missing values or missing unknown data, not a specific type of value. The NULL value in the data table indicates that the field in which the value is located is empty, and the field with a value of NULL has no value, especially to understand that the NULL value is different from 0 or an empty string.

Two kinds of NULL

You may find this strange, because there is only one kind of NULL in SQL. However, when discussing NULL, we generally divide it into two types: "unknown" and "not applicable,inapp licable".

Take the case of "do not know the color of the eyes of the person who wears sunglasses" as an example, this person's eyes must have color, but if he does not take off his glasses, others will not know the color of his eyes. It's called the unknown. And "don't know what color the eyes of the refrigerator are" is "not applicable". Since refrigerators have no eyes at all, the attribute "eye color" does not apply to refrigerators. The term "color of refrigerator eyes" is as meaningless as "round volume" and "male births". Usually, we are used to saying "I don't know", but there are many kinds of "don't know". In the case of "not applicable", NULL is closer to "meaningless" than "uncertain" in semantics. To sum up here: "unknown" means "you don't know now, but you can know after adding certain conditions", while "not applicable" means "you can't know no matter how hard you try."

This classification was first given by E. F. Codd, the inventor of the relational model. The picture below is his classification of "lost information"

Why must it be written as "IS NULL" instead of "= NULL"

I believe many people have this kind of confusion, especially those who are just learning SQL. Let's look at a specific case, assuming that we have the following table and the data

DROP TABLE IF EXISTS tweak sampleblank nulltCreate TABLE t_sample_null (id INT (11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'self-increment primary key', name VARCHAR (50) NOT NULL COMMENT 'name', remark VARCHAR (500) COMMENT 'remarks', primary key (id)) COMMENT 'NULL sample'; INSERT INTO t_sample_null (name, remark) VALUES ('zhangsan',' Zhang San'), ('Li Si', NULL)

We need to query the records marked as NULL (the term NULL itself is wrong, but we are used to it in our daily life, let's look down). How to find out, many novices will write such SQL.

-- SQL does not report an error, but cannot find the result SELECT * FROM t_sample_null WHERE remark = NULL

We don't report errors during execution, but we can't find out the results we want. why? Let's put this question aside for a moment, and let's look down.

Ternary logic

This ternary logic is not a trinomial operation, but refers to three logical values. Some people may wonder, isn't the logical value only true (true) and false (false)? where did the third come from? When we say this, we need to pay attention to the environment. In mainstream programming languages (C, JAVA, Python, JS, etc.), there are indeed only two logical values, but there is a third logical value in SQL: unknown. This is a bit similar to what we usually say: right, wrong, do not know.

The logical value unknown is different from UNKNOWN, which is a kind of NULL. The former is an explicit Boolean logical value, while the latter is neither a value nor a variable. For ease of distinction, the former is represented by the lowercase letter unknown and the latter by the uppercase letter UNKNOWN. In order for you to understand the difference between the two, let's look at a simple equation like xonomx. When x is the logical value unknown, x is judged to be true, and when x is UNKNOWN, it is judged to be unknown.

This is an explicit comparison of logical values unknown = unknown → true--, which is equivalent to NULL = NULLUNKNOWN = UNKNOWN → unknown three-valued logic table.

NOT

AND

OR

The blue part of the figure is a unique operation in ternary logic, which is not found in binary logic. All the other SQL predicates can be combined from these three logical operations. In this sense, these logical tables can be said to be the matrix of SQL.

For NOT, it is easy to remember because the logical value table is relatively simple, but for AND and OR, it is very difficult to remember all of them because there are more logical values combined. For ease of memory, note that there is a priority order between the three logical values.

AND: false > unknown > true

OR: true > unknown > false

The logical value with high priority determines the result of the calculation. For example, true AND unknown, because unknown has a higher priority, the result is unknown. In the case of true OR unknown, the result is true because true has a higher priority. With this order in mind, ternary logic operations can be carried out more easily. In particular, it is important to keep in mind that when unknown is included in the AND operation, the result will definitely not be true (conversely, if the result of the AND operation is true, then both parties participating in the operation must be true).

Assuming a = 2, b = 5, c = NULL, the logical values of the following expressions are as follows

< b AND b >

C → unknowna > b OR b

< c → unknowna < b OR b < c → trueNOT (b c) → unknown"IS NULL" 而非 "= NULL" 我们再回到问题：为什么必须写成"IS NULL"，而不是"＝ NULL" 对 NULL 使用比较谓词后得到的结果总是 unknown 。而查询结果只会包含 WHERE 子句里的判断结果为 true 的行，不会包含判断结果为 false 和 unknown 的行。不只是等号，对 NULL 使用其他比较谓词，结果也都是一样的。所以无论 remark 是不是 NULL ，比较结果都是 unknown ，那么永远没有结果返回。以下的式子都会被判为 unknown -- 以下的式子都会被判为 unknown= NULL>

NULL < NULL NULLNULL = NULL

So why can the result of using the comparison predicate on NULL never be true? This is because NULL is neither a value nor a variable. NULL is just a tag that says "no value", while the comparison predicate applies only to values. Therefore, there is no point in using comparison predicates for NULL that are not values. The statements that "the value of the column is NULL" and "NULL value" are wrong. Because NULL is not a value, it is not in the domain. On the contrary, if someone thinks that NULL is a value, then we can think about it the other way around: what type of value is it? The values that exist in a relational database must belong to a certain type, such as character type or numerical type. So, if NULL is a value, then it must be of some type.

There are two reasons why NULL is easily thought of as a value. The first is that in high-level programming languages, NULL is defined as a constant (many languages define it as an integer 0), which leads to our confusion. However, NULL in SQL is completely different from NULL in other programming languages. The second reason is that a predicate like IS NULL is made up of two words, so we tend to think of IS as a predicate and NULL as a value. In particular, there are predicates such as IS TRUE and IS FALSE in SQL, and so on, so it is not unreasonable to think so. But as the book on standard SQL reminds people, we should think of IS NULL as a predicate. Therefore, it may be more appropriate to write it as IS_NULL.

Gentle traps compare predicates and NULL

The law of the middle of the row is not established.

The law of exclusion means that in the same thinking process, two contradictory ideas cannot be false, but there must be one truth, that is, "either An or not A".

Suppose we have a student table: t_student

DROP TABLE IF EXISTS tactile students create TABLE t_student (id INT (11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'self-increasing primary key', name VARCHAR (50) NOT NULL COMMENT 'name', age INT (3) COMMENT 'age', remark VARCHAR (500) NOT NULL DEFAULT''COMMENT' remarks', primary key (id)) COMMENT 'student information' INSERT INTO t_student (name, age) VALUE ('zhangsan', 25), (' wangwu', 60), ('bruce', 32), (' yzb', NULL), ('boss', 18); SELECT * FROM t_student

The age of the data yzb in the table is NULL, which means that the age of the yzb is unknown. In the real world, yzb is 20 years old, or not 20 years old, one of the two, this is undoubtedly a true proposition. So in the world of SQL, does the law of exclusion still apply? Let's take a look at a SQL

SELECT * FROM t_studentWHERE age = 20 OR age 20

At first glance, isn't this all the records in the query table? Let's look at the actual results.

Yzb didn't find out. Why? Let's analyze that the age of yzb is NULL, so the steps to judge this record are as follows

-1. John is NULL (unknown NULL!) SELECT * FROM t_studentWHERE age = NULLOR age NULL;-- 2. After using the comparison predicate on NULL, the result is unknownSELECT * FROM t_studentWHERE unknownOR unknown;-- 3.unknown OR unknown and the result is unknown (refer to the logical table of ternary logic) SELECT * FROM t_studentWHERE unknown

There are only rows that are judged to be true in the query results of the SQL statement. In order for yzb to appear in the result, you need to add the following "condition 3"

-- add three conditions: age is 20 years old, or not 20 years old, or age unknown SELECT * FROM t_studentWHERE age = 20 OR age 20 OR age IS NULL

CASE expressions and NULL

The simple CASE expression is as follows

CASE col_1 WHEN = 1 THEN'o' WHEN NULL THEN 'x'END

This CASE expression must not return ×. This is because the second WHEN clause is an abbreviation for col_1 = NULL. As we know, the logical value of this formula is always unknown, and the CASE expression is judged in the same way as the WHERE clause, only the condition that the logical value is true is recognized. The correct way to write it is to use a search CASE expression like this

CASE WHEN col_1 = 1 THEN'o' WHEN col_1 IS NULL THEN 'x'ENDNOT IN and NOT EXISTS are not equivalent

One of the techniques we often use to optimize the performance of SQL statements is to rewrite IN to EXISTS, which is equivalent to rewriting, and there is no problem. However, when you rewrite NOT IN to NOT EXISTS, the result may not be the same.

Let's look at an example. We have the following two tables: t_student_A and t_student_B, which represent the students of Class An and Class B, respectively.

DROP TABLE IF EXISTS tactile student students: create TABLE t_student_A (id INT (11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'self-increment key', name VARCHAR (50) NOT NULL COMMENT 'name', age INT (3) COMMENT 'age', city VARCHAR (50) NOT NULL COMMENT 'city', remark VARCHAR (500) NOT NULL DEFAULT 'COMMENT' remarks, primary key (id)) COMMENT 'student information' INSERT INTO t_student_A (name, age, city) VALUE ('zhangsan', 25,' Guangzhou'), ('wangwu', 60,' Guangzhou'), ('bruce', 32,' Beijing'), ('yzb', NULL,' Shenzhen'), ('boss', 43,' Shenzhen'); DROP TABLE IF EXISTS t_student_B CREATE TABLE t_student_B (id INT (11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'self-increasing primary key', name VARCHAR (50) NOT NULL COMMENT 'name', age INT (3) COMMENT 'age', city VARCHAR (50) NOT NULL COMMENT 'city', remark VARCHAR (500) NOT NULL DEFAULT 'COMMENT' remarks', primary key (id)) COMMENT 'student information' INSERT INTO t_student_B (name, age, city) VALUE ('Ma Huateng', 45, 'Shenzhen'), ('Ma San', 25, 'Shenzhen'), ('Jack Ma', 43, 'Hangzhou'), ('Robin Li', 41, 'Shenzhen'), ('Young people', 25, 'Shenzhen'); * FROM t_student_B

Demand: inquire about Class B students of different ages from Class A students living in Shenzhen, that is to say, find out: Ma Huateng and Li Yanhong, how to write this SQL, like this?

-- inquire about SELECT * FROM t_student_BWHERE age NOT IN (SELECT age FROM t_student_A WHERE city = 'Shenzhen') of Class B students of different ages from those of Class A living in Shenzhen.

Let's take a look at the implementation results.

We found that the result was empty and could not query any data. why? NULL is making trouble again. Let's take a look at what happened step by step.

-1. Execute a subquery to get the age list SELECT * FROM t_studentWHERE age NOT IN (43, NULL, 25);-- 2. Rewrite NOT INSELECT * FROM t_studentWHERE NOT age IN (43, NULL, 25) with NOT and IN equivalent;-- 3. Rewrite the predicate INSELECT * FROM t_studentWHERE NOT ((age = 43) OR (age = NULL) OR (age = 25)) with OR equivalent;-- 4. Use de Morgan's law to rewrite SELECT * FROM t_studentWHERE NOT (age = 43) AND NOT (age = NULL) AND NOT (age = 25);-- 5. Rewrite NOT and = SELECT * FROM t_studentWHERE (age 43) AND (age NULL) AND (age 25) with equivalent;-- 6. After using NULL, the result is unknownSELECT * FROM t_studentWHERE (age 43) AND unknown AND (age 25);-7. If unknown is included in the AND operation, the result is not true (refer to the logic table of ternary logic) SELECT * FROM t_studentWHERE false or unknown

As you can see, after a series of transformations, none of the records are judged to be true in the WHERE clause. That is, if NULL exists in the selected column of the table used in the NOT IN subquery, the overall query result of the SQL statement is always empty. This is a terrible phenomenon!

To get the right results, we need to use the EXISTS predicate

The correct SQL sentence: Ma Huateng and Li Yanhong will be queried to SELECT * FROM t_student_B BWHERE NOT EXISTS (SELECT * FROM t_student_A A WHERE B.age = A.age AND A.city = 'Shenzhen')

The execution result is as follows

Similarly, let's take a step-by-step look at how this SQL deals with lines of age NULL.

-1. Compare with NULL in the subquery, where A.age is NULLSELECT * FROM t_student_B BWHERE NOT EXISTS (SELECT * FROM t_student_A A WHERE B.age = NULL AND A.city = 'Shenzhen');-- 2. After using "=" for NULL, the result is unknownSELECT * FROM t_student_B BWHERE NOT EXISTS (SELECT * FROM t_student_A A WHERE unknown AND A.city = 'Shenzhen');-- 3. If unknown is included in the AND operation, the result will not be trueSELECT * FROM t_student_B BWHERE NOT EXISTS (SELECT * FROM t_student_A A WHERE false or unknown);-4. The subquery returns no results, so on the contrary, NOT EXISTS is trueSELECT * FROM t_student_B BWHERE true

In other words, yzb is treated as "a person of different age from anyone else". EXISTS will only return true or false and will never return unknown. So there is the confusion that IN and EXISTS can be used interchangeably, while NOT IN and NOT EXISTS are not.

There are other pitfalls, such as qualifying predicates and NULL, qualifying predicates and extremal functions are not equivalent, aggregate functions and NULL, and so on.

Summary

1. NULL is used to represent missing values or missing unknown data. It is not a specific type of value and cannot be used to describe it.

2. The result of using predicates on NULL is that when unknown,unknown participates in logical operations, the operation of SQL will be different than expected. 3. IS NULL is a predicate instead of: IS is a predicate and NULL is a value; similarly, there are IS TRUE and IS FALSE4. If you want to solve the problems caused by NULL, the best way is to add NOT NULL constraints to the table to try your best to eliminate NULL.

Thank you for your reading, the above is the content of "how to understand ternary logic and NULL". After the study of this article, I believe you have a deeper understanding of how to understand ternary logic and NULL. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.