Confusing null values in Databases

Many people usually think that the null value is the same as the blank value or the 0 value. At least I thought so before today. In fact, the null value is not the same as the blank value or the 0 value. Null indicates that the value is unknown. There are no two equal null values. It is unknown to compare two null values or null values with any number. The following information about null values in SQL books online

Many people usually think that the null value is the same as the blank value or the 0 value. At least I thought so before today. In fact, the null value is not the same as the blank value or the 0 value. Null indicates that the value is unknown. There are no two equal null values. It is unknown to compare two null values or null values with any number. The following information about null values in SQL books online

Many people usually think that the null value is the same as the blank value or the 0 value. At least I thought so before today. In fact, the null value is not the same as the blank value or the 0 value.

Null indicates that the value is unknown. There are no two equal null values. It is unknown to compare two null values or null values with any number.

The following information about null values in SQL books online:

• To test the NULL value in the query, use is null or is not null in the where clause.
• When querying SQL results, the null value is displayed as (null) in the result set)
• You can insert null to a column by explicitly declaring NULL in INSERT or UPDATE, or not entering INSERT
• NULL values cannot be used to distinguish the information required by the two rows in the table.

The following databases are shown:

A null value is displayed as (null), while a blank value is displayed in the lower left corner. A blank value does not indicate no data. It is generated when space data is inserted.

For example, the following statements are completely different:

mysql> INSERT INTO table (name) VALUES (NULL);mysql> INSERT INTO table (name) VALUES ("");

The two statements insert values into the name column, but the first statement inserts a NULL value and the second statement inserts an empty string. The first one can be regarded as "the name is unknown", while the second one can mean "he does not have it ".

In SQL, the NULL value is always FALSE when any other value or even NULL value is compared ). An expression that contains NULL always generates a NULL value, unless it is specified in the document containing operators and functions in the expression. In the following example, all columns return NULL:

mysql> SELECT NULL,1+NULL,CONCAT('Invisible',NULL);

If you want to find a column whose value is NULL, you cannot use = NULL for testing. The following statement does not return any rows because expr = NULL is false for any expression:

mysql> SELECT * FROM my_table WHERE phone = NULL;

To search for a NULL value, you must use the is null test. The following example shows how to find a NULL phone number and a blank phone number:

mysql> SELECT * FROM my_table WHERE phone IS NULL;mysql> SELECT * FROM my_table WHERE phone = "";

In MySQL, just like many other SQL servers, you cannot index columns with NULL values. You must declare that such a column is not null, and you cannot insert NULL to the index column.

When you use load data infile to read DATA, use ''to update the empty column. If you want to include a NULL value in a column, you should use N in a text file. The literal word 'null' can also be used in some cases. See the 7.16 load data infile syntax.

When order by is used, the NULL value is first displayed. If you use DESC to sort data in descending order, the NULL value is finally displayed. When group by is used, all NULL values are considered equal.

To help with NULL processing, you can use the is null and is not null operators and IFNULL () functions.

For certain column types, NULL values are specially processed. If you insert NULL into the first TIMESTAMP column of the table, the current date and time are inserted. If you insert NULL into an AUTO_INCREMENT column, insert the next number in the sequence.

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

NULL is a data value and belongs to a domain (?). Yes. For example, for a string field, the null value can only be a string. Although its content is not defined or unknown, it is a string, which is beyond doubt.

NULL is not invalid data, and the SQL-3 standards are clear. We cannot save 1/0, but can save null values. Although it is null, undefined, or unknown, it is indeed a legal information.

Black Hole calculation: For NULL, a general operation returns NULL. For example, the addition, subtraction, multiplication, division, is a black hole. There will never be any data equal to NULL. Of course, 1 is not equal to NULL, and 2 is the same. However, NULL is not equal to NULL. It is incorrect to say that NULL is equal to NULL. Therefore, we can only compare it with "yes" or "no ". SQL-3 standards have some conventions to avoid confusion. For example, if expression x = NULL, the result should be UNKOWN. The expression "x is NULL" depends on the situation. If x is NULL or False, true (?) is returned (?); If x is not NULL, False is returned. It's a bit strange, isn't it based on NULL not equal to NULL. This rule is indeed supported by the SQL standard. Don't be surprised when you encounter such a database system.

Three-value logic: Wei xiaobao in "Deer Ding Ji", one of the secrets of the whole person is "I just nodded when I asked you a question. If not, I just shook my head and won't let you speak out !" Indeed, our logic is always based on this binary logic system. It is not that simple in the relational theory. There is a situation where there is no absolute right: NULL. This is the three-value logic of the relational theory.

There are also some common NULL-related situations. For example, statistical functions (also known as Aggregate functions and set functions in some documents) usually ignore NULL. However, suppose there is a table T:

C-----12NULLNULL

We execute Two Queries respectively: select count (*) from t and select count (C) from t. What is the difference? At first, I thought the two results should be the same. As a result, the first is 4 and the last is 2. The previous one is equal to 4. It is clear that NULL is based on the fact that data is also the case. The next result is 2, because when column C is counted, COUNT ignores NULL.

In the SQL-3 standard, a search condition (WHERE and HAVING) accepts only TRUE, but the constraint rejects only FALSE, both have the opposite attitude towards NULL. Therefore, the previously seen ROOMS table can write null values into the personnel field. However, there is no rule in sorting. The SQL standard only provides one supplementary definition. Therefore, the ORDER BY method is different for each DBMS. Of course, NULL is either higher than all values or lower than all values.

In "SQL-3 reference book" introduced in more detail about the content of NULL. In addition, the book also introduces two database experts C. j. date and E. f. different arguments about NULL in Codd (E. f. codd even hopes to have a four-value Logic.) If you are interested, you can read it. In practice, we will also encounter some interesting things, especially in join queries. NULL makes our life rich and colorful ".