Application of SQL Server IsNull in database query _mssql

The application of IsNull in database query, especially when the statement is connected

For example, when the connection, a field does not have a value, but also left join to other tables will show empty,

IsNull can determine whether it is null, if it is given a default value

IsNull ("Field name", "Default data")

null values and ISNULL functions in SQL Server

The three major characteristics of NULL values are: 1 null value does not participate in statistics; 2) null values do not enter the evaluation expression; 3 cannot be compared to other values.

The so-called null value does not participate in statistics that is, in the use of statistical functions, all involved in the null value will be ignored (words may not be accurate), do not think it is not important, in fact, in some places it is very important. The null value does not enter the calculation that is, if there is a null value in the statistical calculation between the data, then it is not entered that does not participate in the calculation. This is also a problem that cannot be neglected. This is common even in life. For example, a person's bonus is a null value (no input data or other reasons, do not discuss), and his basic salary is not always 0?! (if 0, has long been T), the end of the total wages when the basic salary plus bonuses, and if the bonus is NULL, total wages = Basic salary + bonus (NULL), then when this happens, the total wage = basic wages?

The affirmative answer is: not equal to. Because the bonus is a null value, what value does the null value represent? Have no idea. Without knowing what the null value is, how can it be calculated? So the total wage equals NULL, and is also uncertain. In this way, there are employees dare in the company? In case you have a total salary of NULL is really empty, then what work AH?! This involves a casting problem, which is to cast the null value to 0 to make it business-meaningful. The cast keyword is the IS, and the syntax is NULL, so that calculations involving null values can be performed.

However, the null value does not affect all statistical functions. Generally speaking. Statistic average (AVG), the null value is bound to have an effect, when the statistic minimum value (min), the null value is likely to affect the min, in my opinion is a somewhat random property, the maximum value (max) or statistics and (null), the null value is completely unaffected.

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

Judge Null value statement: SELECT * from table where field is null;

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

In short, we have to take null values seriously, preferably when using statistical functions, plus is null to prevent accidental occurrences.

An example of the use of IsNull in SQL Server

There is a column in the database that records the number of times the article was accessed. The function I am going to implement now is to refresh the page every time. Number of visits +1. SQL statement, Art_count is the number of accesses, int type.
Update article Set art_count= "(art_count+1) where art_id=" 3 but if art_count is null, it does not work.
If Oracle uses decode, it is easy to implement this feature. How do you implement similar functions in SQL Server?
SQL Server has a function isnull, this function has two parameters IsNull (P1,P2) Its usage is if P1 is null, then replace with P2.
This function is similar to Oracle's NVL. For example
SELECT AVG (ISNULL price, $10.00) The from titles are inspired by this function I write SQL statements like this
Update article Set art_count= "(IsNull (vote_count,0) +1) where art_id=" 3 "