Processing of NULL values in the MySQL database

We all know that MySQL database NULL has a very unique logic significance. The handling of NULL values is a headache. So today I specially sorted out some practical TIPS, I hope to provide you with some help in this regard. If you have any omissions, please criticize them.

1. Sort columns with null values

Table creation:

 
 

  
  
mysql> create table t1(col1 int primary key, col2 varchar(2),col3 int);   
  
  
Query OK, 0 rows affected (0.24 sec)  
 
 

Add data:

 
 

  
  
mysql> insert into t1 values (1,'A',10),(2,'B',NULL),(3,'C',NULL),(4,'D',50),(5,'E',30),(6,'F',NULL),(7,'G',20),(8,'H',90),(9,'I',NULL),(10,'J',60);   
  
  
Query OK, 10 rows affected (0.08 sec)   
  
  
Records: 10 Duplicates: 0 Warnings: 0  
 
 

We know that MySQL always treats NULL as the "minimum value" during sorting. For example:

 
 

  
  
mysql> select * FROM t1 order by 3;   
  
  
+------+------+------+   
  
  
| col1 | col2 | col3 |   
  
  
+------+------+------+   
  
  
| 6 | F | NULL |   
  
  
| 2 | B | NULL |   
  
  
| 3 | C | NULL |   
  
  
| 9 | I | NULL |   
  
  
| 1 | A | 10 |   
  
  
| 7 | G | 20 |   
  
  
| 5 | E | 30 |   
  
  
| 4 | D | 50 |   
  
  
| 10 | J | 60 |   
  
  
| 8 | H | 90 |   
  
  
+------+------+------+   
  
  
10 rows in set (0.00 sec)   
 
 

However, how to achieve the following results:

 
 

  
  
+------+------+------+   
  
  
| col1 | col2 | col3 |   
  
  
+------+------+------+   
  
  
| 1 | A | 10 |   
  
  
| 7 | G | 20 |   
  
  
| 5 | E | 30 |   
  
  
| 4 | D | 50 |   
  
  
| 10 | J | 60 |   
  
  
| 8 | H | 90 |   
  
  
| 2 | B | NULL |   
  
  
| 3 | C | NULL |   
  
  
| 6 | F | NULL |   
  
  
| 9 | I | NULL |   
  
  
+------+------+------+  
  
  
10 rows in set (0.00 sec)   
  
  
SOLUTION：   
  
  
mysql> SELECT col1, col2, col3 FROM (   
  
  
-> SELECT col1, col2, col3,CASE WHEN col3 IS NULL THEN 0 ELSE 1 END AS IS_NULL FROM t1) as n   
  
  
-> ORDER BY n.IS_NULL DESC, col3;  
 
 

Use the 'case' expression to construct an additional column, assign values to null values and non-null values respectively, and then use this additional column as the sorting condition.

 
 

  
  
mysql> select col1, col2, col3,CASE WHEN col3 IS NULL THEN 0 ELSE 1 END AS IS_NULL FROM t1;   
  
  
+------+------+------+---------+ |  
  
  
| col1 | col2 | col3 | IS_NULL |   
  
  
+------+------+------+---------+   
  
  
| 1 | A | 10 | 1 |   
  
  
| 2 | B | NULL | 0 |   
  
  
| 3 | C | NULL | 0 |   
  
  
| 4 | D | 50 | 1 |   
  
  
| 5 | E | 30 | 1 |   
  
  
| 6 | F | NULL | 0 |  
  
  
| 7 | G | 20 | 1 |   
  
  
| 8 | H | 90 | 1 |   
  
  
| 9 | I | NULL | 0 |   
  
  
| 10 | J | 60 | 1 |   
  
  
+------+------+------+---------+   
  
  
10 rows in set (0.00 sec)   
 
 

By constructing an additional column to sort NULL columns in the MySQL database, you can easily determine the position of records containing NULL values in the result set.

2. Use order by null to disable sorting.

If the query contains group by but we want to avoid sorting the result set to reduce consumption, we can use order by null to disable sorting.

 
 

  
  
R,'` A.Kk   
  
  
mysql> EXPLAIN SELECT SUM(col3),CASE WHEN col3 IS NULL THEN 0 ELSE 1 END AS IS_NULL FROM t1 GROUP BY IS_NULL\G;  
 
 

