本文章介紹了現在主流的數據庫中使用is null函數是一些區別和事項,有需要的朋友可以簡單的參考一下.

isnull在數據庫查詢中的應用,特別是再語句連接的時候需要用到,比如連接時候,某個字段沒有值但是又要左連接到其他表上 就會顯示空,isnull可以判斷是否是NULL,如果是給個默認值:

isnull("字段名"，"默認的數據")

ISNULL,使用指定的替換值替換NULL。   

語法:ISNULL(check_expression,replacement_value).

參數:check_expression   

將被檢查是否為 NULL的表達式,check_expression 可以是任何類型的.

replacement_value

在 check_expression 為 NULL時將返回的表達式,replacement_value 必須與    check_expresssion 具有相同的類型.

返回類型:返回與 check_expression 相同的類型.

注釋:如果 check_expression 不為 NULL,那么返回該表達式的值,否則返回   replacement_value.

示例:A.將 ISNULL 與 AVG 一起使用,下面的示例查找所有書的平均價格,用值    $10.00 替換 titles 表的 price 列中的所有 NULL 條目,代碼如下:

1. USE    pubs    
2.  GO    
3.  SELECT    AVG(ISNULL(price,    $10.00))    
4.  FROM    titles    
5.  GO    
6.  
7. 下面是結果集：    
8.    
9.  --------------------------      
10.  14.24                                                
11.    
12.  (1    row(s)    affected)    

B.使用 ISNULL,下面的示例為 titles 表中的所有書選擇書名、類型及價格,如果一個書名的價格是 NULL,那么在結果集中顯示的價格為 0.00,代碼如下:

1. USE    pubs    
2.    GO    
3.    SELECT    SUBSTRING(title,    1,    15)    AS    Title,    type    AS    Type,      
4.          ISNULL(price,    0.00)    AS    Price    
5.    FROM    titles    
6.    GO    
7.  
8.  
9.      
10.  --下面是結果集：    
11.  --Vevb.com 
12.    
13.    Title                        Type                    Price                       
14.    ---------------    ------------    --------------------------      
15.    The    Busy    Execut    business            19.99                                               
16.    Cooking    with    Co    business            11.95                                               
17.    You    Can    Combat      business            2.99                                                 
18.    Straight    Talk    A    business            19.99                                               
19.    Silicon    Valley      mod_cook            19.99                                               
20.    The    Gourmet    Mic    mod_cook            2.99                                                  
21.    The    Psychology      UNDECIDED          0.00                                                 
22.    But    Is    It    User      popular_comp    22.95                                               
23.    Secrets    of    Sili    popular_comp    20.00                                               
24.    Net    Etiquette        popular_comp    0.00                                                 
25.    Computer    Phobic    psychology        21.59                                                
26.    Is    Anger    the    En    psychology        10.95                                                
27.    Life    Without    Fe    psychology        7.00                                                  
28.    Prolonged    Data      psychology        19.99                                               
29.    Emotional    Secur    psychology        7.99                                                 
30.    Onions,    Leeks,      trad_cook          20.95                                                
31.    Fifty    Years    in      trad_cook          11.95                                               
32.    Sushi,    Anyone?      trad_cook          14.99                                                
33.      
34.    (18    row(s)    affected)    

國外一些說明:

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,代碼如下:

1. SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0)) 
2. FROM Products 
3. Oracle 

Oracle does not have an ISNULL() function. However, we can use the NVL() function to achieve the same result,代碼如下:

1. SELECT ProductName,UnitPrice*(UnitsInStock+NVL(UnitsOnOrder,0)) 
2. FROM Products 
3. 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:代碼如下:

1. SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0)) 
2. FROM Products