列值为null相关知识

39. View the Exhibit and examine the description of the PRODUCT_INFORMATION table.
Which SQL statement would retrieve(检索) from the table the number of products having LIST_PRICE as NULL?(求值为null的行数)
比较null值用is NULL,任何NULL不等于其他NULL,使用count(列名)会忽略列中值为NULL的行,count(*)返回记录数,NULL也算一条记录
A. SELECT COUNT(list_price)
FROM product_information
WHERE list_price IS NULL
解释: COUNT(list_price)返回list_price不为NULL的个数
B. SELECT COUNT(list_price)
FROM product_information
WHERE list_price = NULL
解释: 值为NULL写作is NULL,不可写为= NULL
C. SELECT COUNT(NVL(list_price, 0))(right)
FROM product_information
WHERE list_price IS NULL
解释: nvl(arg,value)代表如果前面的arg的值为null那么返回的值为后面的value
D. SELECT COUNT(DISTINCT list_price)
FROM product_information
WHERE list_price IS NULL

解释: DISTINCT是去除重复的关键字,不管有没有DISTINCT关键字,count()都返回不是null的个数,

以上信息来自：http://hz.togogo.net/BrainJam/wenxian/2013/0507/676.html

看一个例子：

SQL> select EMPLOYEE_ID, MANAGER_ID ,COMMISSION_PCt from hr.employees;

EMPLOYEE_ID MANAGER_ID COMMISSION_PCT
----------- ---------- --------------
        198        124
        199        124
        200        101
        201        100
        202        201
        203        101
        204        101
        205        101
        206        205
        100
        101        100

EMPLOYEE_ID MANAGER_ID COMMISSION_PCT
----------- ---------- --------------
        102        100
        103        102
        104        103
        105        103
        106        103
        107        103
        108        101
        109        108
        110        108
        111        108
        112        108

EMPLOYEE_ID MANAGER_ID COMMISSION_PCT
----------- ---------- --------------
        113        108
        114        100
        115        114
        116        114
        117        114
        118        114
        119        114
        120        100
        121        100
        122        100
        123        100

EMPLOYEE_ID MANAGER_ID COMMISSION_PCT
----------- ---------- --------------
        124        100
        125        120
        126        120
        127        120
        128        120
        129        121
        130        121
        131        121
        132        121
        133        122
        134        122

EMPLOYEE_ID MANAGER_ID COMMISSION_PCT
----------- ---------- --------------
        135        122
        136        122
        137        123
        138        123
        139        123
        140        123
        141        124
        142        124
        143        124
        144        124
        145        100             .4

EMPLOYEE_ID MANAGER_ID COMMISSION_PCT
----------- ---------- --------------
        146        100             .3
        147        100             .3
        148        100             .3
        149        100             .2
        150        145             .3
        151        145            .25
        152        145            .25
        153        145             .2
        154        145             .2
        155        145            .15
        156        146            .35

EMPLOYEE_ID MANAGER_ID COMMISSION_PCT
----------- ---------- --------------
        157        146            .35
        158        146            .35
        159        146             .3
        160        146             .3
        161        146            .25
        162        147            .25
        163        147            .15
        164        147             .1
        165        147             .1
        166        147             .1
        167        147             .1

EMPLOYEE_ID MANAGER_ID COMMISSION_PCT
----------- ---------- --------------
        168        148            .25
        169        148             .2
        170        148             .2
        171        148            .15
        172        148            .15
        173        148             .1
        174        149             .3
        175        149            .25
        176        149             .2
        177        149             .2
        178        149            .15

EMPLOYEE_ID MANAGER_ID COMMISSION_PCT
----------- ---------- --------------
        179        149             .1
        180        120
        181        120
        182        120
        183        120
        184        121
        185        121
        186        121
        187        121
        188        122
        189        122

EMPLOYEE_ID MANAGER_ID COMMISSION_PCT
----------- ---------- --------------
        190        122
        191        122
        192        123
        193        123
        194        123
        195        123
        196        124
        197        124

107 rows selected.

SQL> SELECT COUNT(NVL(COMMISSION_PCT, 0)) FROM hr.employees
  2   where COMMISSION_PCT is null;

COUNT(NVL(COMMISSION_PCT,0))
----------------------------
                          72

SQL> SELECT COUNT(DISTINCT COMMISSION_PCT) FROM hr.employees
  2  where COMMISSION_PCT is null; <span style="color:#ff0000;">//因为每个null都不相等</span>

COUNT(DISTINCTCOMMISSION_PCT)
-----------------------------
                            0

SQL> SELECT COUNT( COMMISSION_PCT) FROM hr.employees
  2  where COMMISSION_PCT is null;

COUNT(COMMISSION_PCT)
---------------------
                    0 //<span style="font-family: 宋体; line-height: 24px; "><span style="color:#ff0000;">使用count(列名)会忽略列中值为NULL的行</span></span>