[Oracle] - 서브쿼리

*서브쿼리

- 서브쿼리란?

: SQL 내부에서 사용하는 SELECT문 

 

<예시>

SELECT ENAME, SAL
  FROM EMP
 WHERE SAL > (SELECT SAL 
                FROM EMP
               WHERE ENAME = 'JONES')

 

 

---

 

 

- 서브쿼리 특징

• 연산자와 같은 비교/조회 대상의 오른쪽에 놓이며 괄호()로 묶어 사용
• 특수 경우 제외하고 서브쿼리에서 ORDER BY절 사용 불가능
• 서브쿼리의 SELECT절에 명시한 열은 메인쿼리의 비교 대상과 같은 자료형, 같은 개수로 지정
• 서브쿼리의 결과 행 수는 메인쿼리의 연산자 종류와 호환 가능해야 함( 단일행 연산자 경우 서브쿼리의 출력 결과가 단일행이어야 함)

 

---

 

 

*단일행 서브쿼리 - 실행 결과 하나

: 실행 결과가 단 하나의 행으로 나오는 서브쿼리

 

- 날짜형 데이터

- 서브쿼리의 결과 값이 날짜형인 경우

SELECT *
  FROM EMP
 WHERE HIREDATE < (SELECT HIREDATE
                     FROM EMP
                    WHERE ENAME = 'SCOTT');

=> EMP테이블에서 SCOTT보다 빨리 입사한 사원 목록을 조회

 

- 함수

- 서브쿼리 안에서 함수를 사용하는 경우

SELECT E.EMPNO, E.ENAME, E.JOB, E.SAL, D.DEPTNO, D.DNAME, D.LOC
  FROM EMP E, DEPT D
 WHERE E.DEPTNO = D.DEPTNO
   AND E.DEPTNO = 20
   AND E.SAL > (SELECT AVG(SAL)
                  FROM EMP);

=> 20번 부서에 속한 사원 중 전체 사원의 평균 급여보다 높은 급여를 받는 사원 정보와 소속 부서 정보 조회

 

 

---

 

*다중행 서브쿼리 - 실행 결과 여러개

: 실행 결과가 여러 개의 행으로 나오는 서브쿼리

 

 

- IN 연산자

: 서브쿼리의 결과와 일치하는 값을 가진 행 출력

• 서브쿼리의 결과 값 중 일치하는 값을 가진 행만 출력
• = ANY/SOME과 같은 기능을 수행

 

<예시>

SELECT *
  FROM EMP
 WHERE SAL IN (SELECT MAX(SAL)
                 FROM EMP
                GROUP BY DEPTNO);

 

 

 

---

 

 

- ANY, SOME 연산자

: 서브쿼리의 여러 결과 값에 메인 쿼리의 조건식을 대입

• 서브쿼리 결과 값 중 하나만 조건식에 맞아떨어지면 메인조건 참
• < ANY 연산자는 서브쿼리에서 최댓값을 구하는 것과 동일한 기능을 함

SELECT *
  FROM EMP
 WHERE SAL < ANY (SELECT SAL
                    FROM EMP
                   WHERE DEPTNO = 30)
 ORDER BY SAL, EMPNO;

SELECT *
  FROM EMP
 WHERE SAL < (SELECT MAX(SAL)
                    FROM EMP
                   WHERE DEPTNO = 30)
 ORDER BY SAL, EMPNO;

 

 

 

---

 

 

- ALL 연산자

: 서브쿼리의 모든 결과가 조건식에 맞아떨어져야만 메인쿼리의 조건식이 참

 

<예시>

SELECT *
  FROM EMP
 WHERE SAL < ALL (SELECT SAL
                    FROM EMP
                   WHERE DEPTNO = 30);

=> 서브쿼리의 결과 값 중 가장 작은 값이 기준

SELECT *
  FROM EMP
 WHERE SAL > ALL (SELECT SAL
                    FROM EMP
                   WHERE DEPTNO = 30);

=> 서브쿼리의 결과 값 중 가장 큰 값이 기준

 

 

---

 

 

- EXISTS 연산자

: 서브쿼리에 결과 값이 하나 이상 존재하면 조건식이 모두 참, 존재하지 않으면 모두 거짓이 됨

 

*상호 연관 서브쿼리를 활용하여 함께 실무에서 많이 쓰인다.

-- 고객 중 주문을 한번이라도 한 고객 조회 EXISTS
-- 고객 중 주문을 한번도 하지 않은 고객 조회 NOT EXISTS
-- 제품 중 한번 이상 주문된 제품정보 조회
-- 제품 중 한번도 안된 제품 정보 조회 

 

<예시>

- 결과 값 존재 O => 출력 가능

SELECT *
  FROM EMP
 WHERE EXISTS (SELECT DANAME
                 FROM DEPT 
                WHERE DEPTNO = 10);

 

- 결과 값 존재x  => 출력 불가능

SELECT *
  FROM EMP
 WHERE EXISTS (SELECT DANAME
                 FROM DEPT 
                WHERE DEPTNO = 50);

 

- 상호 연관 서브쿼리 활용

