IN은 제공자 EXISTS는 확인자 역할을 한다.

IN은 먼저 서브쿼리를 분석해와서 메인쿼리에게 비교하기 위한 값들을 제공하고

EXISTS는 단지 존재하는지만 확인해주는 역할을 한다.

IN과 EXISTS의 가장 큰 차이는 찾으려는 값에 NULL이 있을 때다.

IN은 다음과 같이 동작한다. subquery1의 반환값이 'A', 'B', 'C'라고 가정

'A' IN (subquery1)

=> 'A' IN ('A', 'B', 'C')

=> 'A' = 'A' OR 'A' = 'B' OR 'A' = 'C'

=> true OR false OR false

=> true

따라서 NOT IN은

'A' NOT IN (subquery1)

=> 'A' NOT IN ('A', 'B', 'C')

=> 'A' <> 'A' AND 'A' <> 'B' AND 'A' <> 'C'

=> false AND true AND true

=> false

이번에는 subquery2의 반환값에 NULL이 있다고 가정

'A' IN (subquery2)

=> 'A' IN ('A', 'B', NULL)

=> 'A' = 'A' OR 'A' = 'B' OR 'A' = NULL

=> true OR false OR ?

=> ? // 결과적으로는 false와 같아진다.

그렇다면 NOT IN은

'A' NOT IN (subquery2)

=> 'A' NOT IN ('A', 'B', NULL)

=> 'A' <> 'A' AND 'A' <> 'B' AND 'A' <> NULL

=> false AND true AND ?

=> ? // NULL이 포함된 결과와 IN, NOT IN을 한다면 둘다 ? 이므로 false나 마찬가지.

즉, false는 아니지만... NULL이 있을 때는 IN 과 NOT IN의 결과가 같다.

중요한 점은. NULL값이 있으면 서브쿼리가지고 뭔가를 하려고 했던 메인쿼리에서 아무것도 할 수가 없다.

다음은 EXISTS. subquery3에서 조건에 의해 찾은 값이 있다고 가정 (찾은 값이 NULL이더라도)

subquery3 AS (SELECT NULL FROM table WHERE cmp) // 조건 cmp에 의해 찾아도 결과는 항상 NULL이다. 그러나 찾았다는게 중요

EXISTS (subquery3)

=> EXISTS (찾았다!)

=> true

따라서 NOT EXISTS는

NOT EXISTS (subquery3)

=> NOT EXISTS (찾았다!)

=> false

즉, 조건만 맞으면 결과가 NULL이든 뭐든 true or false로 나눠진다.

추가적으로

IN을 쓸 때는 서브쿼리 범위가 작아야 유리하다. 서브쿼리 전체를 뒤지기 때문(?)

EXIEST를 쓸 때는 메인쿼리 범위가 작아야 유리. 서브쿼리는 있는지만 확인 (찾을 때까지만 돌고 있으면 끝남)

IN을 사용할 때는 NULL값이 있는지 확인하고 처리 해줘야 한다.

[SQL] NULL 관련 함수 참고

[출처] [SQL] IN과 EXISTS (NOT IN과 NOT EXISTS)|작성자 Ehne