Summary of null values in MySQL

Null has a unique logic significance. The processing of null values once caused a headache for many friends. Today, we have sorted out some practical tips. I hope it will be helpful to you. For 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, you can easily determine the position of records with 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 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 |
+ ---------- + ----------- + ---------- +
1 row in SET (0.00 Sec)

All query conditions (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.