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가 빠름

비교 대상에 NULL이 포함된 경우

문제

정답 쿼리

틀린 쿼리

• 두 쿼리의 차이점은 not in 연산자의 비교 대상에 오는 서브쿼리 안에서의 where p_id is not null 유무
  • 즉, not in 연산자의 비교 대상에 null이 존재하면 의도치 않은 결과가 나옴
    • 이유를 알려면 4가지 개념을 알아야 함

1. NULL

• 정확하게 알려면 메모리 공간에 대한 이해가 필요
• 쉽게 정의하면 NULL은 NULL임
  • 0도 아니고 ''(SP)도 아니고 FALSE도 아님
  • 알 수 없는 값, 정의할 수 없는 상태, 아무 것도 없는 값
• NULL값과 연산/비교를 하면 어떻게 될까?
  
  • 상수와의 연산(+, -, *, /)이나 비교(＝, ≠) → 모두 NULL
  • boolean과의 비교 → NULL
• NULL에 대한 연산은 따로 정해진 연산자 is를 사용해야 원하는 결과값을 얻을 수 있음
  
  • null is null의 결과는 1(true) null is not null의 결과는 0(false)
  • null에 비교 연산자(＝, ≠)를 사용하면 그 결과는 무조건 null이 출력됨

cf. UNKNOWN

• SQL의 처리 방식은 TRUE/FALSE/UNKNOWN 3가지
  • 연산의 결과 NULL이 나오는 경우를 UNKNOWN으로 처리
• UNKNOWN이라는 처리 방식이 FALSE와 다름을 아는 것이 not in 연산자 작동 방식을 이해하는 데 출발점이 됨

2. IN, NOT IN

• not in의 비교대상에 null이 포함되면 어떤 문제가 생기길래 의도한 결과를 내지 못하는 것일까?

1) IN, NOT IN의 작동 방식

• in은 한마디로 "fanct version of ="
  • in은 or로 연결될 다수의 비교 조건문을 묶어줄 뿐, 그 역할은 =와 동일
• 아래 두 쿼리문은 동치 관계

select '줄력됩니다'
where 1 in (1, 3, 5);

select '동치니까 당연히 출력됩니다'
where (1=1) or (1=3) or (1=5);

• 반대로 NOT IN 연산자는 ≠(같지 않다)와 동일

2) WHERE절의 성질

• WHERE절은 그 결과가 TRUE인 값만 조회 조건에 포함

예시: test_1 테이블

num	name
1	A
2	B
3	C
4	D

위 테이블로부터 num NOT IN (1, 3, null) 조건을 만족하는 row 출력
→ 예상한 결과로는 num = 2, 4인 행이 출력되지 않을까 싶지만 결과는 그렇지 않음

select *
from test_1
where num not in (1, 3, null);

num	name

→ 예상과는 달리 아무것도 출력되지 않았음

• 아무것도 출력되지 않은 이유는 num not in (1, 3, null) 조건을 풀어서 써 보면 알 수 있음

  • 먼저 num = 1, 3은 (num!=1) OR (num!=3)에서 FALSE 판정을 받고 조회 조건에서 제외
  • num = 2, 4는 (num!=1) OR (num!=3)에서는 걸리지 않지만 (num!=null)에서 "UNKNOWN(NULL)" 판정을 받고 조회 조건에서 제외
    • NULL과의 비교 결과는 NULL
  • WHERE절은 어떤 이유에서든 그 결과가 TRUE인 값만 조회 조건에 포함시키기 때문에 FALSE, UNKNOWN은 제외될 것

• 결론적으로 NOT IN의 비교대상에 NULL이 포함되면 모두 조회 조건에서 제외되어 아무것도 출력되지 않음
  

  • not in 연산의 결과 num=1,3은 false(0), num=2,4는 false(0) unknown(null)이 출력됨을 확인할 수 있음
    • false, unknown 모두 where절에서 제외되므로 결론적으로는 출력되는 게 없는 것

• num=2,4인 행이 출력되게 하려면:
  

3. EXIST, NOT EXIST

• in과 매우 비슷하지만 외부 춰리를 통해 레코드를 가져와 내부 쿼리에 값이 "존재하는지" 확인 (외부쿼리 > 내부쿼리)
  
• 결과의 존재 여부만을 평가하며, 설사 그 대상이 null이더라도 그 null이 존재/비존재한다면 where절의 true 조건을 충족시킬 수 있음

not exist의 작동 방식

4. NOT IN, NOT EXISTS의 차이

• NULL 처리 방식
  
  • null로 인해 비교가 불가능한 경우 not in 연산자에서는 모두 unknown(null) 처리가 되지만 not exists에서는 null 또한 존재/비존재 여부만 따져 true/false의 결과를 냄
• 동작 방식
  • in 연산자
    • 내부 쿼리에 먼저 접근하여 내부 쿼리의 데이터를 외부 쿼리에 공급(내 → 외)
  • exists 연산자
    • 외부 쿼리에 먼저 접근하여 내부 쿼리로 데이터를 공급(외 → 내)
• 성능
  • in 연산자
    • exists보다 직관적
    • 적은 용량의 데이터에 유리
  • exists 연산자
    • 지연 평가 원리를 이용하므로 in에 비해 속도나 성능면에서 좋음
    • 대용량 데이터에 유리

결론

1. NULL
   • null is null
   • sql은 null과의 연산을 unknown으로 처리
2. IN, EXISTS
   • null이 섞여도 결과적으로 큰 문제 없음
3. NOT IN
   • ≠와 동일
   • 비교 대상에 null이 포함되면 결과는 아무것도 나오지 않음
   • 따라서 not in을 쓸 때에는:
     • 서브쿼리 안에 where 필드명 is not null을 쓰거나
     • 서브쿼리 뒤애 is not false를 쓰기
4. NOT EXISTS
   • 결과적으로 외부 쿼리에 존재하는 null까지 조회 결과에 포함
   • 참고로 서브 쿼리 안의 null은 아무 상관 없음