쿼리 작성할 때 IN과 EXISTS의 성능 차이에 대해서 종종 이야기하곤 합니다.
이 글에서는 먼저 IN과 EXISTS 연산 동작 방식에서 무엇이 다른지 알아보겠습니다.
그리고 NOT IN은 IN 절에 NULL 값이 있을 때 예상과 다른 결과가 도출되는데요. 그 이유에 대해 알아보겠습니다.
1 SQL엔진의 IN/EXISTS 비교 방식 차이
IN과 EXISTS 연산자의 주요 차이점은 다음과 같습니다.
| IN 연산자 | EXISTS 연산자 | |
| 1. | IN은 여러 OR 연산자 대신 사용할 수 있습니다. | 값이 반환되는지 여부를 확인하기 위해 EXISTS를 사용합니다. |
| 2. | IN은 하위 쿼리 결과가 작을 때 EXISTS 연산자보다 빠르게 작동합니다. | 하위 쿼리 결과가 크면 EXISTS가 IN 연산자보다 빠르게 작동합니다. |
| 3. | IN 조건에서 SQL 엔진은 IN 절의 모든 값을 비교합니다. | EXISTS 조건에서 true가 평가되면 SQL 엔진은 추가 일치 프로세스를 중지합니다. |
| 4. | 단일 열에 대해서만 확인하려면 IN 연산자를 사용할 수 있습니다. | 둘 이상의 단일 열에 대해 확인하려면 EXISTS 연산자를 사용할 수 있습니다. |
| 5. | IN 연산자는 어떤 것도 NULL 값과 비교할 수 없습니다. | EXISTS 절은 모든 것을 NULL과 비교할 수 있습니다. |
| 6. | 비교를 위해 직접 값 세트를 제공할 수 있습니다. | 값을 직접 비교할 수 없으며 하위 쿼리를 지정해야 합니다. |
그 중에도 SQL 엔진의 비교 방식의 차이에 대해 다뤄 보겠습니다.
- IN 조건에서 SQL 엔진은 IN 절의 모든 값을 비교합니다.
- EXISTS 조건에서 true가 평가되면 SQL 엔진은 추가 일치 프로세스를 중지합니다.
먼저 IN절에 대해 알아보겠습니다.
2 SQL엔진은 IN절의 모든 값을 비교한다
SQL 엔진에서 IN절의 동작 프로세스를 확인하기 전에 SQL에서 NULL의 논리 연산 값에 대해 알아보겠습니다.
NULL은 DB에서 값이 아닌 상태로 다른 값과 비교할 수 없지만 NULL이 연산의 결과물에 적합하지 않을 때 NULL에 대한 어떤 연산은 값을 리턴할 수 있습니다.
2-1 SQL에서 NULL의 AND, OR 논리 연산 값
쉽게 표현하면 아래와 같습니다.
OR 연산 : True > Null > False
AND 연산 : True < Null < False
NULL의 AND, OR 논리 연산의 자세한 사항은 아래 더보기를 확인해보세요.
- 피연산자가 NULL일 때 논리 연산자의 동작을 보여 줍니다.
| 왼쪽 피연산자 | 오른쪽 피연산자 | OR | AND |
|---|---|---|---|
| True | NULL | True | NULL |
| False | NULL | NULL | False |
| NULL | True | True | NULL |
| NULL | False | NULL | False |
| NULL | NULL | NULL | NULL |
| 피연산자 | NOT |
|---|---|
| NULL | NULL |
- null과 비교를 하는 것은 ‘참’(T)도 ‘거짓(F)’도 아니며, 항상 제3의 값 ‘미지’(unknown)로 나타난다.
- 만약 **Null 값이 연산의 결과물에 적합하지 않을 때** Null에 대한 어떤 연산은 값을 리턴할 수 있다.
- SELECT NULL OR TRUE -- 결과는 True이 경우, OR의 왼쪽에 있는 값이 알 수 없다는 사실이 부적절하다. 왜냐하면 OR 연산의 결과가 왼쪽 값에 상관없이 참일 것이기 때문이다.
- SQL True, False, Null진리표
OR | T | F | N |
+----+---+---+----+
| T | T | T | T |
| F | T | F | N |
| N | T | N | N |
+----+---+---+----+
NOT |
+-----+---+
| T | F |
| F | T |
| N | N |
+-----+---+
AND | T | F | N |
+-----+---+---+---+
| T | T | F | N |
| F | F | F | F |
| N | N | F | N |
+-----+---+---+---+
2-2 IN절의 동작 프로세스 쿼리로 확인
-- dept.deptno = (10, 20)
select deptno
from dept
where deptno in (10, 50, null);
-- 결과 : 10IN 조건에서 SQL엔진은 IN절의 모든 값을 비교하고 OR연산을 합니다.
위의 쿼리로 보면 dept.detpno를 in절의 10, 50, null과 모두 비교하고 OR연산을 합니다.
즉, where deptno in (10, 50, null) 은 (deptno = 10 or deptno = 50 or deptno = null) 과 같습니다.
deptno의 값 별로 확인해보겠습니다.
DEPTNO=10
(deptno=10 or deptno=50 or deptno=null)
= (10=10 or 10=50 or 10=null)
= (T or F or N)
= (T or N)
= (T)
DEPTNO=20
(deptno=10 or deptno=50 or deptno=null)
= (20=10 or 20=50 or 20=null)
= (F or F or N)
= (F or N)
= (N)
2-3 NOT IN 및 NOT OR 동작 프로세스 쿼리 확인
-- dept.deptno = (10, 20)
select deptno
from dept
where deptno not in (10, 50, null);
-- 결과 : no rows예상 결과는 20인데, 실제는 no rows라는 결과가 발생했습니다.
OR 논리 평가 시 null이 포함될 때 아무 행도 반환되지 않은 원인을 확인해보겠습니다.
DEPTNO=10
NOT (deptno=10 or deptno=50 or deptno=null)
= NOT (10=10 or 10=50 or 10=null)
= NOT (T or F or N)
= NOT (T or N)
= NOT (T)
= (F)
DEPTNO=20
NOT (deptno=10 or deptno=50 or deptno=null)
= NOT (20=10 or 20=50 or 20=null)
= NOT (F or F or N)
= NOT (F or N)
= NOT (N)
= (N)
false와 null의 or 연산 결과는 null이기 때문에 IN 절에 null이 있을 경우 false 결과값이 나올 수가 없습니다. 그러므로 NOT IN에서 false의 not인 True는 반환 될 수 없게 됩니다.
3 NOT IN 과 null 문제 방지하기 위한 NOT EXIST
NOT EXISTS를 사용하면 NULL 행의 영향을 받지 않는데요. 참고로 EXISTS는 외부 쿼리의 행이 서브 쿼리에서 참조되므로 상관 서브쿼리(correlated subquery)라고 부릅니다.
-- dept.deptno = (10, 20)
-- new_dept.deptno = (10, 50, null)
select deptno
from dept
where not exists (
select 1
from new_dept
where **dept.deptno = new_dept.deptno**
);
-- 결과 : 20EXISTS 기준으로 서브 쿼리의 where절에 외부 쿼리의 row을 대입하면서 부합하는 데이터를 1개라도 찾으면 True를 반환하고, True가 평가되면 SQL 엔진은 해당 row의 일치 프로세스를 중지하고 해당 row(외부 쿼리의 row)는 제거됩니다. 부합하는 데이터가 없다면 False를 반환합니다.
NOT EXISTS 는 반대로 서브 쿼리 결과 셋에 존재하는 경우에 False를 반환하고 해당 row의 일치 프로세스를 중지 해당 row(외부 쿼리의 row)를 제거합니다. 데이터가 없다면 True를 반환합니다.
참고로, EXISTS/ NOT EXISTS로 상관 서브쿼리를 작성할 때 select절에 작성하는 컬럼은 중요하지 않습니다. 왜냐면 EXISTS 는 where 절에 부합하는 값이 있는지 없는지만 확인하기 때문에 select까지 접근하지 않기 때문입니다. 그러므로 select 1 처럼 상수값을 적어주었습니다.
결론
NOT IN은 IN 절에 NULL이 있을 경우 잘못된 결과가 도출됩니다. 그러므로 NOT EXIST를 사용해야 합니다.
SQL 엔진은 IN 조건에서 IN 절의 모든 값을 비교하고, EXISTS은 조건에서 true가 평가되면 추가 일치 프로세스를 중지합니다.
댓글