SQL查询和优化（九）_有教无类 sql-CSDN博客

本文链接：https://blog.csdn.net/every__day/article/details/77074581

用LEFT JOIN 优化标量子查询
多次访问同一个表时，尽量不用标量子查询

SELECT s.sid,
        s.sname,
        s.shot,
        s.stype,
        (SELECT a.aid FROM a WHERE a.aid =s.aids) aid,
        (SELECT a.aname FROM a WHERE a.aid =s.aids) aname,
        (SELECT a.atime FROM a WHERE a.aid =s.aids) aatime
    FROM s;

可以看到，在标量子查询中对a表访问了三次，而且关联条件一样，直接改为LEFT JOIN的方式

SELECT s.sid,s.sname,s.type,s.shot,a.aid,a.anme,a.aatime
FROM s
LEFT JOIN a ON(a.aid=s.aids);

再看下面的例子，当标量子查询中有聚合时的改写

SELECT d.department_id,
        d.department_name,
        d.location_id,
        nvl((SELECT SUM(e.salary)FROM hr.employees e
        WHERE e.department_id = d.department_id),0) AS sum_sal
FROM hr.departments d;

改写时，要先汇总，后关联

SELECT d.department_id,
        d.department_name,
        d.location_id,
        nvl (e.sum_sal,0) AS sum_sal
    FROM hr.departments d 
    LEFT JOIN (SELECT e.department_id,SUM(e.salary)AS sum_sal 
                FROM hr.employees e
                GROUP BY e.department_id) e
    ON (e.department_id = d.department_id);