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 |
Need an easy way to get data into XML, or transform XML to another format?
MapForce lets you map XML data to/from any combination of XML, database, flat file, Excel 2007, XBRL, or Web services data.
Then it transforms data instantly or auto-generates royalty-free data integration code for recurrent conversions.
Download a free, fully functional 30-day trial to experience the following features:
- Easy-to-use, graphical data mapping interface
- Instant data transformation
- XSLT 1.0/2.0 and XQuery code generation
- Java, C#, and C++ code generation
- Advanced data processing functions
- Support for all major relational databases including SQL Server, IBM DB2, Oracle, and more
- Visual Studio & Eclipse integration
Download a fully-functional trial today!
|
|
|
|
