SQL story Excerpt (vii) ———— touch NULL value

In the previous article, we had a glimpse of NULL, the incredible little thing. Today, I introduce it as much as I can. By convention, this is a discussion that is as simple as possible but not rigorous, and may even be less serious. My goal is to help readers work more easily and to be interested in learning more about the database. On the other hand, I believe that the mistakes in my argument will certainly be made by others, so please find out that the wrong friends must be openly pointed out. In this way, it will help me and my readers friends to progress. Heartfelt thanks to every friend who has criticized and corrected, especially those who have made public criticisms and corrections. Special thanks to Sunshine19, specially sent to e-mail, pointed out the "SQL Story" of various deficiencies, and put forward valuable advice. I expect him to continue to care about the topic and expect him to bring us good work.

There is no escape.

Example: Double room.

Suppose a school, its single staff apartment, has two beds in each room. There are many advantages to doing this, such as safety, are young people, no matter the boy and girl, have a companion to live always let a person feel relieved, each other also have a care; if anyone is married, because each room up to two people, it can be easier to find a room, first to their homes, slowly waiting for the housing. These things certainly don't bother US programmers more (unless we live in it). Now the concern is that someone has made an apartment management database. The Room management table is designed like this:

CREATE TABLE Rooms (

Roomid CHAR (5),

MasterID CHAR (10),

Secondid CHAR (10),

CONSTRAINT pk_room PRIMARY KEY (Roomid),

CONSTRAINT fk_master FOREIGN KEY (MasterID) REFERENCES livers (ID),

CONSTRAINT fk_second FOREIGN KEY (secondid) REFERENCES livers (ID)

)

Briefly, the administrator in order to facilitate management (such as the collection of water and electricity, etc.), requiring each person's room has a homeowner; two people. More detailed information is saved in the Livers table, so we see two foreign key constraints. Of course, this design is not unassailable, but it has its own reasons, here we do not discuss.

The concern here is that there are less than two rooms for the tenant, what should we fill out in the vacated areas? One way to do this is to fill in an empty string, which means that we default to the existence of a tenant with an ID number of an empty string. Because the empty string is also a string. This is also illustrated by the two foreign key constraints. In fact, in a design like this, I've seen some examples of the authors who, in order to avoid the absence of references, created a non-existent message as part of the database structure. Specifically to this example, someone might actually use an empty string or write a "NONE" or something, written in the Livers table, indicating that no one is living. Usually this causes a lot of problems, such as we may have to add a constraint, so that a person can not appear in the rooms table two times. But now this virtual come out to say that no one tenant what to do? But there are many places there are no people, for example, as long as there is a vacancy, there will be two "NONE" on this record! In the case of a stranger (but not impossible in some applications), there is a tenant whose ID is "NONE". And what if, in extreme cases, the tenant has moved out all the way? Want "delet from livers" all No, "none" can be deleted ah. When you work in such a database, always remind yourself not to offend the non-existent guy. To remember, he had no sex, so the men and women had to live in the room; the other person could only sleep in a bed, but he could have all his free time; After the others had moved out, his old man occupied all the rooms, and our laws and regulations meant nothing to him. The biggest problem with this particular value is the mix of information and database structure. In other words, the patterns of relationships and relationships are confused. If there are a few of these tables in a database, everyone will be crazy.

This is the meaning of NULL, when you define a data as NULL, it is tantamount to telling the system that this data does not exist, or unknown. Leave it alone, its content has no meaning (it is, of course, a meaning in itself). There is a null in the room, which means no one, and is not considered to be a person who is called "No Man" living in it. There will be no one to occupy all the rooms of the evil guests exist. To clear the livers, then clear, as long as you set the cascading constraints, there will be no strange things appear. The only consequence is an unoccupied apartment, isn't that what we want?

NULL is real, although in a sense we cannot explain its existence. It is a black hole in the world of relations. May make some people uncomfortable, but to avoid it will only bring us more trouble.

I can't see it, I can't touch it.

In the SQL-3 reference book, the author explains the meaning of null: unknown or undefined. For null, there are a lot of interesting features.

Null is a data value, and it belongs to a domain (?). ）。 Yes, for example, a string field in which the null value can only be a string. Although its content is undefined, or unknown, it is a string, and there is no doubt about it.

Null is not illegal data, this point SQL-3 standard also said very clear. We have no way to save One-zero, but we can save the null value. Although it is a null value, is undefined, is unknown, but it is indeed a legitimate message.

Operation Black Hole: for null, a normal operation returns NULL. Like subtraction, it's just like a black hole. There will never be any data equal to NULL. Of course, 1 is not equal to null,2. However, NULL is not equal to NULL. It is wrong to say that a null equals null. So we can only compare it to "yes" or "no". To avoid confusion, the SQL-3 standard has some conventions. For example, the expression of x=null, the result should be unkown. The expression "x is NULL" depends on the situation, and if x is null or FALSE, return ture (?). X is Non-null, returns false. It's kind of weird, isn't it, based on null not equal to NULL. This rule is indeed supported by the SQL standards, so don't be surprised to encounter such a database system.

Three-value logic: "Deer ding kee" in the trinket, one of the secrets of the trick is "I ask you, is to nod, not to shake your head, don't you make a noise!" "Indeed, our logical view is always based on this binary logic system, right is right, wrong is wrong." But in relational theory, it's not that simple. There is a situation in which there is no absolute right or wrong: NULL. This is the three-valued logic of relational theory.

There are also some common null-related situations. For example, statistical functions (which are also referred to as aggregate functions, set functions) usually ignore null. However, suppose there is such a table t:

C

-----

1

2

Null

Null

We executed two queries: SELECT COUNT (*) from T and select COUNT (C) from T, and guess what would be different? At first, I thought two results should be the same. As a result, the first one is 4, and the latter one is 2. The previous one equals 4, apparently based on the fact that NULL is also the data; the latter result is 2, because count ignores null when the C column is counted.

In the SQL-3 standard, the search condition (where and having) accept only true, while the constraint rejects only false, which is the opposite of the accepted attitude to null. So the rooms table we saw earlier can write null values to the People field. And in the sort of, but there is no rule. The SQL standard only makes a supplemental definition, so each DBMS's method of processing an order by is not the same, and of course null is not higher than all values or lower than all values.

A more detailed description of NULL is presented in the SQL-3 reference book. In addition, the book also describes the two-bit database experts c.j.date and e.f.codd on the different arguments about null (e.f.codd even want to have four value logic) interested friends can find a read. In practice, we will also encounter some interesting things, especially in the join query, NULL let our life become "colorful."

For a variety of reasons, I may slow down my writing in recent days, but that doesn't mean I will give up. After recuperation and recharge, I will return to what I am good at in the field of combat. Thanks again to every reader who cares about "SQL Story".