The usage analysis of SQL IsNull function in various databases

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")

ISNULL
Replaces NULL with the specified replacement value.

Grammar
ISNULL (Check_expression, Replacement_value)

Parameters
Check_expression

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

Replacement_value

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.

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

Example
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      go      select    AVG ( ISNULL (price,    $10.00))       from    titles      go

   &NBSP
   Below is the result set:  
   
  ---------- ----------------    
   14.24                                                 
   
   (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      go      select    SUBSTRING ( title,    1,   )     as    title,    type    as    type,               ISNULL (price,    0.00)     as    price      from    titles      go

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

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

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