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 aUnion Select b.student from chinese bUnion 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  studentfrom 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 aintersect Select b.student from chinese bintersect Select c.student from math c

<b>等价连接查询SQL

Select distinct a.studentfrom 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 aintersect Select b.student from chinese bminus Select c.student from math c

<b>等价连接查询SQL

Select distinct a.studentfrom chinese ainner join english b on a.student = b.studentleft join math c on a.student=c.student or b.student=c.studentwhere c.student is null 

(4).A∪B∩C

<a>集合查询SQL

Select a.student from english aunion Select b.student from chinese bintersect Select c.student from math c

<b>等价连接查询SQL

select c.studentfrom chinese afull 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 aunionSelect b.student from chinese bminusSelect c.student from math c

<b>等价连接查询SQL

Select distinct case when a.student is null                      then b.student                      else a.student endfrom 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 aunionSelect b.student from english b)

<b>等价连接查询SQL

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

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

                                                                                                        

<a>集合查询SQL

(Select a.student from chinese aunionSelect b.student from english bintersect Select c.student from math c)union(Select a.student from chinese aintersectSelect 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  studentfrom chinese a full join english b on a.student=b.studentfull join math c on a.student=c.student or b.student =c.studentwhere (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 aunionSelect b.student from math b))union(Select c.student from chinese c minus(Select a.student from math aunionSelect b.student from english b))union(Select c.student from math c minus(Select a.student from chinese aunionSelect 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  studentfrom chinese a full join english b on a.student=b.studentfull join math c on a.student=c.student or b.student =c.studentwhere (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，望告知。