Sometimes when retrieving data from the Database we don't want to return NULL but instead of NULL we want to return some friendly message saying 'This record is not found'. In these situations CASE function is very helpful. Look at a simple example below:
SELECT P.Name,Ph.PhoneNumber,
'CellNumber' =
CASE WHEN Ph.CellNumber IS NULL THEN
'No CellNumber Found'
ELSE
Ph.CellNumber
END
FROM tblPerson P, tblPhone Ph
WHERE P.PersonID = Ph.PersonID