ORACLE中Union,Intersect和Minus的等价连接查询SQL写法

        有一天，在处理集合的查询SQL的时候，突发奇想，其实集合的操作是可以用连接的SQL来表示的。并根据这个假设，希望能比较一下两种SQL的查询效率。这一篇主要是用两种不同的查询SQL来同时计算不同集合运算的结果。根据测试，两种查询SQL是等价的。用来测试的表，分别是语文，英语和数学，表里面存放的是上该门课的学生，表结构如下：

create table CHINESE
(
  TID     NUMBER not null,
  STUDENT VARCHAR2(50)
)

create table ENGLISH
(
  TID     NUMBER not null,
  STUDENT VARCHAR2(50)
)

create table MATH
(
  TID     NUMBER not null,
  STUDENT VARCHAR2(50)
)

以下分别是不同的集合操作

(1).A∪B∪C

<a>集合查询SQL

Select a.student from english a
Union 
Select b.student from chinese b
Union 
Select c.student from math c

<b>等价连接查询SQL

Select distinct case when a.student is null then 
                     case when b.student is null then 
                               c.student
                          else b.student end
                     else a.student end  student
from english  a 
full join chinese b on a.student =b.student 
full join math c on a.student=c.student and a.student=b.student

(2).A∩B∩C

<a>集合查询SQL

Select a.student from english a
intersect 
Select b.student from chinese b
intersect 
Select c.student from math c

<b>等价连接查询SQL

Select distinct a.student
from english  a 
inner join chinese b on a.student =b.student 
inner join math c    on a.student=c.student and a.student=b.student

(3).A∩B-C

<a>集合查询SQL

Select a.student from english a
intersect 
Select b.student from chinese b
minus 
Select c.student from math c

<b>等价连接查询SQL

Select distinct a.student
from chinese a
inner join english b on a.student = b.student
left join math c on a.student=c.student or b.student=c.student
where c.student is null 

(4).A∪B∩C

<a>集合查询SQL

Select a.student from english a
union 
Select b.student from chinese b
intersect 
Select c.student from math c

<b>等价连接查询SQL

select c.student
from chinese a
full join english b on a.student=b.student 
inner join math c on (a.student=c.student or b.student=c.student)

(5).A∪B-C

<a>集合查询SQL

Select a.student from english a
union
Select b.student from chinese b
minus
Select c.student from math c

<b>等价连接查询SQL

Select distinct case when a.student is null 
                     then b.student 
                     else a.student end
from chinese a 
full join english b on a.student = b.student 
left join math c on (c.student=a.student or c.student=b.student )
where c.student is null

(6).C-(A∪B)

<a>集合查询SQL

Select c.student from math c 
minus
(Select a.student from chinese a
union
Select b.student from english b)

<b>等价连接查询SQL

Select distinct c.student 
from chinese a
full join english b on a.student=b.student 
right join math c on a.student=c.student or b.student=c.student
where a.student is null and b.student is null

(7).(A∪B∩C)-(A∩B)

                                                                                                        

<a>集合查询SQL

(Select a.student from chinese a
union
Select b.student from english b
intersect 
Select c.student from math c)
union
(Select a.student from chinese a
intersect
Select b.student from english b)

<b>等价连接查询SQL

Select distinct case when a.student is null then 
                     case when b.student is null then 
                               c.student
                          else b.student end
                     else a.student end  student
from chinese a 
full join english b on a.student=b.student
full join math c on a.student=c.student or b.student =c.student
where (a.student is not null and b.student is not null ) or
 (a.student is not null and c.student is not null )or
 (b.student is not null and c.student is not null )

(8).(C-(A∪B))∪(C-(A∪B))∪(C-(A∪B))

<a>集合查询SQL

(Select c.student from english c 
minus
(Select a.student from chinese a
union
Select b.student from math b))
union
(Select c.student from chinese c 
minus
(Select a.student from math a
union
Select b.student from english b))
union
(Select c.student from math c 
minus
(Select a.student from chinese a
union
Select b.student from english b))

<b>等价连接查询SQL

Select distinct case when a.student is null then 
                     case when b.student is null then 
                               c.student
                          else b.student end
                     else a.student end  student
from chinese a 
full join english b on a.student=b.student
full join math c on a.student=c.student or b.student =c.student
where (a.student is null and b.student is null )or (a.student is null and c.student is null )or(b.student is null and c.student is null )

          对于第七和第八两种情况，如果大牛有更高效的SQL，望告知。