欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

full join语句练习

程序员文章站 2022-05-14 20:02:51
...

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语句效率不一定好。