Oracle returns NULL after join

对employees表和department表进行分组后,再连接;与连接分组,再连接返回的结果不同.

要求:查询每个部门有多少人,包含没有部门的人,同时列出部门ID,部门name,最后的结果如下

DEPARTMENT_ID DEPARTMENT_NAME                    COUNTS
------------- ------------------------------ ----------
          100 Finance                                 6
           30 Purchasing                              6
                                                      1
           20 Marketing                               2
           70 Public Relations                        1
           90 Executive                               3
          110 Accounting                              2
           50 Shipping                               45
           40 Human Resources                         1
           80 Sales                                  34
           60 IT                                      5

 

测试1

(1)建立实体子表,拆开测试,为正常返回值

select count(*) counts,department_id from employees group by department_id;

    COUNTS DEPARTMENT_ID

---------- -------------

         6           100

         6            30

         1

         2            20

         1            70

         3            90

         2           110

        45            50

         1            40

        34            80

         5            60

11 rows selected.

create table D2 as select * from (select count(*) counts,department_id from employees group by department_id);

Table created.

17-MAR-12 HR @ hdb>desc D2;

 Name                                     Null?    Type

 -------------------------------------------------------------------------

 COUNTS                                            NUMBER

 DEPARTMENT_ID                                     NUMBER(4)

select D2.department_id,D.department_name,D2.counts from D2 left join departments D on D2.department_id = D.department_id;

DEPARTMENT_ID DEPARTMENT_NAME                    COUNTS

------------- ------------------------------ ----------

          100 Finance                                 6

           30 Purchasing                              6

                                                      1

           20 Marketing                               2

           70 Public Relations                        1

           90 Executive                               3

          110 Accounting                              2

           50 Shipping                               45

           40 Human Resources                         1

           80 Sales                                  34

           60 IT                                      5

11 rows selected.

(2),D2表为虚拟表时,含有NULL的行丢失.

select D2.department_id,D.department_name,D2.counts from (select count(*) counts,
department_id
from employees
group by department_id) D2left join departments D
on D2.department_id = D.department_id;

DEPARTMENT_ID DEPARTMENT_NAME                    COUNTS

------------- ------------------------------ ----------
           80 Sales                                  34
           50 Shipping                               45
           20 Marketing                               2
           90 Executive                               3
           70 Public Relations                        1
           60 IT                                      5
          110 Accounting                              2
           30 Purchasing                              6
           40 Human Resources                         1
          100 Finance                                 6

10 rows selected.

 

测试2

当在统计发生在连接的时候,再进行连接,结果正常.

select t1.department_id,
d1.department_name,
t1.counts
from (select e.department_id,count(*) counts from employees e left join departments d on e.department_id = d.department_id group by e.department_id) t1
left join departments d1
on t1.department_id = d1.department_id;

DEPARTMENT_ID DEPARTMENT_NAME                    COUNTS

------------- ------------------------------ ----------
          100 Finance                                 6
           30 Purchasing                              6
                                                      1
           20 Marketing                               2
           70 Public Relations                        1
           90 Executive                               3
          110 Accounting                              2
           50 Shipping                               45
           40 Human Resources                         1
           80 Sales                                  34
           60 IT                                      5

11 rows selected.

其中的子查询t1如下

select e.department_id,count(e.employee_id) countsfrom employees eleft join departments d on e.department_id = d.department_id group by e.department_id;

DEPARTMENT_ID          COUNTS                

---------------------- ----------------------

100                    6                     

30                     6                     

                       1                     

20                     2                     

70                     1                     

90                     3                     

110                    2                     

50                     45                    

40                     1                     

80                     34                    

60                     5                     

11 rows selected

 

此时下面的查询和t1的结果一样,但无法和departments连接成功,请问这个是为什么呢?

select e.department_id,count(e.employee_id) countsfrom employees egroup by e.department_id;

DEPARTMENT_ID          COUNTS                

---------------------- ----------------------

100                    6                     

30                     6                     

                       1                     

20                     2                     

70                     1                     

90                     3                     

110                    2                     

50                     45                    

40                     1                     

80                     34                    

60                     5                     

11 rows selected