SQL查询和优化（十三）

标量子查询与改写逻辑的一致性

当标量子查询中有聚合函数时，很多人在改写时，很喜欢把聚合函数写在主查询里，大概是认为这样简单。
SELECT * 
    FROM (SELECT * FORM t1 WHERE col4_1 = '0') a
    LEFT JOIN (SELECT col3_1,
                        col3_2 coll, 
                        col2,
                        SELECT DISTINCT col_1
                            FROM t2
                            WHERE col2_2 = a.col2
                              AND col2_3 <= to_date('2012-09-03','YYYY-MM-DD')
                              AND col2_4 >= to_date('2012-09-03','YYYY-MM-DD'))
                FROM t3 a 
                WHERE col2_3 <= to_date('2012-09-03','YYYY-MM-DD')
                  AND col2_4 >= to_date('2012-09-03','YYYY-MM-DD'))b
                ON a.col4 = b.col3_1;
网友在改写这个查询时，直接把DISTINCT 放在了主查询SELECT关键字后面

SELECT * 
    FROM (SELECT * FORM t1 WHERE col4_1 = '0') a
    LEFT JOIN (SELECT DISTINCT a.col3_1,
                      a.col3_2 coll, 
                      a.col2,
                      b.col2_1 AS col3
                FROM t3 a
                JOIN (SELECT * 
                      FROM t2 b
                      WHERE b.col2_3 <= to_date('2012-09-03','YYYY-MM-DD')
                        AND b.col2_4 >= to_date('2012-09-03','YYYY-MM-DD')) b
                    ON a.col2 =b.col2_2
                    AND a.col2_3 <= to_date('2012-09-03','YYYY-MM-DD')
                    AND a.col2_4 >= to_date('2012-09-03','YYYY-MM-DD')) b
        ON a.col4 = b.col3_1;

笔者不赞成这么改写，因为改写之前有很多重复数据 ，改写之后就没有了，可以看下面的等价例子

原查询可以表示为：
SELECT a.job,
       a.dept,
       (SELECT DISTINCT dname FROM dept2 b WHERE b.deptno = a.deptno) AS dename,
 FROM emp a 
 ORDER BY 1,2,3;

按上面的方法更改：
SELECT DISTINCT a.job,
       a.deptno,
       b.dname
   FROM emp a  
   LEFT JOIN dept2 b ON b.deptno = a.deptno;
除非确认主查询没有重复数据，否则不要这样改写，正确的改写如下：
SELECT a.job,
       a.deptno,
       b.dname
   FROM emp a  
   LEFT JOIN (SELECT dname,deptno FROM dept2 GROUP BY dname,deptno) b 
     ON b.deptno = a.deptno;