[실무에서 바로 쓰는 SQL 기본과 SQL 튜닝][ORACLE] 13강

NOT IN

관리자인 사원들의 이름을 출력하시오

  SELECT ENAME,SAL,JOB
  FROM EMP
  WHERE EMPNO IN (SELECT MGR
                  FROM EMP);

예제) 관리자가 아닌 사원들의 이름을 출력하시오

  SELECT ENAME
  FROM EMP
  WHERE EMPNO NOT IN (SELECT MGR
                  FROM EMP);

위 쿼리에서 결과가 안 나온다.. MGR 칼럼에 NULL값이 포함되어 있기 때문

NOT IN = '!=ALL'

TRUE AND TREU AND ... AND NULL = NULL

칼럼 값 중에 NULL이 있으면 전체 결과 값이 NULL이 되버림 (NULL은 TRUE/FALSE도 아닌 알 수 없는 값)

따라서 아래와 같이 서브쿼리의 결과 값에서 NULL값이 포함되지 않도록 WHERE절을 작성하여야 함

  SELECT ENAME
  FROM EMP
  WHERE EMPNO NOT IN (SELECT MGR
                  FROM EMP
                  WHERE MGR IS NOT NULL);

  SELECT ENAME
  FROM EMP
  WHERE EMPNO NOT IN (SELECT NVL(MGR,-1)
                  FROM EMP);

서브 쿼리에서 NOT IN을 쓸 때에는 NULL값이 반환값으로 들어가지 않도록 해야 함

EXISTS/ NOT EXIST

보통은 서브쿼리부터 실행되는데, EXIST문을 작성하면 메인쿼리부터 실행됨

  SELECT *
  FROM DEPT D
  WHERE EXISTS (SELECT *
                  FROM EMP E
                  WHERE E.DEPTNO = D.DEPTNO);

먼저 메인쿼리를 실행하여, DEPTNO의 전체 테이블 정보를 가져온 후, 서브 쿼리의 DEPT.DEPTNO 조건문에 값을 대입하면서 실행된다.

존재하지 않는 컬럼만 출력하려면, NOT EXIST

  SELECT *
  FROM DEPT D
  WHERE NOT EXISTS (SELECT *
                  FROM EMP E
                  WHERE E.DEPTNO = D.DEPTNO);

HAVING절의 서브 쿼리

  SELECT JOB, SUM(SAL)
  FROM EMP
  GROUP BY JOB
  HAVING SUM(SAL) > (SELECT SUM(SAL)  -- 그룹함수에 대한 조건은 HAVING절에 작성
                      FROM EMP
                      WHERE JOB='SALESMAN');

예제) 부서번호, 부서번호별 인원수를 출력하는데 10번 부서번호의 인원수보다 더 큰 것만 출력하시오

  SELECT DEPTNO, COUNT(*) 
  FROM EMP 
  GROUP BY DEPTNO
  HAVING COUNT(*) > (SELECT COUNT(*)
                      FROM EMP
                      WHERE DEPTNO = 10);

FROM절의 서브쿼리

분석함수는 WHERE절에는 작성 불가능

  SELECT *
  FROM (SELECT ENAME, SAL, RANK() OVER (ORDER BY SAL DESC) RNK
          FROM EMP
        );

위와 같이 실행하면 FROM절 안의 서브쿼리가 가장 먼저 실행됨 - 서브쿼리의 결과를 메모리에 올려놓음

실행 순서 : (서브쿼리 내부) FROM -> (서브쿼리 내부) SELECT -> (메인쿼리) FROM -> (메인쿼리) WHERE -> (메인쿼리) SELECT

  SELECT *
  FROM (SELECT ENAME, SAL, RANK() OVER (ORDER BY SAL DESC) RNK
          FROM EMP
        )
  WHERE RNK = 1;

예제) 직업이 SALESMAN인 사원들 중에서 가장 먼저 입사한 사원들의 이름과 입사일을 출력하시오

  SELECT *
  FROM (SELECT ENAME, HIREDATE, RANK() OVER (ORDER BY HIREDATE DESC) RANK
       FROM EMP
       WHERE JOB = 'SALESMAN')
  WHERE RANK = 1;

SELECT 절의 서브 쿼리

그룹함수의 결과를 일반 칼럼과 같이 출력하고 싶을 때 사용

  SELECT ENAME, SAL (SELECT MAX(SAL) FROM EMP WHERE JOB='SALESMAN') AS MAX1,
                      (SELECT MIN(SAL) FROM EMP WHERE JOB='SALESMAN') AS MIN1
  FROM EMP
  WHERE JOB='SALESMAN';

SELECT 문의 6가지 절 (서브쿼리 사용 유무)

SELECT (O)
FROM (O)
WHERE (O)
GROUP BY (X)
HAVING (O)
ORDER BY (O)

예제) 부서번호가 20번인 사원들의 이름과 월급을 출력하고, 그 옆에 20번 부서번호인 사원들의 평균 월급이 출력되게 하시오

  SELECT ENAME, SAL ,(SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 20) 평균
  FROM EMP
  WHERE DEPTNO = 20;