[SQL] DB 스터디 - 06

Subquery와 JOIN

1. Subquery란

- SQL 명령문 안에 포함된 또 다른 SELECT 명령문

- Main Query보다 먼저 실행될 수 있고, 글 결과를 Subquery가 위치한 곳에서 사용

- Subquery는 해당 Query가 실행 될 때, 단일행인지 여러행인지에 따라 다르게 사용됨

- 직접적으로 조건절에 사용되는 값을 검색해서 일일이 작성하지 않기 위해 사용 됨

- SELECT 절에 나오는 Subquery는 반드시 하나의 컬럼만 반환 시켜야 함

예)

->

-- emp 테이블에서 'JONES'보다 급여가 높은 사원을 검색

-- 기존 방법 : 'JONES'의 sal을 먼저 검색 후 해당 값으로 조건을 주어 전체 테이블 검색

SELECT sal

FROM emp

WHERE ename = 'JONES';

SELECT *

FROM emp

WHERE sal > 2975;

-- Subquery를 사용

SELECT *

FROM emp

WHERE sal > (    SELECT sal

FROM emp

WHERE ename = 'JONES'    );

-- 위와 같이 사용

---

2. Subquery 사용 방법

- 괄호로 묶어서 작성

- 가독성을 위해 비교 조건의 오른쪽에 Subquery를 배치

- 단일 행 Subquery에는 단일 행 연산자를 사용하고(스칼라 Subquery), 여러 행 Subquery에는 여러 행 연산자를 사용

예)

->

-- 단일 행 Subquery

-- 사원들의 급여가 전체 급여의 평균 보다 높은 사원을 출력

SELECT *

FROM emp

WHERE

    sal > ( SELECT AVG(sal)

            FROM emp);

            

-- emp 테이블에서 부서 번호가 'JONES'와 같고

-- 급여가 'JONES'보다 높은 사원 검색

SELECT *

FROM emp

WHERE 

    deptno = (  SELECT deptno

                FROM emp

                WHERE ename = 'JONES')

AND

    sal > ( SELECT sal

            FROM emp

            WHERE ename = 'JONES');

-- 여러 행 Subquery

-- 부서별 최소 급여의 사원 정보 출력

-- 여러 행의 Subquery를 사용하는 시에는 IN을 사용

SELECT *

FROM emp

WHERE

    sal IN ( SELECT MIN(sal)

            FROM emp

            GROUP BY deptno);

-- HAVING절에 사용되는 Subquery

SELECT 

    department_id, MIN(salary)

FROM employees

GROUP BY department_id

HAVING 

MIN(salary) > (  SELECT MIN(salary)

                           FROM employees

                           WHERE department_id = 50 );

---

3. 여러 행 Subquery

- 두 개 이상의 행을 반환

- 여러 행 비교 연산자를 사용

- ANY와 ALL을 사용

- ALL : AND연산에 사용

-> 조건에 만족하는 모두를 만족하는 행을 출력

- ANY : OR 연산에 사용

-> 조건에 하나라도 만족하는 행을 출력

예)

->

-- ANY와 MIN의 활용

-- 하나라도 만족하는 행을 검색

SELECT *

FROM emp

WHERE

    sal > ANY(  SELECT AVG(sal)

                FROM emp

                GROUP BY deptno);

-- 하나라도 만족하는 것은 가장 작은 값보다 큰 모든 행을 검색하는 것과 같음

SELECT *

FROM emp

WHERE

    sal >   (  SELECT MIN(AVG(sal))

                FROM emp

                GROUP BY deptno);

-- ALL과 MAX의 활용

-- 모두 만족 시키는 행을 검색

SELECT *

FROM emp

WHERE

    sal > ALL(  SELECT AVG(sal)

                FROM emp

                GROUP BY deptno);

-- 모두 즉, 가장 큰 값보다 큰 행을 검색하는 것과 같음

SELECT *

FROM emp

WHERE

    sal > (  SELECT MAX(AVG(sal))

                FROM emp

                GROUP BY deptno);

