서브쿼리

• Subquery는 다른 하나의 SQL 문장의 절에 NESTEDED된 SELECT 문장 이다.
• SELECT, UPDATE, DELETE, INSERT와 같은 DML문과 CREATE TABLE 또는 VIEW의 WHERE절이나 HAVING절에서 사용 된다.
• 보통 Subquery는 Main Query 이전에 한 번 실행된다.
• Subquery는 괄호로 묶어야 한다.
• 단일 행 연산자(=, >, >=, <, <=, <>, !=)와 다중 행 연산자(IN, NOT IN, ANY, ALL, EXISTS)들이 서브쿼리에 사용 된다.
• Subquery는 연산자의 오른쪽에 위치해야 한다.

 

🌱 서브쿼리의 유형

• 단일 행(Sing-Row) 서브쿼리 : SELECT 문장으로 부터 오직 하나의 행 만을 검색하는 질의이다.
• 다중 행(Multiple-Row) 서브쿼리 : SELECT 문장으로부터 하나 이상의 행을 검색하는 질의이다.
• 다중 열(Multiple-Column) 서브쿼리 : SELECT 문장으로부터 하나 이상의 컬럼을 검색하는 질의이다.
• FROM절상의 서브쿼리(INLINE VIEW) : FROM절상에 오는 서브쿼리로 VIEW처럼 작용 한다.
• 상관관계 서브 쿼리 : 바깥쪽 쿼리의 컬럼 중의 하나가 안쪽 서브쿼리의 조건에 이용되는 처리 방식 이다.

---

Single-Row Subquery

• SELECT 문장에서 오직 하나의 행(값)만 반환하는 Query이다
• 단일 행 연산자(=,>, >=, <, <=, <>, !=) 만 사용된다.

💊 empno가 7369의 job을 조회 한 후, job이 'CLERK'인 사원의 이름과 직업을 반환

SELECT ename,job
FROM emp
WHERE job = (SELECT job
             FROM emp
             WHERE empno = 7369);

 

---

Multiple-Row Subquery

• 하나 이상의 행을 반환하는 Subquery이다
• 단일 행 연산자를 사용하지 못하며, 다중 행 연산자(IN, NOT IN, ANY, ALL, EXISTS)만 사용이 가능하다.

 

🌱 IN 연산자

• IN 연산자는 하나의 컬럼이 여러개의 '=' 조건을 가지는 경우에 사용
• OR는 IN을 포함한다. IN을 사용해 표현할 수 있는 것은 당연히 OR로 표현할 수 있다.
• 하지만 OR로 표한한 것은 IN으로 표현하지 못할때가 있다. (OR에서 LIKE같은 연산자를 사용한 경우)
• IN은 반드시 하나의 컬럼이 비교되어야 하므로 나중에 인덱스 구성에 대한 전략을 수립할 때 유리하다.
• 그러므로 가능한 OR보다는 IN 을 사용하는 것이 좋다.

💊 부서별로 가장 급여를 많이 받는 사원의 정보를 출력하는 예제

SELECT empno,ename,sal,deptno 
FROM emp
WHERE sal IN (SELECT MAX(sal)
              FROM emp
              GROUP BY deptno);

EMPNO ENAME  SAL    DEPTNO
----- ------ ------ -------
 7698  BLAKE   2850      30
 7788  SCOTT   3000      20
 7902  FORD    3000      20

---

 

🌱 ANY 연산자

ANY 연산자는 Subquery의 여러 결과값 중 어느 하나의 값만 만족이 되면 행을 반환 한다.

💊 ALESMAN 직업의 급여보다 많이 받는 사원의 사원명과 급여 정보를 출력하는 예제

SELECT ename, sal
FROM emp
WHERE deptno != 20
AND sal > ANY (SELECT sal
               FROM emp
               WHERE job='SALESMAN');

ENAME      SAL
---------- ----------
     ALLEN       1600
     BLAKE       2850
     CLARK       2450
...

 

---

🌱 ALL 연산자

ALL 연산자는 Subquery의 여러 결과값 중 모든 결과 값을 만족해야 행을 반환 한다.

💊 모든 SALESMAN직업의 급여보다 많이받는 사원의 사원명과 급여정보를 출력하는예제

SELECT ename, sal
FROM emp
WHERE deptno != 20
AND sal > ALL (SELECT sal
               FROM emp
               WHERE job='SALESMAN');

ENAME      SAL
---------- --------
     CLARK     2450
     BLAKE     2850
     KING      5000

---

🌱 EXISTS 연산자

• EXISTS 연산자는 Subquery 데이터가 존재하는가를 체크해 존재 여부(TRUE,FALSE)를 결과로 반환한다.
• EXISTS절에는 반드시 메인 쿼리와 연결이 되는 조인 조건을 가지고 있어야 한다.
• subquery에서 결과 행을 찾으면, inner query 수행을 중단하고 TRUE를 반환한다.

-- 아래 예처럼 emp 테이블을 통해 사원들이 속한 부서번호의 정보만 조회하는 경우
-- 추출하고자 하는 대상은 dept 테이블이지만 emp 테이블과 조인하여 부서번호를
-- 체크해야 한다.
-- 두 테이블의 관계가 1 : M 이므로 불필요하게 EMP 테이블을 모두 액세스하고
-- DISTINCT로 중복 제거를 한다.
SELECT DISTINCT d.deptno, d.dname
FROM dept d, emp e
WHERE d.deptno = e.deptno;

