Introduction to ISNULL functions in SQL

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.