有一天,在处理集合的查询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
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
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
<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)
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,望告知。