Introduction to ISNULL functions in SQL

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