SELECT COALESCE AS col_1, COALESCE AS col_2, COALESCE AS col_3;

Do you know these NULL-related details in

SQL?

NULL is one of the common keywords in SQL, which means "empty, none". It is a unique existence in SQL. Today, let’s summarize the knowledge points related to it. Do you know all of them?

First post our original data. It is a table with only 1 column. The table name is example. It is very simple:

.NULL is a special value. When using the distinct keyword for a certain field, NULL will be heavy-duplicated like the general value, and only one value is retained.

. You cannot use the comparison operator for NULL values ​​

. To put it bluntly, you cannot use the equal sign (=) or the inequality sign (!=) for null values ​​for comparison. You must use is null and is not null.

There is a situation that needs to be noted, assuming we need to take all col values ​​whose col is not 2, including null. You cannot just write where col '2', because such writing will not include NULL values. We need to write where col '2' or col is null.

.count(*) will count the null value, and count (column name) does not include the null value.

. The calculation results containing NULL values ​​are NULL, as shown in the figure below (click to view the larger image)

. When using the sum function and the avg function, what will happen if the corresponding column contains NULL?

sum and avg functions act on columns containing NULL, and the NULL value does not participate in the calculation. In the above figure, sum(col) is 1+2+2+3=8. avg(col) is (1+2+2+3)/4=2, note that the denominator is 4 instead of 6. If you need to participate in the operation as a 0 value, you can use case when to determine the assignment.

select sum(case when col is null then 0 else col end) from example;#The result is 8

select avg(case when col is null then 0 else col end) from example;#The denominator is 6, and the result is 1.33

In addition, when using max and min, the NULL value will also be ignored. In fact, if the aggregate function takes the column name as a parameter, NULL will be excluded before calculation.

. If a column contains null, when using group by for aggregation, the null value will be retained in a single row.

This point is a bit similar to the first point, see the code below.

.null takes up?

We insert a line of empty string data based on the original data. Then look at the space occupied by each value. As you can see, the space occupied by NULL is NULL, which is occupied by , and the empty string length is 0, which does not occupy space.

NULL columns require additional space in the row to record whether their values ​​are NULL.
NULL columns require additional space in the row to record whether their values ​​are NULL.

has a very appropriate metaphor: empty value is like a vacuum cup with nothing, while NULL value is a cup full of air. Although it all looks the same, it has essential differences.

Supplementary Note: For judging null values, you need to use arithmetic operators such as =, !=, and NULL values ​​do not work. Aggregation functions such as count ignore NULL values, but not null values.

. Sort NULL, what are the results? The above results in

, sorted ascendingly, NULL is at the beginning, but this does not mean that NULL is smaller than 1, because we mentioned earlier that comparison operators cannot be used for NULL. The result here is just to display NULL at the beginning, and it may be displayed at the end in another database.

. Most functions act on NULL, and the result is NULL, such as concat function, abs function, etc. Except for the COALESCE function, it returns the first value that is not NULL. The NVL function we often see is a simplified version of this function, and similar functions also include IFNULL.

SELECT COALESCE(NULL, 1) AS col_1,

COALESCE(NULL, 'test', NULL) AS col_2,

COALESCE(NULL, NULL, '2009-11-01') AS col_3;

--Result: 1 test 2009-11-01

0.NULL Other functions

NULL is mostly used in field constraints. If non-empty constraints can be represented by NOT NULL. NULL is often used in the ELSE clause in case expressions: case when condition else NULL end, the else part can also be not written, but for the sake of readability, it is still recommended to write it.

Summary

Summary of common knowledge points for NULL is as follows: the mind map below, welcome to add it. (In some places, I have seen the precautions and index-related knowledge about NULL when inserting and updating data. Since I use it less, I will not put it here. You can refer to the link at the end of the article to learn it yourself)