Not In과 Not Exists의 차이점
In은 조건에 만족하는 row를 찾는 것이고 Exists는 Exists이하 절이 true인지 아닌지를 체크합니다.
Not In과 Not Exists의 차이점은 연결고리가 되는 컬럼의 값 중 Null값을 처리하는 부분에서 차이가 발생합니다. Not In 은 Where 절의 조건이 만족하더라도 연결고리 컬럼이 Null값을 갖는 경우 결과에서 제외 됩니다.
Not Exists는 Not In과 달리 Null값을 가진 Row들도 결과에 포함됩니다. 이는 연결고리 값이 Null값을 가질때 Not In은 조인 연산을 하지 않기 때문에 결과에서 제외되며Not Exists는 Exists 이하의 절이 False를 리턴하고 거기에 대한 Not이기 때문에 결과적으로 true가 되어 결과에 포함된다고 볼 수 있습니다.
1. 개념
- NOT IN
SELECT * FROM a WHERE a.key NOT IN ( SELECT b.key FROM b )
✔︎ b 테이블을 먼저 접근
✔︎ b.key를 IN 리스트에 나열 후 a.key에 공급
✔︎ b테이블: '공급자 역할'
- NOT EXISTS
SELECT * FROM a WHERE NOT EXISTS ( SELECT * FROM b WHERE a.key = b.key )
✔︎ a 테이블을 먼저 접근
✔︎ 구해진 a의 row들을 NOT EXISTS절의 b테이블에서 필터링
✔︎ b테이블: '확인자 역할'
출처: http://blog.kkomzi.net/128
2. 테스트
2.1. 사전작업
테스트 케이스는 아래와 같다.
tab2에 중복 데이터 유무에 따라..
1) tab1, tab2에 null 값이 없는 경우
2) tab1에만 null 값이 있는 경우
3) tab2에만 null 값이 있는 경우
4) tab1, tab2에 null 값이 있는 경우
DROP TABLE IF EXISTS tab1;
DROP TABLE IF EXISTS tab2;
CREATE TABLE tab1 ( a INT );
CREATE TABLE tab2 ( a INT );
INSERT INTO tab1 VALUES (1), (2), (3), (4);
INSERT INTO tab2 VALUES (1), (2), (3); --tab2에 중복 데이터가 존재하지 않을 경우
INSERT INTO tab2 VALUES (1), (2), (3), (4); --tab2에 중복 데이터가 존재할 경우
2.2. 실행할 쿼리
각 케이스에 대해서 아래와 같은 쿼리를 실행한다.
(1) SELECT a AS `IN` FROM tab1 WHERE a IN ( SELECT a FROM tab2 );
(2) SELECT a AS `EXISTS` FROM tab1 WHERE EXISTS ( SELECT '' FROM tab2 WHERE tab1.a=tab2.a);
(3) SELECT tab1.a AS `JOIN` FROM tab1 INNER JOIN tab2 ON tab1.a=tab2.a;
(4) SELECT a AS `NOT IN` FROM tab1 WHERE a NOT IN ( SELECT a FROM tab2 );
(5) SELECT a AS `NOT EXISTS` FROM tab1 WHERE NOT EXISTS ( SELECT '' FROM tab2 WHERE tab1.a=tab2.a);
(6) SELECT tab1.a AS `OUTER JOIN` FROM tab1 LEFT OUTER JOIN tab2 ON tab1.a=tab2.a WHERE tab2.a IS NULL;
3. 결과
4. 분석
4.1. tab2에 중복 데이터가 있는 경우의 JOIN 연산 (♣︎)
결과 테이블에서 ♣︎ 표시된 곳을 보자.
tab2에 중복데이터가 있는 경우에 (3)번 쿼리(SELECT tab1.a AS `JOIN` FROM tab1 INNER JOIN tab2 ON tab1.a=tab2.a;)로 연산을 수행하면 tab2의 모든 데이터가 연산에 참여하게 되므로 중복된 데이터가 출력된다.
따라서, 조인할 테이블에 중복된 데이터가 있다면, 아래와 같이 DISTINCT로 중복을 제거한 후 진행한다.
SELECT tab1.a FROM tab1 INNER JOIN (SELECT DISTINCT a FROM tab2) tab2 ON tab1.a=tab2.a;
4.2. tab2에 NULL이 들어갈 경우의 NOT IN 연산 (★)
결과 테이블에서 ★ 표시된 곳을 보자.
tab2엔 없지만 tab1에만 존재하는 데이터가 있는데도 Empty set이라고 출력이 되었다.
그 이유는 무엇일까?
tab1은 tab2의 (1,2,3,NULL)과 != 연산으로 비교가 된다.
즉 4번 쿼리는 아래와 같이 변환되어 수행되는 것이다.
(4) SELECT a FROM tab1 WHERE a NOT IN ( SELECT a FROM tab2 );
↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓
(4`) SELECT a FROM tab1 WHERE a!=1 AND a!=2 AND a!=3 AND a!=NULL;
이때 NULL과의 비교는 != 연산이 아닌 IS NOT NULL 구문을 통해서 가능하다.
따라서 a!=NULL연산은 NULL을 내뱉고 전체 결과값은 NULL이 되어 최종적으로 Empty set이 되는 것이다.
4.3. tab2에 NULL이 들어갈 경우의 NOT EXISTS 연산 (♠︎)
결과 테이블에서 ♠︎ 표시된 곳을 보자.
(6)번 쿼리 (SELECT tab1.a AS `OUTER JOIN` FROM tab1 LEFT OUTER JOIN tab2 ON tab1.a=tab2.a WHERE tab2.a IS NULL;) 에서 tab1과 tab2 조인시 tab1의 (1,2,3,4,NULL) 중에서 (1,2,3)만 JOIN에 성공하게 된다. (NULL은 조인에 참여하지 않는다.)
여기서 NOT 연산이 적용되어 (1,2,3)이 아닌 (4,NULL)이 결과에 나오게 된 것이다.
(cf. EXISTS는 서브쿼리가 TRUE인지 FALSE인지 체크하는 것이므로, NOT EXISTS는 서브쿼리가 FALSE이면 전체적으로 TRUE가 된다.)
출처: https://hellomysql.tistory.com/entry/IN-EXIST-정리 [Hello, MySQL !]