NOT IN, EXISTS/NOT EXISTS

NOT IN

• 특정 쿼리에서 검색한 데이터 중 다른 쿼리에 없는 데이터를 검색할 때
  (예) 관리자가 아닌 사원들의 이름과 월급, 직업을 출력
  • 관리자가 아닌 사원 == 직속 부하 직원이 한 명도 없는 사원
  • 즉 사원번호(EMPNO)가 관리자 번호(MGR)와 같지 않은 사원을 의미

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

※ 단일행 서브 쿼리의 연산자를 사용하면 에러가 발생함
ORA-01427: single-row subquery returns more than one row
→ 다중 행 서브 쿼리 중 NOT IN을 사용하여 문제 해결

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

• 그러나 NOT IN을 사용하여 쿼리를 작성해도 MGR에 NULL 값이 포함되어 있어 선택된 레코드가 없음
  • NOT IN을 사용할 경우 서브 쿼리에서 메인 쿼리로 NULL 값이 하나라도 리턴되면 결과가 출력되지 않기 때문
  • NOT IN으로 작성한 서브쿼리문은 다음의 SQL과 같음

SELECT ENAME, SAL, JOB
FROM EMP
WHERE EMPNO != 7839 AND EMPNO != 7698 AND EMPNO != 7902 AND EMPNO != 7566 
	  AND EMPNO != 7566 AND EMPNO != 7788 AND EMPNO != 7782 AND EMPNO != NULL;

→ 위 SQL의 결과는 NULL(전체가 NULL이 되어 결과가 출력되지 않음)
→ 따라서 서브 쿼리문에서 NOT IN을 사용할 때는 반드시 서브 쿼리문에서 메인 쿼리문으로 NULL 값이 리턴되지 않게 해야 함

ENAME	SAL	JOB
TURNER	1500	SALESMANN
WARD	1250	SALESMANN
MARTIN	1250	SALESMANN
ALLEN	1600	SALESMANN
MILLER	1300	CLERK
SMITH	800	CLERK
ADAMS	1100	CLERK
JAMES	950	CLERK

EXISTS / NOT EXISTS

• 특정 테이블의 데이터가 다른 테이블에도 존재하는지 아닌지를 확인하기 위해 EXISTS / NOT EXISTS를 WHERE절 바로 다음에 사용

EXISTS

• A 테이블의 데이터가 B테이블에도 존재하는지 확인
  (예) 부서 테이블에 있는 부서 번호 중에서 사원 테이블에도 존재하는 부서 번호의 부서번호, 부서명, 부서 위치를 출력
  • 부서 테이블(DEPT)에 있는 부서 번호(DEPTNO)가 사원 테이블(EMP)에도 존재하는지 D.DEPTNO = E.DEPTNO 조건을 통해 검색

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

NOT EXISTS

• EXISTS와 반대로 A 테이블의 데이터가 B 테이블에 존재하지 않는지 확인

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

차이점

• IN() , EXISTS, INNER JOIN은 상호 변환이 가능하면서도 성능에 차이가 있음
  참고
  • 보통 INNER JOIN, IN(), EXISTS 순으로 수행 시간이 짧은데 항상 그런 건 아니고 경우에 따라 EXISTS가 INNER JOIN보다 빠를 때가 있음
    • 1:n의 관계에서 1에 있는 정보만 출력을 할 경우는 EXISTS가 빠름