Can someone explain the usage of isnull vs is null etc 1

[jasperjasper]

Hi

I am somewhat new to t-sql and having some trouble understanding the nuances of its usage of null...
There is ISNULL and I think there is an IS NULL and a NIULLIF....oy!

Can you test a variable or a value if it is null....

Thanks

 

[Snowcrash]

ISNULL:-
The ISNULL Function replaces NULL with the specified replacement value and is like an If Then function. It takes 2 arguments: the first is the field which you are testing and the second is the value you wish to replace the NULL with if encountered. So the statement

SELECT CountryID, ISNULL(Country, 'Unknown') AS Country
FROM Tbl_Country

would return replace NULL with Unknown in the resultset.

IS NULL:-
The IS NULL determines whether or not a given expression is NULL and is used to "weed out" the NULL values eg

SELECT CountryID, Country
FROM Tbl_Country
WHERE LanguageID IS NULL

would return all country records where LanguageID is NULL

NULLIF:-
The NULLIF function returns a null value if the two specified expressions are equivalent so if you wanted to only return 1 phone number if a user works from home you would write:

SELECT UserID, Home_Ph, NULLIF(Home_Ph, Work_Ph) AS Work_No
FROM Tbl_Users

Snowcrash

 

[jasperjasper]

THank you so much. Ever think about writing a t-sql guide!!!!!!

 

[Katy44]

Thank you! I have used all those functions in the past, but each time I have to search around for the one I need and get confused.

 

[TysonLPrice]

Here is a nice easy to read write up on NULLS.

http://www.sqlservercentral.com/columnists/dPriyankara/nullfunctionsarentthosemeetings.asp