当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXISTS替换
現測試數據如下:

         SQL> WITH A AS (SELECT 'A' DEPTNO,'A1' NAME FROM DUAL
  2             UNION
  3             SELECT 'B' DEPTNO,'B1' NAME FROM DUAL
  4             UNION
  5             SELECT 'C' DEPTNO,'C1' NAME FROM DUAL
  6             UNION
  7             SELECT 'D' DEPTNO,'D1' NAME FROM DUAL
  8             ),
  9       B AS (select 'A' DEPTNO,1 ID FROM DUAL
 10             UNION
 11             select 'A' DEPTNO,2 ID FROM DUAL
 12             UNION
 13             select 'B' DEPTNO,1 ID FROM DUAL
 14             UNION
 15             select 'B' DEPTNO,3 ID FROM DUAL
 16             UNION
 17             select 'C' DEPTNO,4 ID FROM DUAL
 18             UNION
 19             select 'E' DEPTNO,5 ID FROM DUAL
 20             )
 21  select  DISTINCT A.DEPTNO,A.NAME FROM A,B
 22  WHERE A.DEPTNO=B.DEPTNO
 23  ORDER BY A.DEPTNO
 24  /
 
DEPTNO NAME
------ ----
A      A1
B      B1
C      C1

不加DISTINCT結果如下

SQL> WITH A AS (SELECT 'A' DEPTNO,'A1' NAME FROM DUAL
  2             UNION
  3             SELECT 'B' DEPTNO,'B1' NAME FROM DUAL
  4             UNION
  5             SELECT 'C' DEPTNO,'C1' NAME FROM DUAL
  6             UNION
  7             SELECT 'D' DEPTNO,'D1' NAME FROM DUAL
  8             ),
  9       B AS (select 'A' DEPTNO,1 ID FROM DUAL
 10             UNION
 11             select 'A' DEPTNO,2 ID FROM DUAL
 12             UNION
 13             select 'B' DEPTNO,1 ID FROM DUAL
 14             UNION
 15             select 'B' DEPTNO,3 ID FROM DUAL
 16             UNION
 17             select 'C' DEPTNO,4 ID FROM DUAL
 18             UNION
 19             select 'E' DEPTNO,5 ID FROM DUAL
 20             )
 21  select A.DEPTNO,A.NAME FROM A,B
 22  WHERE  A.DEPTNO=B.DEPTNO
 23  /
 
DEPTNO NAME
------ ----
A      A1
A      A1
B      B1
B      B1
C      C1

用EXISTS來替換DISTINCT

 SQL> WITH A AS (SELECT 'A' DEPTNO,'A1' NAME FROM DUAL
  2             UNION
  3             SELECT 'B' DEPTNO,'B1' NAME FROM DUAL
  4             UNION
  5             SELECT 'C' DEPTNO,'C1' NAME FROM DUAL
  6             UNION
  7             SELECT 'D' DEPTNO,'D1' NAME FROM DUAL
  8             ),
  9       B AS (select 'A' DEPTNO,1 ID FROM DUAL
 10             UNION
 11             select 'A' DEPTNO,2 ID FROM DUAL
 12             UNION
 13             select 'B' DEPTNO,1 ID FROM DUAL
 14             UNION
 15             select 'B' DEPTNO,3 ID FROM DUAL
 16             UNION
 17             select 'C' DEPTNO,4 ID FROM DUAL
 18             UNION
 19             select 'E' DEPTNO,5 ID FROM DUAL
 20             )
 21  select A.DEPTNO,A.NAME FROM A
 22  WHERE EXISTS (SELECT 1 FROM B WHERE A.DEPTNO=B.DEPTNO)
 23  /
 
DEPTNO NAME
------ ----
A      A1
B      B1
C      C1

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