ISNULL ()
Replaces null with the specified replacement value.
Grammar
ISNULL (Check_expression, Replacement_value)
Parameters
Check_expression
An expression that will be checked for null. Check_expression can be of any type.
Replacement_value
The expression that will be returned when check_expression is null. Replacement_value must have the same type as check_expresssion.
return type
Returns the same type as check_expression.
Comments
If Check_expression is not NULL, the value of the expression is returned, otherwise Replacement_value is returned.
Example
A. Using ISNULL with AVG
The following example finds the average price for all books and replaces all null entries in the Value column of the titles table with the values $10.00.
Usepubs
GO
Selectavg (ISNULL (Price, $10.00))
Fromtitles
GO
here is the result set :
--------------------------
14.24
(1row (s) affected)
B. Using ISNULL
The following example selects the title, type, and price for all books in the titles table. If the price of a title is NULL, then the price shown in the result set is 0.00.
Usepubs
GO
Selectsubstring (title, 1,) as title, type as type,
ISNULL (Price, 0.00) AS-price
Fromtitles
GO
C. Using IsNull in full join case
Table A:
Tid
Uid
Anum
Table B:
Tbid
Uid
Bnum1
Bnum2
All two tables need to be connected by UID:
Selecta.tid,a.uid,a.anum,b.bnum1,b.bnum2 from a full join B ona.uid=b.uid
The full connection will be a lot of empty cases, you can use IsNull to solve, instead:
Selectisnull (A.tid,b.tid), IsNull (A.uid,b.uid), IsNull (a.anum,0), IsNull (B.BNUM1,
Summary: The most important feature of this function is to assign a default value to a null value, to avoid the occurrence of null values for non-null properties!
Introduction to. NET Foundation--mssql functions IsNull ()