fulljoin语句练习

full join语句练习 需求：将表 A,B,C 合并到一个结果集中 表A如图： [html] N D 1 eeee 3 dddd 5 cccc 7 bbbb 9 aaaa 11 dddd 13 eeee 15 wwww 17 qqqq 19 tttttt 表B如图：[html] N E 5 rrrrrr 4 fffff 3 ssssss 2 jjjjjj 1 kkkkkk 7 uuuuuu 表C如图：[html

full join语句练习

需求：将表 A,B,C 合并到一个结果集中

表A如图：

[html] 
N   D  
1   eeee  
3   dddd  
5   cccc  
7   bbbb  
9   aaaa  
11  dddd  
13  eeee  
15  wwww  
17  qqqq  
19  tttttt  
表B如图：
[html] 
N   E  
5   rrrrrr  
4   fffff  
3   ssssss  
2   jjjjjj  
1   kkkkkk  
7   uuuuuu  
表C如图：
[html] 
N   F  
5   oooo  
4   lllll  
3   hhss  
2   ddfj  
1   kdsfkkk  
7   sduuu  
8   ewrtwy  
12  sdgfsd  
22  dfgee  
要得到的结果集如图：

[html] 
N   D   E   F  
1   eeee    kkkkkk  kdsfkkk  
22          dfgee  
11  dddd           
13  eeee           
2       jjjjjj  ddfj  
5   cccc    rrrrrr  oooo  
4       fffff   lllll  
17  qqqq           
8           ewrtwy  
3   dddd    ssssss  hhss  
7   bbbb    uuuuuu  sduuu  
9   aaaa           
15  wwww           
19  tttttt         
12          sdgfsd  

两种思路：
1, full join
语句为：
[html] 
select  nvl(a.n,nvl(b.n,c.n)) as N,  
            a.d,b.e,c.f  
from A full join B on A.N=B.N   
       full join c on b.n=c.n;  

2, 先union all ，再列转行
语句为：
[html] 
select n ,max(case when nn='a' then d end) as d,  
          max(case when nn='b' then d end) as e,  
          max(case when nn='c' then d end) as f  
from (  
  select n,d as d,'a' as nn from a  
  union all  
  select n,e as d,'b' as nn from b  
  union all  
  select n,f as d,'c' as nn from c  
)  
group by n;  

实际的问题是我要将70个左右的窄表连接成一个宽表，full join 几乎都编译不过，为此我对比了下2种情况的执行计划
使用 full join 的语句
[html] 
explain plan for  
select  nvl(a.n,nvl(b.n,c.n)) as N,  
            a.d,b.e,c.f  
from A full join B on A.N=B.N full join c on b.n=c.n;  
select * from table(dbms_xplan.display());  
执行

结果为 [html] Plan hash value: 2877137913 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19 | 1197 | 122 (4)| 00:00:02 | | 1 | VIEW | | 19 | 1197 | 122 (4)| 00:00:02 | | 2 | UNION-ALL | | | | | | |* 3 | HASH JOIN OUTER | | 11 | 1342 | 61 (4)| 00:00:01 | | 4 | VIEW | | 11 | 825 | 57 (2)| 00:00:01 | | 5 | UNION-ALL | | | | | | |* 6 | HASH JOIN OUTER | | 10 | 60 | 29 (4)| 00:00:01 | | 7 | TABLE ACCESS FULL| A | 10 | 30 | 14 (0)| 00:00:01 | | 8 | TABLE ACCESS FULL| B | 5 | 15 | 14 (0)| 00:00:01 | |* 9 | HASH JOIN ANTI | | 1 | 6 | 29 (4)| 00:00:01 | | 10 | TABLE ACCESS FULL| B | 5 | 15 | 14 (0)| 00:00:01 | | 11 | TABLE ACCESS FULL| A | 10 | 30 | 14 (0)| 00:00:01 | | 12 | TABLE ACCESS FULL | C | 9 | 423 | 3 (0)| 00:00:01 | |* 13 | HASH JOIN ANTI | | 8 | 272 | 61 (4)| 00:00:01 | | 14 | TABLE ACCESS FULL | C | 9 | 189 | 3 (0)| 00:00:01 | | 15 | VIEW | | 11 | 143 | 57 (2)| 00:00:01 | | 16 | UNION-ALL | | | | | | |* 17 | HASH JOIN OUTER | | 10 | 60 | 29 (4)| 00:00:01 | | 18 | TABLE ACCESS FULL| A | 10 | 30 | 14 (0)| 00:00:01 | | 19 | TABLE ACCESS FULL| B | 5 | 15 | 14 (0)| 00:00:01 | |* 20 | HASH JOIN ANTI | | 1 | 6 | 29 (4)| 00:00:01 | | 21 | TABLE ACCESS FULL| B | 5 | 15 | 14 (0)| 00:00:01 | | 22 | TABLE ACCESS FULL| A | 10 | 30 | 14 (0)| 00:00:01 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("B"."N"="C"."N"(+)) 6 - access("A"."N"="B"."N"(+)) 9 - access("A"."N"="B"."N") 13 - access("B"."N"="C"."N") 17 - access("A"."N"="B"."N"(+)) 20 - access("A"."N"="B"."N") Note ----- - dynamic sampling used for this statement 使用union all 的语句 [html] explain plan for select n ,max(case when nn='a' then d end) as d, max(case when nn='b' then d end) as e, max(case when nn='c' then d end) as f from ( select n,d as d,'a' as nn from a union all select n,e as d,'b' as nn from b union all select n,f as d,'c' as nn from c ) group by n; select * from table(dbms_xplan.display()); 执行结果为： [html] 1 Plan hash value: 1237158055 2 3 ----------------------------------------------------------------------------- 4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 5 ----------------------------------------------------------------------------- 6 | 0 | SELECT STATEMENT | | 24 | 576 | 32 (4)| 00:00:01 | 7 | 1 | HASH GROUP BY | | 24 | 576 | 32 (4)| 00:00:01 | 8 | 2 | VIEW | | 24 | 576 | 31 (0)| 00:00:01 | 9 | 3 | UNION-ALL | | | | | | 10 | 4 | TABLE ACCESS FULL| A | 10 | 30 | 14 (0)| 00:00:01 | 11 | 5 | TABLE ACCESS FULL| B | 5 | 15 | 14 (0)| 00:00:01 | 12 | 6 | TABLE ACCESS FULL| C | 9 | 189 | 3 (0)| 00:00:01 | 13 ----------------------------------------------------------------------------- 14 15 Note 16 ----- 17 - dynamic sampling used for this statement

对比2种处理方式，union all 的方式明显优于 full join。也可以看出简单的sql语句效率不一定好。