Detailed explanation of NULL values in the database language _mysql

Although proficient in the SQL of the people do not have any questions about NULL, but summed up a very full 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 committed. Even today, 50 years later, the null value in SQL is the culprit for many common errors.

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

Null does not support size/equality judgment

The following 2 queries, regardless of how many records are in the table users, 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 this be? Everything just because null is a type that represents an "unknown". That is, it does not make sense to compare null with other values using the regular comparison operator (normal conditional operators). Null is also not equal to NULL (approximate understanding: Unknown value can not equal 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, and 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 to NULL is to use the IS keyword and the is not operator:

SELECT * from users
 
where deleted_at is null;
 
–result: All users who are marked for deletion

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

SELECT * from users
 
where has_address are distinct from Has_photo
 
–result: addresses or photos (photo) Only one user

Not in and Null

Subqueries (subselect) are 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 the users 
 
where ID not in (select user_id from packages)

But at this point, if the user_id of a row in the packages table is 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. The following is a simpler example:

SELECT * from users 
 
where ID is not in (1, 2, NULL)

This SQL statement is 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. The result of an and operation of any value and null is NULL, so it is equivalent to no other condition. The reason for this result is that the logical value of NULL is not true.

If the conditions are reversed, the query results will be fine. Now we are looking for users with package.

SELECT * from users 
 
where IDs in (select user_id from packages)

Again, 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, it is irrelevant that one of the results is null. Non-True (non-true) values do not affect the calculations 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. In descending order (descending) This makes you very large, because null values are at the top of the line.

The following query is to display the ranking of users based on the score, but it will not score users to the front!

Select name, points from the users order by
 
2 desc;
 
–points-null records are ranked before all records!

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

– converts null to 0 on output:
 
select name, coalesce (points, 0) from the
 
users order by
 
2 desc;
 
– The output is reserved for null, but is converted to 0 when sorted:
 
select name, points from users ordered by
 
Coalesce (points, 0) desc;

There is also a way to support the database by specifying whether to put the null value at the front or the last side of the sort:

Select name, coalesce (points, 0) from the users order through
 
2 desc nulls last;

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

by 0 apart

A divisor of 0 is a very egg-painfull error. Yesterday also ran a good SQL, suddenly 0 in addition to a sudden error. A common solution is to use the case statement to determine whether the denominator (denominator) is 0, and then the division operation.

Select Case When num_users = 0 Then 0 
 
else total_sales/num_users end;

The way ASE statements are actually very difficult to see and the denominator is reused. If the situation is simple, if the denominator is a very complex expression, then the tragedy comes: it is difficult to read, difficult to maintain and modify, carelessly is a bunch of bugs.

At this point we can look at the benefits of NULL. Use Nullif to make the denominator 0 o'clock null. This will no longer be an error, Num_users = 0 o'clock Returns the result becomes null.

Select Total_sales/nullif (num_users, 0);

Nullif is to convert other values to NULL, and Oracle's NVL converts null to other values.

If you do not want NULL, but you want to convert to 0 or other numbers, you can use the COALESCE function on the basis of the previous SQL:

Select COALESCE (Total_sales/nullif (num_users, 0), 0);

Null and convert back to 0

Conclusion

Tony Hoare may regret his mistake, but at least the problem of NULL is easily solved. So quickly to practice the new big recruit, henceforth away from null to dig out of the void Big Pit (nullifying)!