[ORACLE] SUBQUERY/ 단일행 , 다중행, 상호연관 서브쿼리 / exists 연산자

 

※ SUBQUERY 서브쿼리

   - 쿼리안에 또 다른 쿼리가 담겨 있는 것 (main query 안에 sub query)

 

 

▷ subquery 기본형식

  - () 안에 서브쿼리를 넣으면 됨

  - 서브쿼리는 select 절 / from 절 / where 절에 넣을 수 있으며,  위치에 따라 이름이 달라진다.  = > go to day31 !

select * from 테이블  --main query
where 조건 연산자 ( select 컬럼 from 테이블 where 조건)  --subquery

 

 

 

 

※ 서브쿼리의 종류
 

1) 단일행 서브쿼리

  - 서브쿼리를 수행한 결과가 1건만 나오고, 이 결과를 main query로 전달해서 main query를 수행하게 됨

  - 단일행 서브쿼리일 경우 where 절에서 사용되는 연산자 =, !=, >, <, >=, <= 로 묶어야함

  - SUBQUERY 부분은 where 절의 연산자 오른쪽에 위치해야 하며 반드시 괄호로 묶어야 함

 

ex ) 이윤나 학생과 1전공(deptno1)이 // 동일한 학생들 // 의 이름과 1전공 이름 // 을 출력하시오

  - 4 단계로 나눠서 구해보기

 

[1] 이윤나의 1전공 조회

select deptno1 from student
where name = '이윤나';  //101

[2] 조회한 전공과 같은 전공인 학생들 조회

select * from student
where deptno1 = 101;

[3] 서브쿼리 이용

select name, deptno1 from student
where deptno1 =(select deptno1 from student
                    where name = '이윤나');

 = > 이렇게 해서 이윤나 학생과 전공이 같은 학생들의 이름과 전공번호을 얻었다.

       문제에서는 < 학과명 > 도 같이 얻으라고 했는데 학과명(dname)은

       student 와 다른 테이블인 department 테이블에있다. student 와 department 를 join 시켜 학과명을 가져오자 ! ▽

 

[4] join

select s.*, d.dname 학과명
from student s join department d
on s.deptno1 = d.deptno
where deptno1 =(select deptno1 from student
                    where name = '이윤나');

 

짠 ! 모르겠으면 절대로 한번에 데이터를 가져오려고하지말고

이렇게 단계별로 나눠보자

 

 

 

 

ex ) professor 테이블에서 바비와  직급이(position) 같은 교수들의 정보를 조회하되 이름(name)의 내림차순으로 조회하는 뷰를 작성하시오

create or replace view v_professor
as
SELECT * FROM professor
WHERE position=(SELECT position FROM professor WHERE name = '바비')
ORDER BY name desc;

 

---

 

2) 다중컬럼 서브쿼리 (pairwise subquery)

   - 서브쿼리의 결과가 2건 이상 출력되는 경우

   - group by를 사용하고 누군지 알고싶으면 사용하면됨 << (무슨말인지모르겠지만 ㅠ ..)

      ex ) 학년별 최대키를 갖는 학생의 정보 조회, employees에서 직업별 최대급여를 받는 사원 삭제

             group by 학년별로 학생정보 도출. 근데 그 학생들이누군데 ? ==> 다중행서브쿼리사용

   - 서브쿼리의 결과가 여러 건 출력되기 때문에 !! 단일행 연산자를 사용할 수 없음 !!

 

 

▶ 다중 행 서브쿼리 연산자

 

in       : 같은 값을 찾는다 (포함된 값)
< any : 최대값을 반환함

           ex ) sal < any(100,200,300) => 300  -- 셋중에 하나보다만 작으면 됨
> any : 최소값을 반환함

           ex ) sal > any(100,200,300) => 100  -- `` 크면 됨
< all : 최소값을 반환함

           ex )  sal < all(100,200,300) => 100  -- 괄호안의 모두보다 작아야함 = 100보다 작아야함
> all : 최대값을 반환함

           ex ) sal > all(100,200,300) => 300  -- `` 커야함 = 300보다 커야함

 

  == > any - 여러 개 중 아무거나 하나만 조건을 만족해도 된다는 의미
           all - 서브쿼리에서 반환되는 모든 row 값을 만족해야 함을 의미

 

★ 다중행서브쿼리에서는 = 을 사용할 수 없다. 따라서 != 도 사용불가

    대신 ! [ in / not in ] 을 사용하면된당

 

 

 

예제1 ) 직업별 최대급여를 받는 사원을 구하기

select * from employees
where (job_id,salary) in (select JOB_ID,max(salary) from employees
                    		 group by job_id);

 

 

 

 

 

 

▷ 같은문제 단일형 / 다중형 서브쿼리로 풀어서 차이점 한눈에보기

 

문제 ) emp2 테이블을 조회하여 각 부서별 평균 연봉 // 을 구하고 그 중에서 평균 연봉이 가장 적은 부서의 평균 연봉//보다 많이 받는 직원들의 부서명,직원명, 연봉을 출력하시오.

 

문제푸는순서 :

1. 각 부서별 평균연봉 구하기

2. 그 중에서 평균연봉이 가장 적은 부서의 평균연봉구하기

3. 2의 평균연봉보다 많이 받는 직원들의 부서명, 직원명, 연봉구하기

  -- 부서명은 depart2 테이블에 있기때문에 join 해주기

 