1. row

 
 

  
  
id: 1   
  
  
select_type: SIMPLE   
  
  
table: t1   
  
  
type: ALL    
  
  
possible_keys: NULL   
  
  
key: NULL   
  
  
key_len: NULL   
  
  
ref: NULL   
  
  
rows: 10   
  
  
Extra: Using temporary; Using filesort   
  
  
1 row in set (0.00 sec)   
  
  
mysql> EXPLAIN SELECT SUM(col3),CASE WHEN col3 IS NULL THEN 0 ELSE 1 END AS IS_NULL FROM t1 GROUP BY IS_NULL m)ORDER BY NULL\G;   
 
 

1. row

 
 

  
  
id: 1   
  
  
select_type: SIMPLE   
  
  
table: t1   
  
  
type: ALL   
  
  
possible_keys: NULL   
  
  
key: NULL   
  
  
key_len: NULL ts   
  
  
ref: NULL   
  
  
rows: 10   
  
  
Extra: Using temporary   
  
  
1 row in set (0.00 sec)  
 
 

Obviously, if order by null is used, no filesort is performed for the query. In large result sets, the filesort operation is usually quite time-consuming.

3. subquery not in and NOT EXISTS NULL

IN some cases, a subquery in the not in form returns an empty result set, but changes it to the not exists form, and then returns to normal, as shown below:
Table creation:

 
 

  
  
mysql> CREATE TABLE t2 (col1 int default NULL, col2 int default NULL);   
  
  
Query OK, 0 rows affected (0.01 sec)   
  
  
mysql> CREATE TABLE t3 (col1 int default NULL, col2 int default NULL);   
  
  
Query OK, 0 rows affected (0.01 sec)  
 
 

Add data:

 
 

  
  
mysql> INSERT INTO t2 VALUES (1,2),(1,3);   
  
  
Query OK, 2 rows affected (0.00 sec)   
  
  
Records: 2 Duplicates: 0 Warnings: 0   
  
  
mysql> INSERT INTO t3 VALUES (1,2),(1,NULL);   
  
  
Query OK, 2 rows affected (0.00 sec)   
  
  
Records: 2 Duplicates: 0 Warnings: 0  
 
 

Execute the following query:

 
 

  
  
mysql> SELECT * FROM t2 WHERE col2 NOT IN (SELECT col2 FROM t3);   
  
  
Empty set (0.00 sec)   
  
  
mysql> SELECT * FROM t2 WHERE NOT EXISTS (SELECT 1 FROM t3 WHERE t3.col2 = t2.col2);   
  
  
+------+------+   
  
  
| col1 | col2 |   
  
  
+------+------+   
  
  
| 1 | 3 |   
  
  
+------+------+   
  
  
1 row in set (0.00 sec)  
 
 

Why? This should begin with the particularity of MySQL database NULL:

There are three statuses in MySQL: True, False, and Unknown. Any NULL comparison operation is in the Unknown status, as shown below:

 
 

  
  
mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;   
  
  
+----------+-----------+----------+----------+   
  
  
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |   
  
  
+----------+-----------+----------+----------+   
  
  
| NULL | NULL | NULL | NULL |   
  
  
+----------+-----------+----------+----------+   
  
  
1 row in set (0.00 sec)  
 
 

All query conditions, such as ON, WHERE, and HAVING, process the Unknown state as False.

Therefore, the first query field is equivalent to: col2 not in (2, NULL) => col2 <> 2 AND col2 <> NULL => true AND Unknow => False
The query condition is always False, so no results are returned for this query.

Not exists is executed cyclically.

He first runs SELECT 1 FROM t3 WHERE t3.col2 = 2

The returned results are NOT output because the query condition is False after the not exists operation,

Next, run SELECT 1 FROM t3 WHERE t3.col2 = 3.

No response is returned. After the not exists operation, the query condition is True, and the query result is output.

Therefore, if a not in subquery does NOT return results, pay special attention to whether the result set of the inner query contains null values. If yes, you should try to rewrite the query to not exists format.

This can also lead to performance improvement in some cases. The above content is an introduction to MySQL database NULL. I hope you will get some benefits.