How to handle SQL Null values
An example of the use of IsNull in SQL Server
There is a column in the database tutorial 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 "
-Determine if some of the fields are empty
--case
Select Case ' field name ' is null then ' n ' else convert (varchar (20), ' Field name ') End as ' newname '
Select Case when null are null then ' n ' else convert (varchar, NULL) end as ' newname '
--sql Server 2005:coalesce
Select COALESCE (' String Type field ', ' n ') as ' newname '
Select COALESCE (CONVERT (varchar (20), ' Non-string Type field '), ' n ') as ' newname '
Select COALESCE (CONVERT (varchar, null), ' n ') as ' newname '
--COALESCE, returns the first non-empty expression in its argument
Select COALESCE (null,null,1,2,null) union
Select COALESCE (null,11,12,13,null) union
Select COALESCE (111,112,113,114,null)
Null values also do 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 the null value seriously, preferably when using statistical functions, plus is null to prevent accidental occurrence