In-depth explanation of NULL in SQL

NULL in the computer and programming world represents an unknown, indeterminate. Although the Chinese translation is "empty", this empty (null) is not Pethin (empty). Null represents an unknown state, the future state, such as how much money in Xiao Ming's pocket I do not know, but also cannot be sure of 0, at this time in the computer using NULL to represent unknown and indeterminate.

Although the person who is proficient in SQL will not have any questions about NULL, but the whole article is still very difficult to find, see an English version, feel good.

Tony Hoare invented the null reference in 1965 and considered it a "billions of dollar mistake" he had made. Even today, 50 years later, a null value in SQL is the culprit for many common mistakes.

Let's look at the most shocking cases together.

Null does not support size/equality judgment

The following 2 queries, regardless of users how many records are in the table, return a record of 0 rows:

select * from users where deleted_at = null;

–result:0 rows

select * from users where deleted_at != null;

–result:0 rows

How could it be like this? All because null it is a type that represents an "unknown". That null is, it is meaningless to compare the other values with the regular comparison operator (normal conditional operators). Nullnor is it equal Null to (Approximate understanding: The unknown value cannot equal the unknown value, the relationship between the two is unknown, otherwise the math and logic will be messed up).

– Note : The following SQL is 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 way to compare a value with null is to use the is keyword, as well as the is not operator:

select * from userswhere deleted_at is null;

–result: All users who have not been deleted

select * from userswhere deleted_at is not null;

–result: All users marked for deletion

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

select * from userswhere has_address is distinct from has_photo

–result: Addresses or photos (photo) Only one of the users

Not in and Null

Sub-query (subselect) is a convenient way to filter data. For example, if you want to query a user who does not have any packages, you can write a query such as the following:

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

But if there packages is a row in the user_id table null , the problem is: The return result is empty! To understand why this weird thing happens, we need to understand what the SQL compiler is doing. Here's a simpler example:

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

This SQL statement will be converted to:

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

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

If the condition is reversed, the query results are no problem. Now we query the users who have the package.

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

Again we can use a simple example:

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

This SQL is converted to:

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

Because the where clause is a string of or conditions, one of the results is null also irrelevant. The non-True (non-true) value does not affect the calculation of other parts of the clause, which is equivalent to being ignored.

Null and sort

When sorting, null values are considered to be the largest. When sorting in descending order (descending) This will make you very big, because the null value is first.

The following query is to show the rank of the user based on the score, but it ranks the user without scoring to the front!

select name, pointsfrom usersorder by 2 desc;

–points null records are queued before all records!

There are two ways to solve this kind of problem. The simplest is to use coalesce to eliminate the effect of NULL:

– Convert null to 0 on output:

select name, coalesce(points, 0)from usersorder by 2 desc;

– The output retains null, but is converted to 0 when sorted:

select name, pointsfrom usersorder by coalesce(points, 0) desc;

There is also a way to support the database, specifying whether the null value is placed first or last when sorting:

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

Of course, NULL can also be used to prevent errors, such as handling mathematical errors with a divisor of 0.

be removed by 0

A divisor of 0 is a very egg-painfull error. Yesterday also run a good SQL, suddenly by 0 except a sudden error. A common workaround is to first use the case statement to determine if the denominator (denominator) is 0, and then divide the operation.

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

The way the case statement is actually very difficult to see, and the denominator is reused. If this is a simple situation, if the denominator is a complex expression, then the tragedy comes: 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 null the benefits. Use nullif to make the denominator 0 o'clock null . This will no longer cause an error, and the num_users = 0 returned result becomes null.

select total_sales/nullif(num_users, 0);

nullifis to convert the other values to NULL, while Oracle nvl converts null to a different value.

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

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

Null and then convert back to 0

Conclusion

Tony Hoare may regret his mistake, but at least a null problem can easily be solved. Then go to practice the new big strokes, from the null dug out of the invalid Big Pit (nullifying)!

Original link: Understanding SQL ' s Null

Original date: March 17, 2015

Translation Date: March 18, 2015

Translators: Anchor Http://blog.csdn.net/renfufei

In-depth explanation of NULL in SQL