SQL isnull function Usage Analysis in various databases

This article introduces some differences and issues about using the is null function in mainstream databases. If you need it, you can give a simple reference.

Isnull is used in database queries, especially when the statement is connected.

For example, if a field has no value but needs to be left connected to another table during connection, it will be empty,

Isnull can be used to determine whether it is NULL. If it is a default value

 

Isnull ("field name", "default data ")

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.

 

The Code is as follows:	Copy code
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.

The Code is as follows:	Copy code
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:

The Code is as follows:

Copy code

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 0.00 But Is It User popular_comp 22.95 Secrets of Sili popular_comp 20.00 Net Etiquette popular_comp 0.00 PC Phobic psychology 21.59 Is Anger the En psychology 10.95 Life Without Fe fig 7.00 Prolonged Data psychology 19.99 Emotional Secur psychology 7.99 Onions, Leeks, trad_cook 20.95 Policty Years in trad_cook 11.95 Sushi, Anyone? Trad_cook 14.99 (18 row (s) affected)

Foreign descriptions

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

The Code is as follows:	Copy code
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:

The Code is as follows:	Copy code
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:

The Code is as follows:	Copy code
SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE (UnitsOnOrder, 0 )) FROM Products