Examples of SQL Server IsNull applications

Server|sqlserver

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

An expression to 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
Returns the value of the expression if check_expression is not NULL, otherwise return replacement_value.

Example
A. Use ISNULL with AVG
The following example finds the average price of all books and replaces all NULL entries in the prices column of the titles table with the value $10.00.

Use pubs
Go
SELECT AVG (ISNULL (Price, $10.00))
From titles
Go

Here is the result set:

--------------------------
14.24

(1 row (s) affected)

B. Use of ISNULL
The following example selects the title, type, and price for all the books in the titles table. If the price of a title is NULL, the price shown in the result set is 0.00.

Use pubs
Go
SELECT SUBSTRING (title, 1,) as title, type as type,
ISNULL (Price, 0.00) as Price
From titles
Go

C. Using IsNull in full join
Table A:
Tid
Uid
Anum

Table B:
Tbid
Uid
Bnum1
Bnum2
Two tables need to be fully connected via UID:
Select A.tid,a.uid,a.anum,b.bnum1,b.bnum2 from a full join B on a.uid=b.uid
Full connection will have a lot of empty cases, you can use IsNull to resolve, instead:
Select IsNull (A.tid,b.tid), IsNull (A.uid,b.uid), IsNull (a.anum,0), IsNull (b.bnum1,0), IsNull (b.bnum2,0) from a full Join B on A.uid=b.uid

Plus a comprehensive usage in asp.net2.0:

<%@ Page language= "C #"%><! DOCTYPE HTML PUBLIC "-//W3C//DTD XHTML 1.0 transitional//en" "Http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd ">