Search w3schools.com:

SHARE THIS PAGE

SQL NULL Functions


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

Look at the following "Products" table:

P_Id ProductName UnitPrice UnitsInStock UnitsOnOrder
1 Jarlsberg 10.45 16 15
2 Mascarpone 32.56 23  
3 Gorgonzola 15.67 9 20

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

We have the following SELECT statement:

SELECT ProductName,UnitPrice*(UnitsInStock+UnitsOnOrder)
FROM Products

In the example above, if any of the "UnitsOnOrder" values are NULL, the result is NULL.

Microsoft's ISNULL() function is used to specify how we want to treat NULL values.

The NVL(), IFNULL(), and COALESCE() functions can also be used to achieve the same result.

In this case we want NULL values to be zero.

Below, if "UnitsOnOrder" is NULL it will not harm the calculation, because ISNULL() returns a zero if the value is NULL:

SQL Server / MS Access

SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0))
FROM Products

Oracle

Oracle does not have an ISNULL() function. However, we can use the NVL() function to achieve the same result:

SELECT ProductName,UnitPrice*(UnitsInStock+NVL(UnitsOnOrder,0))
FROM Products

MySQL

MySQL does have an ISNULL() function. However, it works a little bit different from Microsoft's ISNULL() function.

In MySQL we can use the IFNULL() function, like this:

SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products

or we can use the COALESCE() function, like this:

SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))
FROM Products



W3Schools Certification

W3Schools' Online Certification

The perfect solution for professionals who need to balance work, family, and career building.

More than 10 000 certificates already issued!

Get Your Certificate »

The HTML Certificate documents your knowledge of HTML.

The HTML5 Certificate documents your knowledge of advanced HTML5.

The CSS Certificate documents your knowledge of advanced CSS.

The JavaScript Certificate documents your knowledge of JavaScript and HTML DOM.

The jQuery Certificate documents your knowledge of jQuery.

The XML Certificate documents your knowledge of XML, XML DOM and XSLT.

The ASP Certificate documents your knowledge of ASP, SQL, and ADO.

The PHP Certificate documents your knowledge of PHP and SQL (MySQL).

Your suggestion:

Close [X]

Thank You For Helping Us!

Your message has been sent to W3Schools.

Close [X]