SQL NULL Functions

SQL IFNULL(), ISNULL(), COALESCE(), and NVL() Functions

Look at the following "supplier" table:

Suppose that the "City" column is optional, and may contain NULL values.

Look at the following SELECT statement:

 SELECT Supplier_name, Address * (City + Pincode)
 FROM  supplier;

You can click on above box to edit the code and run again.

Solutions in MySQL

The MySQL IFNULL() function lets you return an alternative value if an expression is NULL:

 SELECT Supplier_name, Address * (City + IFNULL(Pincode,  0 ))
 FROM  supplier;

or we can use the COALESCE() function:

 SELECT Supplier_name, Address * (City +  COALESCE(Pincode,  0 ))
 FROM  supplier;

The MySQL ISNULL() function lets you return an alternative value if an expression is NULL:

 SELECT Supplier_name, Address * (City + ISNULL(Pincode,  0 ))
 FROM  supplier;

or we can use the COALESCE() function:

 SELECT Supplier_name, Address * (City + COALESCE(Pincode,  0 ))

 FROM  supplier;

The MS Access IsNull() function returns TRUE (-1) if the expression is a null value, otherwise FALSE (0):

 SELECT Supplier_name, Address * (City + IIF(IsNull(Pincode),  0 , Pincode))
 FROM  supplier;

The Oracle NVL() function achieves the same result:

 SELECT Supplier_name, Address * (City + NVL(Pincode,  0 ))
 FROM  supplier;

or we can use the COALESCE() function:

 SELECT Supplier_name, Address * (City + COALESCE(Pincode, 0  ))
 FROM  supplier;