[Oracle] 서브 쿼리(SUB QUERY)

●이번엔 서브쿼리에 대해서 알아 볼 것이다.

---

•  Sub Query
    하나의 쿼리 문에 두 개 이상의 SELECT ~ 문을 사용할 때 메인 SELECT ~ 문 이외의 나머지 SELECT ~ 문을 Sub Query 문이라고 한다. 첫 번째 SELECT ~ 문이 Main Query 문이고, 두 번째 이하의 SELECT ~ 문이 Sub Query로써 Main SELECT ~ 문 안에 Sub SELECT ~ 문이 들어가는 것으로 이해하면 된다.
  
    서브 쿼리는 결과가 하나의 행으로 나오는 단일 행 서브쿼리, 결과가 여러 행으로 나오며 IN(하나라도 일치하면 true), ANY/SOME(결과가 1개 이상이면 true), ALL(모든 결과가 일치하면 true), EXISTS(서브 쿼리의 결과가 있으면 ture)를 함께 사용하는 다중 행 서브쿼리, 서브 쿼리의 select 문에 비교할 여러 대상을 넣는 다중 열 서브쿼리가 있다. 이 외에도 인라인 뷰와 with-절, 스칼라 서브 쿼리 등이 있다.
  
  서브 쿼리는 
•  연산자를 사용한 비교나 조회 대상의 우측에 놓이며 서브 쿼리문을 ()로 묶는다. 
•  특수한 몇몇 경우를 제외하고 대부분 서브 쿼리 절에는 order by 절을 사용할 수 없다. c) 서브 쿼리의 select 절에 명시된 열은 메인 쿼리의 비교 대상과 같은 자료형과 같은 개수로 지정한다. 
•  서브 쿼리에 있는 select 문의 수행결과로 나오는 행의 수는 메인 쿼리의 연산자 종류와 호환되어야 한다는 등의 특징이 있다.  
  
  ●select e.empno, e.name, e.deptno, d.dname, d.area
  from (select * from emp2 where deptno=1000)e,
       (select * from dept2) d
  where e.deptno = d.deptno;
  ===> 
  ●select e.empno, e.name, e.deptno, d.dname, d.area
  from (select * from emp2 where deptno=1000) e 
        JOIN (select * from dept2) d
        ON e.deptno = d.deptno;

---

●예제

 SUB QUERY
●select last_name , salary from employees
where salary > (select avg(salary) from employees);

select last_name , salary from employees
where salary > (select avg(salary) from employees);

●select e.empno, e.name, e.pay, d.deptno, d.dname
from emp2 e, dept2 d
where e.deptno = d.deptno
and e.deptno = 10
and e.pay > (select avg(pay) from emp2); <=ORACLE JOIN
==>
from emp2 e JOIN dept2 d
ON e.deptno = d.deptno
where e.deptno =10
and e.pay > (select avg(pay) from emp2); <=ANSI

select e.empno, e.name, e.pay, d.deptno, d.dname
from emp2 e, dept2 d
where e.deptno = d.deptno
and e.deptno = 10
and e.pay > (select avg(pay) from emp2);

●select * from emp2
where pay in (select max(pay) from emp2 group by deptno);

select * from emp2
where pay in (select max(pay) from emp2 group by deptno);

●select e.empno, e.name, e.deptno, d.dname, d.area
from (select * from emp2 where deptno = 1000)e,
     (select * from dept2) d
where e.deptno = d.deptno;
<비교>
●select e.empno, e.name, e.deptno, d.dname, d.area
 from (select * from emp2 where deptno=1000) e
JOIN (select * from dept2) d
ON e.deptno = d.deptno;

select e.empno, e.name, e.deptno, d.dname, d.area
from (select * from emp2 where deptno = 1000)e,
     (select * from dept2) d
where e.deptno = d.deptno;
<비교>
select e.empno, e.name, e.deptno, d.dname, d.area
 from (select * from emp2 where deptno=1000) e
JOIN (select * from dept2) d
ON e.deptno = d.deptno;

●employees 테이블에서 job_id 별로 최대 월급자를 서브쿼리로 구하시오
●select job_id, max(salary) from employees
group by job_id;*/
select employee_id, last_name, job_id, salary from employees
where salary = (select max(salary) from employees group by job_id);

●select employee_id, last_name, job_id, salary from employees
where salary IN (select max(salary) from employees group by job_id);
최대 월급받는 사람이 하나 이상이 있을수 있어서 쿼리를 신용할수 없다.

●select employee_id, last_name, job_id, salary from employees
where (job_id, salary) IN (select job_id, max(salary) from employees group by job_id);

select job_id, max(salary) from employees
group by job_id;*/
select employee_id, last_name, job_id, salary from employees
where salary = (select max(salary) from employees group by job_id);

select employee_id, last_name, job_id, salary from employees
where salary IN (select max(salary) from employees group by job_id);
최대 월급받는 사람이 하나 이상이 있을수 있어서 쿼리를 신용할수 없다.

select employee_id, last_name, job_id, salary from employees
where (job_id, salary) IN (select job_id, max(salary) from employees group by job_id);