▶ 단일행 서브쿼리

select name, deptno, pay, d.dname
from emp2 e join dept2 d
on e.deptno=d.dcode
and pay > (select min(avg(nvl(pay,0)))
                from emp2 group by deptno);

  = > subquery : 그룹별 평균연봉중에서 가장 작은 값 == subquery 결과가 1개

 

▶ 다중행 서브쿼리

select name, deptno, pay, d.dname
from emp2 e join dept2 d
on e.deptno=D.DCODE
and pay >any
    (select avg(nvl(pay,0)) from emp2 group by deptno);

 = > subquery : 그룹별 평균연봉 == subquery 결과가 여러개

 

 

 

---

3) 상호연관 서브쿼리

(=연관성 있는 서브쿼리,  상관관계 서브쿼리)

 

 

- 서브쿼리가 메인쿼리에 독립적이지 않고, 연관관계 즉 조인을 통해 연결되어 있는 쿼리를 말함

- 서브쿼리와 메인쿼리 사이에서 조인이 사용됨
- 메인 쿼리의 컬럼이 서브쿼리의 where조건절에 사용됨

- Main query 값을 sub query에 주고 sub query를 수행한 후,

  그 결과를 다시 main query로 반환해서 수행하는 sub query

 - 자신의 값과 비교할때 사용함

 

▷ 이런문제일때 상호연관 subquery 를 사용해요 ! 

- 직원들 중에서 자신의 직급의 평균 연봉과 같거나 많이 받는 사람들

- 교수들 중에서 자신의 학과의 평균 급여보다 적게 받는 교수들

- 직원들 중에서 자신의 job의 평균 연봉(sal)보다 적거나 같게 받는

=> 이런식으로 비교/비교 할때

 

 

- 제일 복잡하지만 이것도 단계로 나눠서 풀면 쉽다

   ex ) professor 테이블을 조회하여 교수들 중에서 자신의 학과의 평균 급여 // 보다 적게 받는 교수 // 들의

          이름, 학과, 현재 급여를 출력하시오

 

[1]

select avg(nvl(pay,0))
from professor
where deptno =101 ;   // 400

 = >  101 학과에서 가장 작게 받는 교수의 평균급여

 

select * from professor
where deptno = 101 and pay<400;

 = > 101 학과에서 가장적게받는교수 (400) 보다 적게받는 교수 

 

[2]

select * from professor a                -- main
where pay<(select avg(nvl(pay,0)) 
                from professor b        -- sub
                where b.deptno=a.deptno);

 

코드를 해석해보자면 !

(subquery 부터)

pay 의 평균을 구해라 테이블 b에서

근데이제 b의 deptno 가 a 의 deptno 와 같은경우만 평균을 출력해라

이 b 평균을 a pay 와 비교했을때 a pay 가 더 작은 값의 모든 컬럼을 출력해라

 = > 이런식으로 main 에서 계속 값을 넘겨받아 a.deptno 에 들어가는거임

 

---

▶ exists 연산자

 

    - 특정 컬럼값이 존재하는지 여부를 체크
    - 서브쿼리가 반환하는 결과에 메인쿼리에서 추출될 데이터들이 존재하기만 하면 조건을 만족하게 됨
    - 성능면에서는 in보다 exists가 월등히 우수함

    - 한건이라도 존재하면 true, 없으면 false 를 리턴한다

 

 

예제 ) 

두 테이블에 공통으로 존재하는 데이터 : EMPNO 가 7566,7782 인 데이터

JOB 이 MANAGER 이고, 두 테이블에 공통으로 존재(exists)하는 EMPNO 의 데이터들의

EMPNO, ENAME, DEPTNO 를 가져온것 

 

[ SELECT 1 ] : SELECT 절에 컬럼은 불필요하기 때문에 의미없는 1을 기입한것

 

 

▷ case 문에서 exists 사용

=> empno, ename, job, deptno 를 출력하는데,

     emp 테이블에 dept_history 와 같은 empno 가 있으면(exists) dept_history_yn 컬럼에 Y 를 입력하고

    없으면 그냥 그냥 empno, ename, job, deptno 만 출력하라

 

 

 

  ▷ in, exists 비교
  

    1) in - 어떤 값에 포함되는지 여부를 체크
          in은 ()안에 비교할 값이 올 수도 있고, 서브쿼리가 올 수도 있다.

          in은 서브쿼리 결과를 모두 수행
    2) exists - 특정 컬럼값이 존재하는지 여부를 체크
           exists는 오직 서브쿼리만 올 수 있다. 따라서 무조건 join 이 되어야함

          일치하는 결과가 있으면 더 이상 수행하지 않는다 => 성능이 더 좋음

 

 

△ ex ) 부서테이블의 pdept값이 null이 아닌 부서에 속하는 사원 추출

 

-- in 이용

 select * from emp2
 where deptno in (select dcode from dept2
                    where pdept is not null); --subquery

select * from panmae
where p_code in (select p_code from product
                  where del_yn='Y');

--exists이용

select * from emp2 e
where exists (select dcode from dept2 d
                    where d.dcode=e.deptno
                    and pdept is not null);

select * from panmae a
where exists (select 1 from product b
              where a.p_code = b.p_code
              and del_yn='Y');

--join이용

select e.*, d.*
from emp2 e join dept2 d
on e.deptno=d.dcode
and pdept is not null;