[SQL심화] IN, NOT IN, EXISTS, NOT EXISTS 상세 비교 정리

0. 기본 테이블 세팅

select * from phone;

select * from fruit;

---

1. IN

select * 
from phone p 
where p.number in (select f.number from fruit f);

위와 같은 쿼리를 실행하면, 아래와 같은 결과가 나온다.

쿼리의 접근 순서를 살펴보자면,
-> 괄호 안의 fruit 테이블에 먼저 접근한다.
-> fruit 테이블에서 검색된 결과들을 리스트로 뽑는다.
-> phone 테이블에 접근한다.
-> phone 테이블과 뽑아진 리스트의 요소들이 일치하는지 하나씩 비교한다.

---

2. NOT IN

select * 
from phone p 
where p.number not in (select f.number from fruit f);

위와 같은 쿼리를 실행하면, 아래와 같은 결과가 나온다.

우리가 예상했던 결과와는 사뭇 다른 결과가 나왔다. 여기서 not in의 주요 특성을 하나 발견할 수 있다.
위의 쿼리를 다시 풀어 써 보자면, 괄호 안에 들어갈 요소들은 (1, 2, 3, 4, 5, 6, null, null, 7)이 된다.

이 때, null 값과 not in의 비교연산이 실행된다면, 괄호 자체의 조건이 UNKNOWN 값으로 바뀌게 된다. 그래서 비교 연산을 할 수가 없는 상태가 되어버려서, 더 이상 괄호안의 리스트와 연산을 수행하지 않는다. 이러한 이유로 아무것도 출력하지 않는것이다.
우리가 기대하는 결과를 도출해내려면 아래와 같은 쿼리를 작성하면 된다.

select * 
from phone p 
where p.number 
not in (select f.number from fruit f where f.number is not null);

---

3. EXISTS

select * 
from phone p 
where exists (select f.number from fruit f);

위와 같은 쿼리를 실행하면, 아래와 같은 결과가 나온다.

쿼리의 접근 순서를 살펴보자면,
-> 괄호 안의 fruit 테이블에 먼저 접근한다.
-> fruit 테이블의 f.number의 존재 유무를 확인한다. (존재하므로 결과는 참)
-> where 절이 참의 조건이 되었기 때문에, phone의 행들이 출력된다.
※ in은 각각의 요소들을 비교하는 연산이라면, exists는 존재의 유무 만을 파악하는 연산이다.

---

3. NOT EXISTS

select * 
from phone p 
where not exists (select f.number from fruit f where p.number = f.number);

위와 같은 쿼리를 실행하면, 아래와 같은 결과가 나온다.

exists를 생각해서 대입해본다면, 위 쿼리는 p.number=f.number이 조건을 만족하지 않는 쿼리를 출력하라. 라는 의미로 해석된다. 그래서 우리가 원하는 not in에서 null 값이 포함된 행들이 출력된다!!!!