-- 부서테이블에서 사원을 한명이라도 가지고 있는 부서 출력
SELECT D.DNAME AS 부서명, D.DEPTNO AS 부서번호
  FROM DEPT D
 WHERE EXISTS (SELECT DEPTNO FROM EMP WHERE DEPTNO = D.DEPTNO);
 
 -- 부서 중 직원이 한명도 없는 부서 출력
SELECT D.DNAME AS 부서명, D.DEPTNO AS 부서번호
  FROM DEPT D
 WHERE NOT EXISTS (SELECT DEPTNO FROM EMP WHERE DEPTNO = D.DEPTNO);

 

✔️상호 연관 서브쿼리(correlated subquery)

: 메인쿼리에 사용한 데이터를 서브쿼리에서 사용하고 서브쿼리의 결과 값을 다시 메인쿼리로 돌려주는 방식

*다만 성능을 떨어뜨리는 원인이 될 수 있다.

*인덱스를 사용하기 때문에 속도는 매우 빠르다

 

<예시>

- 각 부서의 최대급여를 받는 사원의 부서코드, 이름, 급여 출력(상관 서브쿼리 사용)

SELECT E1.DEPTNO, E1.ENAME, E1.SAL
  FROM EMP E1
 WHERE E1.SAL = (SELECT MAX(SAL) --만약 E2.DEPTNO = 20이면 3000
                   FROM EMP E2
                  WHERE E2.DEPTNO = E1.DEPTNO);

 

- 각 부서의 최소급여를 받는 사원을 제외한 전체 사원 정보 출력

SELECT *
  FROM EMP E1
 WHERE SAL > (SELECT MIN(SAL)
                FROM EMP E2
               WHERE E1.DEPTNO = E2.DEPTNO)
 ORDER BY DEPTNO, SAL;

 

---

 

* 다중열 서브쿼리 - 비교 열 여러개

: 서브쿼리의 SELECT절에 비교할 데이터를 여러 개 지정하는 방식

• 메인쿼리에 비교할 열을 괄호로 묶어 명시
• 서브쿼리에는 SELECT절에 괄호로 묶은 데이터와 같은 자료형의 데이터 명시

<예시>

SELECT * 
  FROM EMP
 WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, MAX(SAL)
                           FROM EMP
                          GROUP BY DEPTNO);

 

 

---

 

 

* FROM절 서브쿼리 - WITH절

: 인라인 뷰(inline view)라고도 부르며 SELECT문으로 일부 테이터를 먼저 추출해 온 후 별칭을 주어 사용한다.

=> WITH절은 대시보드 만들 때 주로 많이 쓴다.

 

• FROM절에서 쓰는 서브쿼리

<예시>

DELECT E10.EMPNO, E10.ENAME, E10.DEPTNO D.DNAME, D.LOC
  FROM (SELECT * FROM EMP WHERE DEPTNO = 10) E10,
        (SELECT * FROM DEPT) D
 WHERE E10.DEPTNO = D.DEPTNO;

 

• WITH절 사용

WITH 
E10 AS (SELECT * FROM EMP WHERE DEPTNO = 10),
D   AS (SELECT * FROM DEPT)
SELECT E10.EMPNO, E10.ENAME, E10.DEPTNO, D.DNAME, D.LOC
  FROM E10, D
 WHERE X10.DEPTNO = D.DEPTNO;

 

 

SELECT E.ENAME, EMPS1.EMP_CNT, M.ENAME AS MGR_NAME, EMPS2.EMP_CNT MGR_CNT 
  FROM EMP E
     , (SELECT DEPTNO, COUNT(*) AS EMP_CNT FROM EMP GROUP BY DEPTNO) EMPS1
     , EMP M
     , (SELECT DEPTNO, COUNT(*) AS EMP_CNT FROM EMP GROUP BY DEPTNO) EMPS2
 WHERE E.DEPTNO = EMPS1.DEPTNO
   AND E.MGR = M.EMPNO
   AND M.DEPTNO = EMPS2.DEPTNO;
   
WITH EMP_CNT AS (
    SELECT DEPTNO, COUNT(*) AS DEPT_CNT FROM EMP GROUP BY DEPTNO
    )
SELECT E.ENAME, EMPS1.DEPT_CNT, M.ENAME AS MGR_NAME, EMPS2.DEPT_CNT MGR_CNT 
  FROM EMP E
     , EMP_CNT EMPS1
     , EMP M
     , EMP_CNT EMPS2
 WHERE E.DEPTNO = EMPS1.DEPTNO
   AND E.MGR = M.EMPNO
   AND M.DEPTNO = EMPS2.DEPTNO;

 

---

*SELECT절 서브쿼리

: 스칼라 서브쿼리(scalar subquery)라고 부르며 SELECT절에 하나의 열 영역으로서 결과를 출력

 

<예시>

SELECT EMPNO, ENAME, JOAB, SAL,
       (SELECT GRADE
          FROM SALGRADE
         WHERE E.SAL BETWEEN LOSAL AND HISAL) SGRADE,
       DEPTNO,
       (SELECT DNAME
          FROM DEP
         WHERE E.DEPTNO = DEPT.DEPTNO) AS DNAME
  FROM EMP E;

: