SQL- NULL Functions

1. ISNULL() : 

Syntax:

ISNULL ( check_expression , replacement_value )

It replaces the NULL value with replacement value. If the Check_expression evaluates to NULL then it is replaced with the replacement value else the value of Check_expression is returned.

ISNULL function can be used both in SQL SERVER and MSAccess

Let's understand with examples: Consider a below table SalesInfo for illustrations:

SELECT * FROM SalesInfo

We see that some of the products has not been sold . This is is evident as we can see there are NULL in QuantitySold column. Now,for better understanding we want  to display zero insteadof  NULL in QuantitySold column,we can do this by using ISNULL function.

SELECT ProductId,ProductName,Price,

ISNULL(QuantitySold,0)
FROM SalesInfo

Suppose we want to calculate, sales per item. For this we need to multiply the unit price and QuantitySold. If the Quantity sold is NULL, we can not get the correct result because multiplication of any number with NULL is always NULL. In such scenario, ISNULL() function is very useful. We can easily replace all NULL values with zero using ISNULL() function.

SELECT ProductName,
Price*(ISNULL(QuantitySold,0))AS SalePerItem
FROM SalesInfo

2. NVL() :

Syntax:

NVL( check_expression , replacement_value )

Just like ISNULL function in SQL SERVER, we have NVL function in ORACLE. It replaces the NULL value with replacement value. If the Check_expression evaluates to NULL then it is replaced with the replacement value else the value of Check_expression is returned.

This function holds the same explaination as that of ISNULL function.

SQL> SELECT ProductId,ProductName,Price,NVL(QuantitySold,0)
   2      FROM SalesInfo;

NOTE: IFNULL performs the same function as ISNULL () and NVL()
in Mysql database.

3. COALESCE():

 Syntax:
 COALESCE(Expression1, Expression2[,Expression3.........ExpressionN)
It returns the first non null value among its arguments.

Let us understand from the 'TelephoneRecords' Table.

SELECT * FROM TelephoneRecords

We want to display the Name and Mobile number of each person. For displaying the mobile number, Primary No should be displayed. If the Primary number is not available then Alternate No should be displayed.If Alternate No is not available then Office No should be displayed. For such a scenario, COALESCE function will serve our purpose as it always returns the first non null value.

SELECT Name,
COALESCE(PrimaryNo,AlternateNo,OfficeNo)
AS MobileNo
FROM TelephoneRecords

NOTE: If all the arguments in COALESCE function returns NULL then output will also be NULL.

It can also work similar to ISNULL function.

SELECT Name,COALESCE(PrimaryNo,0) 

AS MobileNo

FROM TelephoneRecords

We see that all NULL values replaced with zero.