Oracle 서브상관쿼리, exists, not exists

select *
from BALJEON;
--|| 발전일별로 누적량을 보여라 ||--
select to_char(balday,'yyyy-mm-dd') "발전일", balyang "발전량",
    sum(balyang) over(order by balday) "누적량"
from BALJEON;
--|| 서브상관쿼리로 발전일별 누적량 출력 ||--
select to_char(balday,'yyyy-mm-dd') "발전일",   -- 그룹지어진 컬럼 갯수만큼 출력을 해야한다.
    balyang "발전량",                        -- 컬럼수가 초과되면 오류가 난다.
    (select sum(balyang) from BALJEON T2 where T2.BALDAY <= T1.balday) "누적량"
from BALJEON T1
order by 1;
----// 위쿼리문에서 바깥쪽에 있는부분을 외부쿼리, 안쪽에 있는부분을 내부쿼리라 하고
--     외부쿼리문의 컬럼명이 내부쿼리의 조건절에 사용되어질때 이것을 상관서브쿼리(서브상관쿼리)라 한다.
------------------------------------------------------------------------------------------------------
select *
from OLDSALESINFO;

select *
from SALESINFO;
--|| 제품별로 판매량이 최고이였던 날짜와 총판매량을 나타내시오. ||--
select jpname "제품명", to_char(saleday,'yyyy-mm-dd') "판매일자", sum(su) "판매량"
from
(
select *
from OLDSALESINFO
union
select *
from SALESINFO
) T1
group by jpname, to_char(saleday,'yyyy-mm-dd')
having sum(su) = (
             select max(sum(su))  -- max만 하게되면 제품전체에 대한 max값만 나오기때문에
      from                 -- 하단에 where로 조건을 넣어서 제품별 max값이 나오게 해야한다.
      (
        select *
           from OLDSALESINFO
        union
        select *
        from SALESINFO
      ) T2
      where T2.jpname = T1.jpname -- 제품별로 최대치가 나오게 하기위한 조건부
      -- 외부쿼리에 컬럼이 내부쿼리 조건에 포함
      group by jpname, to_char(saleday,'yyyy-mm-dd')
     );
--// 외부쿼리 컬럼이 내부쿼리 조건절에 포함된 경우도 상관서브쿼리라 할 수 있다.
--------------------------------------------------------------------------------------------
---||| 연습문제 |||---
--문제 13. Emp테이블에서 부서별로 월급이 평균 월급보다 높은사원을 부서번호, 이름, 급여 출력.
select deptno, empno, ename, sal
from EMP E1
where sal > (
        select avg(sal)
    from EMP E2
        where E2.deptno = E1.deptno
       )
order by deptno
--문제 14. Emp테이블에서 업무별로 월급이 평균 월급보다 높은사원을 부서번호, 이름, 급여 출력.
select job, empno, ename, sal
from EMP E1
where sal < (
        select avg(sal)
    from EMP E2
        where E2.job = E1.job
       )
order by job
--★★ 교제 165PAGE EXISTS ★★-- 중요
--// exists : 조건에 만족하는 행이 존재하면 참이고, 존재하지 않으면 거짓
select *
from EMP
where exists(select * from dept where deptno = 10); -- 참인 예제

select *
from EMP
where exists(select * from dept where deptno = 50); -- 거짓 예제

--// not exists : 조건에 만족하는 행이 존재하면 거짓, 존재하지 않으면 참
select *
from EMP
where not exists(select * from dept where deptno = 10); -- 거짓 예제

select *
from EMP
where not exists(select * from dept where deptno = 50); -- 참 예제
---||| 주소지가 DALLAS 와 CHICAGO에 근무하는 사원의 부서번호, 사원번호, 사원명, 급여를 출력.
------// 서브쿼리에 in이 나오면 쿼리 처리 속도를 위해 exists를 써주는게 좋다.
select deptno, empno, ename, sal
from emp
where deptno in (select deptno
        from DEPT
        where loc in('DALLAS','CHICAGO') );
       
select deptno, empno, ename, sal
from emp E
where exists(select deptno
      from DEPT D
      where loc in('DALLAS','CHICAGO')
      and D.deptno = E.deptno);
--|=| 참고사항 : 서브쿼리보다는 join이 처리속도가 더 빠르다.
select D.dname E.empno, E.ename, E.sal
from emp E, dept D
where E.deptno = D.deptno and D.loc in ('DALLAS','CHICAGO');

select D.dname, E.empno, E.ename, E.sal
from
(select empno, ename, sal, deptno from EMP) E,
(select dname, deptno from DEPT where loc in ('DALLAS','CHICAGO')) D
where E.deptno = D.deptno;
-- 위 두개의 쿼리문은 출력값은 같으나 위쪽쿼리는 emp,dept테이블의 column, row의 모든값을
-- 표시하기때문에 아래쿼리문처럼 미리 필요한 column, row만(필요없는 부분 출력안함) 뽑아서
-- 쿼리문을 작성하면 처리 속도가 빨라진다. (대용량 DB에 매우 유용하다.)