---

4. Subquery에서 사용되는 IN과 NOT IN연산

- IN연산에서는 Subquery에서 나오는 모든 값을 OR 연산 시킴

- NOT IN 연산에서는 AND 연산 시킴

- NOT IN 연산에서 Subquery에서 반환되는 값에 NULL이 있으면 절대 연산 되지 않음

- 즉, Subquery문에서 NULL이 나오지 않도록 조건을 설정 하거나, NVL을 이용해 NULL값을 변경 해야 함

예)

->

-- IN과 NOT IN을 사용하는 Subquery

-- deptno라는 컬럼을 가져 와서 3개 중에 하나라도 만족하는 부서를 검색

SELECT *

FROM dept

WHERE 

    deptno IN ( SELECT deptno

                FROM emp);

---------- 동일함 ----------

SELECT *

FROM dept

WHERE deptno IN (10,20,30);

-- deptno라는 컬럼을 가져와서 사원이 없는 부서 검색

SELECT *

FROM dept

WHERE

    deptno NOT IN (  SELECT deptno

                            FROM emp );

--------------- 동일함 --------------

SELECT *

FROM dept

WHERE deptno NOT IN (10,20,30);

---------------- 한 명이라도 근무하는 부서 정보 ----------------

SELECT *

FROM departments

WHERE department_id 

IN (    SELECT department_id

                        FROM employees  );

---------------- 한 명도 근무하지 않는 부서 정보 ----------------

-- 결과 출력 X

-- Subquery에 있는 NULL 때문에 출력이 되지 않음

-- NOT IN 연산에서 목록에 NULL이 있으면 무조건 결과가 출력되지 않음

SELECT *

FROM departments

WHERE department_id 

NOT IN (    SELECT department_id

                                     FROM employees  );

--------------- 올마른 SQL 명령문 -----------------

SELECT *

FROM departments

WHERE department_id 

NOT IN (    SELECT department_id

                                FROM employees

                                WHERE department_id IS NOT NULL);

            

---

5. 멀티플 컬럼 Subquery

- 무조건 WHERE 연산자는 IN만 사용 가능

- Subquery에서 반환 받은 컬럼의 개수를 비교하는 Main Query에 컬럼 수와 동일 해야 함

예)

->

-- 부서별 최소 급여를 받는 사원의 정보 출력

SELECT *

FROM emp

WHERE 

(deptno, sal) 

IN (     SELECT deptno, MIN(sal)

FROM emp

GROUP BY deptno )

ORDER BY deptno;

-> 그렇다면 부서별 평균 급여보다 높은 급여를 받는 사원의 정보를 검색 하려면 어떻게 해야 하는가?

-- JOIN문을 이용한 SQL 명령문을 이용하면 됨

-- Subquery는 JOIN에서 사용될 수 있음

-- 소속 부서의 평균 급여보다 많은 급여를 받는 사원을 검색

SELECT *

FROM emp E

JOIN

(    SELECT deptno, AVG(sal) AVG_SAL    

-- AVG(sal)은 별칭을 사용해서 조건식을 판별할 수 있도록 함

FROM emp

GROUP BY deptno    ) A    

-- 해당 Subquery를 이용해나온 즉, 새로운 테이블의 명칭을 A라고 별칭

ON 

    E.deptno = A.deptno

AND

    E.sal > A.AVG_SAL;

-- 부서별로 먼저 작업을 해서 JOIN을 해야 함

-- 부서의 평균 급여가 화면에 출력 됨

-- 다른 방법

-- Subquery와 Main Query에서 비교하는 혹은, 그룹핑을 하는 값의 관계를 확인하여 만듬

SELECT *

FROM emp E

WHERE sal > ( SELECT AVG(sal)

                    FROM emp

                    WHERE deptno = E.deptno    );

-- 해당 Subquery는 Main Query보다 먼저 실행 될 수 없음

-- 먼저 실행 되는 경우 E.deptno를 확인할 수 없음

-- 부서의 평균 급여는 화면에 출력되지 않음