내부 쿼리에서 메인 쿼리의 데이터를 참조하는 방식인 상관서브쿼리(Correlated Subquery)를 사용하고 있습니다. 상관서브쿼리는 메인 쿼리 적용 테이블의 행마다 서브쿼리가 반복 실행되는 방식입니다. 일반적인 쿼리의 경우 서브 쿼리의 결과를 메인에서는 단순히 이용만 하지만 상관 서브 쿼리에서는 서브 쿼리가 메인 쿼리의 값을 이용하여 값을 구하면 그 값을 다시 메인 쿼리에서 이용하는 구조입니다.
Inner조인으로 쿼리하면 서로 키가 일치하는 데이터만 구할 수 있습니다. 여기서 구하고자 하는 데이터는 한쪽 테이블에 일치하는 정보가 없을 때 이므로 outer 조인으로 해결하거나 exists 구문을 사용해야 합니다. exists는 처리결과로 boolean 값을 리턴하고 join은 처리결과로 테이블을 리턴하는 차이가 있습니다.
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 )
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 ANDa!=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가 된다.)