The usage analysis of SQL IsNull function in various databases

Source: Internet
Author: User

The application of IsNull in database query, especially when the statement is connected

For example, when the connection, a field does not have a value, but also left join to other tables will show empty,

IsNull can determine whether it is null, if it is given a default value

IsNull ("Field name", "Default data")

Replaces NULL with the specified replacement value.

ISNULL (Check_expression, Replacement_value)


An expression that will be checked for null. Check_expression can be of any type.


An expression to be returned when check_expression is null. Replacement_value must have the same type as check_expresssion.

return type
Returns the same type as check_expression.

Returns the value of the expression if check_expression is not NULL, otherwise return replacement_value.

A. Use ISNULL with AVG
The following example finds the average price of all books and replaces all NULL entries in the prices column of the titles table with the value $10.00.

  code is as follows copy code
  use     pubs  
   select    AVG ( ISNULL (price,    $10.00))   
   from    titles  

   Below is the result set:  
  ---------- ----------------    
   (1    row (s)     affected)  & nbsp
   b.    use     isnull  
    The following example selects the title, type, and price for all the books in the     titles    table. If the price of a book title is     NULL, the price shown in the result set is     0.00.   &NBSP

  code is as follows copy code
use     pubs  
   select    SUBSTRING ( title,    1,   )     as    title,    type    as    type,    
          ISNULL (price,    0.00)     as    price  
   from    titles  

Here 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
Computer phobic Psychology 21.59
Is anger the En psychology 10.95
Life without Fe psychology 7.00
Prolonged Data psychology 19.99
Emotional Secur Psychology 7.99
Onions, leeks, trad_cook 20.95
Fifty Years in Trad_cook 11.95
Sushi, Anyone? Trad_cook 14.99

(s) affected)

Some foreign notes

In the example above, if the ' UnitsOnOrder ' values are null, the result is null.

The Microsoft ' ISNULL () function is used to specify and we want to treat NULL values.

The NVL (), Ifnull (), and coalesce () functions can also to used the achieve.

In the case we want NULL values to be zero.

Below, if "UnitsOnOrder" is null it won't 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 does not have a 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 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 as this:

  code is as follows copy code

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: 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.