標量子查詢

--標量子查詢
select e.empno, e.ename, e.sal, e.deptno,
       (select d.dname from dept d where e.deptno = d.deptno)as dname
  from emp e
--插入一條數據
insert into emp(empno,deptno) values(9999,null)--返回結果15條記錄
--改成left join(hash outer)
select e.empno, e.ename, e.sal, e.deptno,d.dname
  from emp e
  left join dept d
    on (e.deptno = d.deptno)
--NL outer
select /*+ use_nl(e,d) */e.empno, e.ename, e.sal, e.deptno,d.dname
  from emp e
  left join dept d
    on (e.deptno = d.deptno)
/*Note:修改后plan一般有outer字樣，如果沒有，注意是否改錯。*/

--用left join 優化標量子查詢之聚合改寫
select dp.department_id, dp.department_name, dp.location_id,
       nvl((select sum(em.salary)
              from hr.employees em
             where em.department_id = dp.department_id),
            0) as sum_dept_salary
  from hr.departments dp

--錯誤寫法
select dp.department_id, dp.department_name, dp.location_id,
       nvl(sum(em.salary), 0) as sum_sal
  from hr.departments dp
  left join hr.employees em
    on dp.department_id = em.department_id
    
--原標量子查詢改寫為：

select em.department_id, sum(em.salary) as sum_sal
  from hr.employees em
 group by em.department_id
 
 --左聯改寫后的內聯視圖
  select dp.department_id, dp.department_name, dp.location_id,
         nvl(sum(e.sum_sal), 0) as sum_sal
    from hr.departments dp
    left join (select e.department_id, sum(e.salary) as sum_sal
                 from hr.employees e
                group by e.department_id) e
      on (dp.department_id = e.department_id)
   group by dp.department_id, dp.department_name, dp.location_id
--
create table dept2 as select * from scott.dept;
insert into dept2  select * from scott.dept where deptno=10

select t1.job, t1.deptno,
       (select distinct dname from dept2 b where b.deptno = t1.deptno) as dname
  from scott.emp t1
 order by 1, 2, 3
--以下改寫結果變了
select distinct t1.job, b.deptno, b.dname
  from scott.emp t1
  left join dept2 b
    on t1.deptno = b.deptno
--正確改寫
select t1.job, t1.deptno, f.dname
  from scott.emp t1
  left join (select b.deptno, b.dname
               from dept2 b
              group by b.deptno, b.dname) f
    on (f.deptno = t1.deptno)