Detailed description of the null value in the database language, detailed description of the null value in the database

Detailed description of the null value in the database language, detailed description of the null value in the database

Although those who are familiar with SQL do not have any questions about Null, it is still difficult to find a well-summarized article. It is good to see an English version.

Tony Hoare issued a null reference in 1965 and thought it was a "billions of dollars error ". even 50 years later, the null value in SQL still causes many common errors.

Let's take a look at the most shocking situations.

 

Null does not support size/equal judgment

The following two queries return 0 rows regardless of the number of users records:
 

select * from users where deleted_at = null; – result: 0 rows select * from users where deleted_at != null; – result: 0 rows

 

How can this happen? Null indicates an "unknown" type. That is to say, it is meaningless to use the normal conditional operators operator to compare null with other values. Null is not equal to Null (approximate understanding: an unknown value cannot be equal to an unknown value, and the relationship between the two is also unknown; otherwise, it will be confused in mathematics and logic ).

-Note: The following SQL statements are suitable for MySQL. If it is Oracle, you need to add... From dual;

 

select null > 0; – result: null select null < 0; – result: null select null = 0; – result: null select null = null; – result: null select null != null; – result: null

 

The correct method for comparing a value with null is to use the is keyword and the is not OPERATOR:
 

Select * from users where deleted_at is null;-result: All users marked as deleted

 

If you want to determine whether the values of the two columns are different, you can use is distinct from:
 

Select * from users where has_address is distinct from has_photo-result: the address (address) or photo (photo) have only one user.

 

Not in and Null

Subselect is a convenient way to filter data. For example, if you want to query users without any packages, you can write the following query:
 

select * from users  where id not in (select user_id from packages)

 

However, if the user_id of a row in the packages table is null, the problem arises: The returned result is null! To understand why such a strange thing occurs, we need to understand what the SQL Compiler has done. Below is a simpler example:
 

select * from users  where id not in (1, 2, null)

 

This SQL statement is converted:
 

select * from users  where id != 1 and id != 2 and id != null

 

We know, id! = Null is an unknown value. null. the result of performing the and operation for any value and null is null, so it is equivalent to no other conditions. the reason for this result is that the logical value of null is not true.

If the condition is changed, the query result is correct. Now we query users with packages.
 

select * from users  where id in (select user_id from packages)

 

We can also use a simple example:
 

select * from users where id in (1, 2, null)

 

This SQL statement is converted:
 

select * from users  where id = 1 or id = 2 or id = null

Because the where clause is a string of or conditions, it does not matter if one of the results is null. The non-true value does not affect the calculation results of other parts of the clause, which is equivalent to being ignored.

 

Null and sorting

In sorting, the null value is considered to be the largest. in descending order (descending), this will make you very large, because the null value ranks first.

The following query aims to display the User ranking based on the score, but it ranks the user with no score at the top!
 

Select name, points from users order by 2 desc;-records whose points is null are placed before all records!

 

There are two ways to solve such problems. The simplest one is to use coalesce to eliminate the impact of null:
 

-In the output, convert null to 0: select name, coalesce (points, 0) from users order by 2 desc;-leave null in the output, but convert it to 0 in sorting: select name, points from users order by coalesce (points, 0) desc;

 

Another method requires database support. It specifies whether to put the null value at the beginning or end of the sorting:
 

select name, coalesce(points, 0) from users order by 2 desc nulls last;

Of course, null can also be used to prevent errors, such as processing mathematical errors with a division of 0.

 

Divide by 0

A division of 0 is a very egg-painfull error. Yesterday, a good SQL statement was run, and an error occurred when the SQL statement was suddenly divided by 0. A common solution is to first use the case statement to determine whether the denominator (denominator) is 0 and then perform Division operations.

 

select case when num_users = 0 then 0  else total_sales/num_users end;

 

The ase statement method is really ugly, and the denominator is used repeatedly. If it's a simple situation, it's okay. If the denominator is a complicated expression, then the tragedy will come: It's hard to read, it's hard to maintain and modify, and it's a bunch of bugs.

At this time, we can look at the advantages of null. When nullif is used to convert the denominator to null when the denominator is 0, no error is reported. If num_users = 0, the returned result is null.
 

select total_sales/nullif(num_users, 0);

 
Nullif converts other values to null, while Oracle nvl converts null to other values.

 

If you do not want null, but want to convert it to 0 or another number, you can use the coalesce function on the basis of the previous SQL statement:
 

select coalesce(total_sales/nullif(num_users, 0), 0);

 
Convert null to 0.

 

Conclusion

Tony Hoare may regret his mistake, but at least the null problem can be easily solved. so go and practice new tricks, and stay away from the inactive trap (nullifying) dug out by null )!