Application of sqlserver isnull in Database Query

Isnull is used in database queries, especially when the statement is connected.

For example, if a field has no value but needs to be left connected to another table during connection, it will be empty,

Isnull can be used to determine whether it is NULL. If it is a default value

Isnull ("field name", "default data ")

The null Value in SQL Server and the IsNull Function

Three major features of NULL values: 1) NULL values do not participate in statistics; 2) NULL values do not enter the computing expression; 3) They cannot be compared with other values.

When NULL values do not participate in statistics, that is, when using statistical functions, all NULL values involved will be ignored (the words may be inaccurate). Do not think this is not important, in fact, this is very important in some places. The NULL value does not enter the calculation, that is, if there is a NULL value in the statistical calculation between data, it does not enter or participate in the calculation. This is also a problem that cannot be ignored. This is even common in life. For example, if a person's bonus is NULL (there is no data input or other reasons, so don't discuss it), and his basic salary is not always 0, right ?! (If it is 0, it has long been replaced by T.) When the total salary is calculated at the end of the month, the basic salary is added with a bonus. If the bonus is NULL, total salary = basic salary + bonus (NULL), then when this happens, total salary = basic salary?

A positive answer is: not equal. Because the bonus value is NULL, what is the value of NULL? I don't know. But how can we calculate the NULL value? So the total salary is equal to NULL, which is also uncertain. In this way, do employees dare to work in the company? What kind of work should you do if NULL of your total salary is really empty ?! This involves a forced conversion problem, that is, to forcibly convert the NULL value to 0, so that it has business significance. The mandatory conversion keyword IS, And the syntax is null. In this way, the calculation involving NULL values can be performed.

However, the NULL value does not affect all statistical functions. In general. When calculating the average value (AVG), the NULL value will certainly have an impact. When calculating the minimum value (MIN), the NULL value may have an impact on MIN, which is a random attribute in my opinion; when the maximum value (MAX) or statistical value (NULL) is counted, the NULL value has no effect on it.

So there is another saying: null values do not participate in statistics, do not participate in calculation, and can only be judged by is.

Statement for determining the Null value: select * from table where field is null;

Convert a null value statement: select Field 1, Field 2, Field 3, is null (Field 3, 'a value') from table;

In short, we should take the NULL value seriously. It IS best to add is null when using the statistical function to prevent unexpected occurrence.

An example of isnull in sqlserver

There is a column in the database that records the number of visits to articles. What I want to implement now is to refresh the page every time. Number of visits + 1. SQL statement. art_count indicates the number of accesses. It is of the int type.
Update article set art_count = "(art_count + 1) where art_id =" 3 but it does not work if art_count is NULL.
If decode is used for oracle, this function can be easily implemented. How does sqlserver implement similar functions?
Sqlserver has a function isnull, which has two parameters: isnull (p1, p2). If p1 is null, it is replaced by p2.
This function is similar to the nvl of oracle. For example
Select avg (ISNULL (price, $10.00) FROM titles is inspired by this function to write SQL statements like this
Update article set art_count = "(isnull (vote_count, 0) + 1) where art_id =" 3"