当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在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 DISTINCTA.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  WHEREEXISTS (SELECT 1 FROM B WHERE A.DEPTNO=B.DEPTNO)
 23  /
 
DEPTNO NAME
------ ----
A      A1
B      B1
C      C1