-- EXISTS를 사용하는 Subquery로 변경
-- 추출하고자 하는 대상만을 FROM절에 놓고 emp테이블은 체크만 하기위해
-- EXISTS절에 위치시켰으며 이로 인해 수행속도가 대폭 감소하게 된다.
SELECT d.deptno, d.dname
FROM dept d
WHERE EXISTS
      (SELECT 1
       FROM emp e
       WHERE e.deptno = d.deptno);

 

---

Multiple-Column Subquery

결과 값이 두 개 이상의 컬럼을 반환하는 Subquery이다

🌱 Pairwise(쌍비교) Subquery

Subquery가 한 번 실행되면서 두 개 이상의 컬럼을 검색해서 주 쿼리로 넘겨 준다.

SELECT empno, sal, deptno
FROM emp
WHERE (sal, deptno) IN (SELECT sal, deptno
                        FROM emp
                        WHERE deptno = 30
                        AND comm is NOT NULL);

     EMPNO        SAL     DEPTNO
---------- ---------- ----------
      7521       1250         30
      7654       1250         30
      7844       1500         30
      7499       1600         30

 

---

🌱 Nonpairwise(비쌍비교) Subquery

WHERE 절에서 두 개 이상의 서로 다른 Subquery가 사용되어서 결과 값을 주 쿼리로 넘겨 준다.

SELECT empno, sal, deptno
FROM emp
WHERE sal IN (SELECT sal
              FROM emp
              WHERE deptno = 30
              AND comm is NOT NULL);
AND deptno IN (SELECT deptno
               FROM emp
               WHERE deptno = 30
               AND comm is NOT NULL);

     EMPNO        SAL     DEPTNO
---------- ---------- ----------
      7521       1250         30
      7654       1250         30
      7844       1500         30
      7499       1600         30

 

---

🌱 Null Values in a Subquery

서브쿼리에서 NULL 값이 반환 되면 주 쿼리 에서는 어떠한 행도 반환되지 않는다.

---

Inline View (From절 Subquery)

• FROM절에 오는 Subquery이다.
• FROM절에서 원하는 데이터를 조회하여 가상의 집합을 만들어 조인을 수행하거나 가상의 집합을 다시 조회 할 때 사용한다.
• Inlivew View 안에 또 다른 Inline View가 올 수 있다.

💊 부서번호 20의 평균 급여보다 크고, 부서번호 20에 속하지 않은 관리자를 조회하는 예제

SELECT b.empno, b.ename, b.job, b.sal, b.deptno
FROM (SELECT empno
      FROM emp 
      WHERE sal >(SELECT AVG(sal)
                  FROM emp
                  WHERE deptno = 20)) a, emp b
 WHERE a.empno = b.empno
   AND b.mgr is NOT NULL
   AND b.deptno != 20;

 EMPNO ENAME      JOB         SAL     DEPTNO
 ----- --------- --------- ------- ---------
  7698 BLAKE      MANAGER     2850        30
  7782 CLARK      MANAGER     2450        10

---

 

Scalar Subquery

SELECT 절에서 사용하는 Subquery 이다.

특징

• 한 개의 로우만 반환 한다.
• 메인 쿼리에서 추출되는 데이터 건 수 만큼 수행되기 때문에 조인으로 수행 될 때보다 수행회수가 적을 수 있다.
• 일치하는 값이 없는 경우 NULL을 반환 한다.
• 코드성 테이블에서 코드값을 조회 할 때, 불필요한 조인을 하지 않기 위해 많이 사용한다.

💊 직업이 ‘MANAGER’인 사원의 사원명, 부서명을 조회하는 예제

SELECT ename,
       (SELECT dname FROM dept d WHERE d.deptno = e.deptno) deptno
FROM emp e
WHERE job ='MANAGER';

ENAME      DEPTNO
---------- --------------
JONES      RESEARCH
BLAKE      SALES
CLARK      ACCOUNTING

 

💊 NULL값을 반환하는 Outer Join형태의 Scalar Subquery 예

-- Scalar Subquery는 일치하는 값이 없으면 NULL을 반환하므로 Outer Join과 같은 형태이다.
-- 아래는 부서별 최대 급여정보 조회 예이다.
-- 부서 40에 해당하는 직원이 없기 때문에 최대 급여가 조회 되지 않는다.
SELECT d.deptno, d.dname,
       (SELECT MAX(sal)
          FROM emp
         WHERE deptno = d.deptno) sal
FROM dept d;

-- 결과가 동일한 Outer Join 예이다.
SELECT d.deptno, d.dname, MAX(e.sal)
FROM dept d, emp e
WHERE d.deptno = e.deptno(+)
GROUP BY d.deptno, d.dname
ORDER bY d.deptno;

DEPTNO DNAME          SAL
------ -----------  -----
    10 ACCOUNTING    5000
    20 RESEARCH      3000
    30 SALES         2850
    40 OPERATIONS