ORACLE 用EXISTS替換DISTINCT

             当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXISTS替换
k*D"r,[9QW~'|"fc%F0現測試數據如下:

         SQL> WITH A AS (SELECT 'A' DEPTNO,'A1' NAME FROM DUAL
}7PLP#x(RI2^Tct,Z"F0  2             UNION
,RZy%p6o#Wb0  3             SELECT 'B' DEPTNO,'B1' NAME FROM DUAL
[y6lPi@ M7@0  4             UNIONITPUB个人空间?BKJ:B`EE:u,T#[
  5             SELECT 'C' DEPTNO,'C1' NAME FROM DUALITPUB个人空间 g"Mzve!Nt#b
  6             UNION
SEmc+N;G(s0  7             SELECT 'D' DEPTNO,'D1' NAME FROM DUALITPUB个人空间0z+Wq2p\
  8             ),ITPUB个人空间-n3D@U@D
  9       B AS (select 'A' DEPTNO,1 ID FROM DUALITPUB个人空间-c0|*t{;MH%r G
 10             UNIONITPUB个人空间j9ms(vQ2aem V
 11             select 'A' DEPTNO,2 ID FROM DUALITPUB个人空间%?:kJ3]HV.F
 12             UNIONITPUB个人空间m[YK%GMhF
 13             select 'B' DEPTNO,1 ID FROM DUAL
#[Zr:ZDzx0 14             UNION
|+e Ga$eQI;y0 15             select 'B' DEPTNO,3 ID FROM DUAL
.UKj#u]7a;A-R?N0 16             UNION

m,P$R|8} {A E5[0 17             select 'C' DEPTNO,4 ID FROM DUALITPUB个人空间AW1j(E \0p:LJQD
 18             UNIONITPUB个人空间1e?*C:t)SX
 19             select 'E' DEPTNO,5 ID FROM DUALITPUB个人空间hT-| ry;q{iGG
 20             )
%d#H1?(U7f@c0 21  select DISTINCTA.DEPTNO,A.NAME FROM A,B
m*J'n\&R z0 22  WHERE A.DEPTNO=B.DEPTNO
+reJvSb
[[/F(pA0 23  ORDER BY A.DEPTNO
&n'F T I},d:Z9C0 24  /ITPUB个人空间;uV7E0u;q:]
 ITPUB个人空间,tYCn?Y0d
DEPTNO NAMEITPUB个人空间)ST:{Lg]I{
------ ----
K+c8OC4O'W&^1X0A      A1
7q,p!B8H#z.tY0B      B1
2v%cee W qs1W;l8}u0C      C1

不加DISTINCT結果如下

SQL> WITH A AS (SELECT 'A' DEPTNO,'A1' NAME FROM DUALITPUB个人空间`v"ZQ h&c
  2             UNIONITPUB个人空间t:p*u&Opud;K1n
  3             SELECT 'B' DEPTNO,'B1' NAME FROM DUAL
3j0g.FJ%i\a0  4             UNION
8{)JeTRt0  5             SELECT 'C' DEPTNO,'C1' NAME FROM DUALITPUB个人空间*uE:{)UT5hD
  6             UNION
l1V)X*\ o%rrw(}0  7             SELECT 'D' DEPTNO,'D1' NAME FROM DUALITPUB个人空间%U/XY_Z
  8             ),ITPUB个人空间t*B9}#^"? mQ
  9       B AS (select 'A' DEPTNO,1 ID FROM DUAL
"N?5~wr"|#L8i$F0 10             UNIONITPUB个人空间OHf'BaKa
 11             select 'A' DEPTNO,2 ID FROM DUAL
B5\B%u6Y]NM0 12             UNIONITPUB个人空间:v QsHv"u0QQ n#Dl7n
 13             select 'B' DEPTNO,1 ID FROM DUALITPUB个人空间~C8av Y
 14             UNIONITPUB个人空间 uaa b m$]`\
 15             select 'B' DEPTNO,3 ID FROM DUALITPUB个人空间ZFla)J Ye
 16             UNIONITPUB个人空间g.R8m;K-z:ljf(~
 17             select 'C' DEPTNO,4 ID FROM DUAL
o4eT |k9@B c0 18             UNIONITPUB个人空间 M_-pj8BdIi/e
 19             select 'E' DEPTNO,5 ID FROM DUAL
:\{~ll)neG6Nn0 20             )ITPUB个人空间)m)e'j e2G(U kY)vGmg
 21  select A.DEPTNO,A.NAME FROM A,BITPUB个人空间 Z[H5y*N4FH
 22  WHERE  A.DEPTNO=B.DEPTNOITPUB个人空间X6`7\T"y
 23  /
-X rB!~Mp5x0 ITPUB个人空间Xa~S9B0X:t(c
DEPTNO NAME
:l#sZO f|:T0------ ----ITPUB个人空间bK-z:y9CL)A
A      A1ITPUB个人空间%o!zUs%_*p|`h7e
A      A1
P'w0l+^.v'TE%g3X1p c0B      B1ITPUB个人空间 Uo
Wz9Q,j
y
B      B1

v A5nu~K0C      C1

用EXISTS來替換DISTINCT

 SQL> WITH A AS (SELECT 'A' DEPTNO,'A1' NAME FROM DUAL
^ iun!W0  2             UNION

Q:d3d0C\X0  3             SELECT 'B' DEPTNO,'B1' NAME FROM DUALITPUB个人空间UYM*}ar^'i;l
  4             UNIONITPUB个人空间k zMA1Q g-]@
  5             SELECT 'C' DEPTNO,'C1' NAME FROM DUALITPUB个人空间$k M"Xk+_
i:q
  6             UNION
4Wxd6B.mX}0  7             SELECT 'D' DEPTNO,'D1' NAME FROM DUAL
o_XI5wv0  8             ),
#i(Xs,t SA.XW(^0  9       B AS (select 'A' DEPTNO,1 ID FROM DUALITPUB个人空间&d-h)\ X-z;vOe9m'l'B
 10             UNIONITPUB个人空间+o1Y!O-d M3Q
b
 11             select 'A' DEPTNO,2 ID FROM DUAL
usE|2j.u0 12             UNION
BCcOJ(y"L(XU:E0 13             select 'B' DEPTNO,1 ID FROM DUAL
2V"U i&F NSc0 14             UNION
;R(D UO~~F1Tg#V0 15             select 'B' DEPTNO,3 ID FROM DUALITPUB个人空间2P f'[$[k)CbfQA
 16             UNIONITPUB个人空间d7]#C9x-^ae
 17             select 'C' DEPTNO,4 ID FROM DUALITPUB个人空间.a MRS\gp
 18             UNION

jE\6v `|0@gEe0 19             select 'E' DEPTNO,5 ID FROM DUALITPUB个人空间(?;_iLk;MZoF
 20             )
FTV$saqTyI0 21  select A.DEPTNO,A.NAME FROM AITPUB个人空间3I7CJ6yeD
 22  WHEREEXISTS (SELECT 1 FROM B WHERE A.DEPTNO=B.DEPTNO)
E.D&~SU@J0 23  /
t-mX8})dicf9H7Or0 
{#hBF)E0DEPTNO NAME
$E!Ul Jzz:|[w0------ ----
'b]"ir7l$Uif#TW0A      A1
ec/q)qx0B      B1
@7K3r3z*`#p0C      C1

結論如下:用EXISTS的時候,可以自動去掉重復的紀錄,需要申明一點的是一定要是一對一(或者一對多)的表信息才可以,如果是兩表的紀錄是多對一的關系的話就有問題!