Document directory
- SQL Server/MS Access
- Oracle
- MySQL
ISNULL
Replace NULL with the specified replacement value.
Syntax
ISNULL (check_expression, replacement_value)
Parameters
Check_expression
Whether the expression is NULL is checked. Check_expression can be of any type.
Replacement_value
The expression returned when check_expression is NULL. Replacement_value must be of the same type as check_expresssion.
Return type
Returns the same type as check_expression.
Note
If check_expression is not NULL, the value of this expression is returned; otherwise, the value of replacement_value is returned.
Example
A. Use ISNULL With AVG
The following example finds the average price of all books and replaces all NULL entries in the price column of the titles table with the value $10.00.
USE pubs
GO
Select avg (ISNULL (price, $10.00 ))
FROM titles
GO
The following is the result set:
--------------------------
14.24
(1 row (s) affected)
B. Use ISNULL
The following example shows how to select the title, type, and price for all books in the titles table. If the price for a title is NULL, the price displayed in the result set is 0.00.
USE pubs
GO
Select substring (title, 1, 15) AS Title, type AS Type,
ISNULL (price, 0.00) AS Price
FROM titles
GO
The following is the result set:
Title Type Price
-----------------------------------------------------
The Busy Execut business 19.99
Cooking with Co business 11.95
You Can Combat business 2.99
Straight Talk A business 19.99
Silicon Valley mod_cook 19.99
The Gourmet Mic mod_cook 2.99
The Psychology UNDECIDED
SQL ISNULL (), NVL (), IFNULL (), and COALESCE () Functions
See the following "Products" table:
P_Id |
ProductName |
UnitPrice |
UnitsInStock |
UnitsOnOrder |
1 |
Computer |
699 |
25 |
15 |
2 |
Printer |
365 |
36 |
|
3 |
Telephone |
280 |
159 |
57 |
Assume that "UnitsOnOrder" is optional and can contain NULL values.
We use the following SELECT statement:
SELECT ProductName,UnitPrice*(UnitsInStock+UnitsOnOrder)FROM Products
In the preceding example, if the "UnitsOnOrder" value is NULL, the result is NULL.
Microsoft's ISNULL () function is used to specify how to process NULL values.
NVL (), IFNULL () and COALESCE () functions can also achieve the same result.
Here, we want the NULL value to be 0.
If "UnitsOnOrder" is NULL, It is not conducive to calculation. Therefore, if the value is NULL, ISNULL () returns 0.
SQL Server/MS Access
SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0))FROM Products
Oracle
Oracle does not have the 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 also has functions similar to ISNULL. However, it works in a different way than 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