Drill down to specifically explain 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. In the future, I don't know how much money I have in the Fangxiaoming pocket, but I'm not sure if it's 0, then NULL is used in the computer to represent the unknown and indeterminate.

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

Tony Hoare invented the null reference in 1965 and felt it was "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 inference

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". Other words. It is meaningless to compare the other values with the regular comparison operator (normal conditional operators) null . Nullnor is it equal Null to (Approximate understanding: An unknown value cannot be equal to an unknown value.) The relationship between the two is also unknown. Otherwise the math and logic will be messed up).

– Note : The following SQL is suitable for MySQL, assuming 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 to another null is to use 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: Users who are all marked for deletion

Suppose you want to infer that the values of two columns are not the same, you can use is distinct from :

select * from userswhere has_address is distinct from has_photo

–result: Address or photograph (photo) Only one of the users

Not in and Null

Sub-query (subselect) is a very convenient way to filter data. For example, if you want to query a user who doesn't have any packages, you can write a query like this:

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

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

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. id != nullThe result is an unknown value null . The random values and the results of the null and operation are null , therefore, equivalent to no other conditions. The reason for such a result is that null the logical value is not true .

If the condition is reversed, the query results will be no problem. We now look for users with a package.

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

The same we can use simple examples:

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 irrelevant. The non-True (non-true) value does not affect the results of the other parts of the clause, which is equivalent to being ignored.

Null and sort

When sorting, null values are thought 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;

Another way to do this is to have the database support, specifying whether the null value is placed first or last in the sort order:

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

Of course, NULL can also be used to prevent errors, such as dealing with a 0-divisor math error.

be removed by 0

A divisor of 0 is a very egg-painfull error. Yesterday also executed a good SQL. All of a sudden it was 0 apart and suddenly it went wrong. A frequently used workaround is to use the case statement to infer whether the denominator (denominator) is 0 and then divide.

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

The way the case statement is actually very ugly, and the denominator is reused.

Assuming that the simple case is OK, assuming that the denominator is a very complex expression, then the tragedy comes: very difficult to read, very difficult to maintain and change, accidentally is a bunch of bugs.

At this time we can look at null the advantages. 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.

Suppose you do not want NULL, but instead 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 the null problem is very easy to conquer. So go and practice the new big tricks now. From this null dug out of the void 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

Drill down to specifically explain NULL in SQL