In-depth explanation of Null in SQL and SQLNull

In-depth explanation of Null in SQL and SQLNull

NULL represents unknown in the computer and programming world and is uncertain. Although the Chinese translation is "empty", this null is not empty ). Null indicates an unknown state and future state. For example, I don't know how much money James has in his pocket, but it cannot be zero, in this case, Null is used in the computer to indicate unknown and uncertain.

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, regardless of the tableusersIf there are many records, the returned records are all 0 rows:

select * from users where deleted_at = null;

-Result: 0 rows

select * from users where deleted_at != null;

-Result: 0 rows

How can this happen? All becausenullIs an "unknown" type. That is to say, using the regular comparison operator (normal conditional operators)nullIt is meaningless to compare with other values.NullOr equalNull(Approximate understanding: an unknown value cannot be equal to an unknown value. The relationship between the two is also unknown. Otherwise, it will be messy in mathematics and logic ).

-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

Associate a valuenullThe correct method for comparison is to useisKeyword, andis notOPERATOR:

select * from userswhere deleted_at is null;

-Result: All undeleted users

select * from userswhere deleted_at is not null;

-Result: All users marked as deleted

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

select * from userswhere has_address is distinct from has_photo

-Result: the address or photo address has 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, ifpackagesTheuser_idYesnullThe problem is: The returned result is empty! 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 that,id != nullThe result is an unknown value,nullAnd any value andnullProceedandThe operation results arenullSo there are no other conditions. The reason for this isnullThe logical value of is nottrue.

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

BecausewhereClause is a stringorCondition, so one of the results isnullIt is also irrelevant. 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, pointsfrom usersorder by 2 desc;

-Points isnullBefore all records!

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

-Convert null to 0 in the output:

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

-Null is reserved for output, but is converted to 0 for sorting:

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

Another method requires database support. When sorting is specifiednullPut the value at the beginning or at the end:

select name, coalesce(points, 0)from usersorder 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 usecaseStatement to determine whether the denominator (denominator) is 0 and then perform division.

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

The case statement is actually 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.

Now let's take a look.nullBenefits. UsenullifWhen the denominator is 0nullIn this way, no error is reported,num_users = 0The returned result is null.

select total_sales/nullif(num_users, 0);

nullifConvert other values to null, while Oracle'snvlIs to convert null to other values.

If you do not want null, but want to convert it to 0 or another number, you can usecoalesceFunction:

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 )!

Original article: Understanding SQL's Null

Original Article Date: January 1, March 17, 2015

Translated on: February 1, March 18, 2015

Iron anchor http://blog.csdn.net/renfufei