서론

업무 중 SQL 쿼리를 작성하다가 쿼리 결과가 예상한 것과 다른 경우가 있었다. 확인해보니 WHERE 절 안의 IN, NOT IN 쿼리에서 비교 대상이 null 인 경우가 문제였는데 관련해서 검색해보니 이 주제를 다룬 MySQL 블로그 포스트를 볼 수 있었다.

I will try to make it short and clear:
if you are writing SQL queries with “NOT IN” like
SELECT … WHERE x NOT IN (SELECT y FROM …)
you have to be sure to first understand what happens when “x” or “y” are NULL: it might not be what you want!

사실 null 이면 null 이고 아니면 아니지 않나 하는 생각이었는데 공식 블로그에 포스트로 작성되어있을 정도면 뭔가 크게 잘못 생각하고 있었다는 생각에 정독을 하게 되었고 중요한 사실을 알게 되었다.

우선 이 문제는 위의 글귀에서 볼 수 있듯이 WHERE 절의 컬럼과 비교대상 값에 NULL이 들어가는 경우에만 발생하는 문제다. 그래서 둘 다 NOT NULL 하다면 문제가 없지만 항상 NOT NULL 한 값끼리만 비교할 수는 없기 때문에 알아두는 것이 좋을 것이다.

본론

NULL은 무엇인가?

가장 먼저 이해해야 할 것은 NULL은 NULL일 뿐이라는 것이다. IN, NOT IN 쿼리가 들어갈 WHERE 절의 관점에서 볼 때 NULL은 TRUE도 아니고 FALSE도 아닌 그냥 NULL이기 때문에 이런 문제가 발생한다고 이해하는 것이 편하다. 뭔가 이해가 안 되는 문장이고 실제로 MySQL 공식 문서에서도 “The NULL value can be surprising until you get used to it.”라고 평가하고 있는 헷갈리는 명제다.

다시 말하지만 NULL은 NULL이다. 마치 C언어에서 비어있는 변수를 null로 초기화했던 것처럼 어떠한 값도 갖고 있지 않다는 것을 의미한다. TRUE인지, FALSE인지, 숫자값인지, 문자열인지 자료형에 상관없이 알 수 없는 값을 나타내는 것이다. 그래서 이 NULL에 대한 거의 대부분의 연산은 입력값에 상관없이 NULL 출력값을 가진다.

NULL에 대한 연산 결과

에를 들어 아래와 같은 쿼리는 예측할 수 있는 값을 출력한다(1은 TRUE, 0은 FALSE 값임).

1 IS TRUE	0 IS TRUE	1 IS FALSE	0 IS FALSE
1	0	0	1

그러나 NULL로 비교해보면 어떨까?

NULL IS TRUE	NULL IS FALSE
0	0

둘 다 0, 즉 FALSE가 나왔다. NULL IS TRUE 표현식이 0, FALSE니까 NULL은 FALSE라는 걸까? 그렇다고 하기엔 NULL IS FALSE 표현식도 0, FALSE가 나왔다. 즉 NULL은 TRUE도, FALSE도 아니라는 말이 된다. 불리언 연산의 참 값은 1이어야 하기 때문이다.

그러면 결과가 0이니까 그러면 NULL은 0일까? 그것 역시 아니다.

NULL = 0	NULL <> 0
NULL	NULL

위처럼 NULL에 대한 연산은 NULL로 나오는 것을 볼 수 있다. 심지어 NULL 끼리 비교해도 그렇다.

NULL = NULL	NULL <> NULL
NULL	NULL

이런 결과는 NULL이 아무런 값도 갖지 않는 비어있는 값이라는 것을 생각해보면 이해가 가는데 갖고있는 값이 아무것도 없기 때문에 다른 무언가와 비교 자체를 할 수가 없는 것이다. “NULL = NULL” 이라는 표현식은 사람이 이해하기에는 “NULL 값은 NULL 값인가?” 라는 문장인 것 같지만 컴퓨터에게는 마치 속이 보이지 않는 상자(아무런 값도 가리키거나 가지지 않는 메모리 상의 공간)를 두 개 두고 두 상자 안에 들어있는 물건(값)이 같은 물건이냐고 물어보는 것과 같다고 할 수 있다.

NULL IS... UNKNOWN

그럼 이제 뭐가 문제가 되는 것일까? 그것은 쿼리가 WHERE 절에 있는 모든 조건이 TRUE가 되는 레코드만 조회한다는 사실이다. 즉 FALSE나 알 수 없는 값인 UNKNOWN(NULL 같은)은 TRUE가 아니기 때문에 조회 대상에 포함되지 않으며 나처럼 아래와 같은 실수를 하게 된다.

Id	text
1	[NULL]
2	SELECT_ME
3	HELLO
4	WORLD
5

문서에서는 잘 표현이 안되지만 id가 1인 로우는 “[NULL]”이라는 문자열이 아니라 NULL 값을 가지며 id가 5인 로우는 빈 문자열을 가지고 있다.

SELECT *
FROM person p 
WHERE p.name in (
	NULL ,
	'SELECT_ME'
);

내가 의도했던 쿼리의 결과는 person 테이블에서 name 컬럼이 NULL 값을 가지거나 “SELECT_ME”인 레코드를 조회하는 것이었다. 하지만 실제로 조회된 레코드는 id가 2인 “SELECT_ME”밖에 없었다.

그 이유는, 이제는 알겠지만, p.name in (NULL, ‘SELECT_ME’) 라는 WHERE 절의 조건이 p.name = NULL OR p.name = ‘SELECT_ME’ 라는 조건으로 해석되었기 때문이다. 그리고 p.name = NULL 은 TRUE가 아닌 UNKNOWN이기 때문에 조회 조건에 포함되지않아 결과적으로 p.name = ‘SELECT_ME’에 해당하는 id가 2인 ‘SELECT_ME’ 레코드만 조회된 것이다.

그러면 p.name = NULL, 즉 id가 1인 레코드도 조회하고 싶다면 어떻게 해야 할까? 이는 IS NULL, IS NOT NULL 같은 NULL 전용 연산을 사용할 수 있다.

SELECT *
FROM person p 
WHERE p.name IN (
	'SELECT_ME'
) OR p.name IS NULL;

이 경우 다음처럼 둘 다 잘 조회될 것이다.

Id	text
1	[NULL]
2	SELECT_ME

마지막으로 IN이 아니라 NOT IN 연산에서 NULL은 어떨까? 이때 역시 NULL은 UNKNOWN이기 때문에 조회 조건에 포함되지 않는다.

SELECT *
FROM person p 
WHERE p.name NOT IN (
	'SELECT_ME'
);

Id	text
3	HELLO
4	WORLD
5

IS UNKNOWN TRUE?

블로그 포스트에서는 이런 문제에 대해 좀 특이한 방법도 제시하고 있다. 만약 처음에 생각했던 것처럼 NULL과 ‘SELECT_ME’를 둘 다 조회하고 싶다면 다음처럼 쿼리를 작성할 수 있다.

SELECT *
FROM person p 
WHERE p.name IN (
	'SELECT_ME'
) IS NOT FALSE;

사실 생각하지도 못한 방법인데(왜냐면 애초에 TRUE, FALSE 말고 UNKNOWN이라는 존재를 생각하지 못했으니) 잘 동작해서 놀랐다. 이게 뭔가 해서 살펴보니 IS NOT FALSE, 즉 TRUE와 UNKNOWN 둘 다 받겠다는 동작으로 보강한 것을 알 수 있다.

반대로 NOT IN, ‘SELECT_ME’를 빼고 조회하지만 NULL은 가져오고 싶다고 하면 다음처럼 쿼리를 작성할 수 있다.

SELECT *
FROM person p 
WHERE p.name IN (
	'SELECT_ME'
) IS NOT TRUE;

NOT IN을 목적으로 했지만 실제로 작성된 쿼리는 IN인 것에 유의하라. 역시 TRUE가 아닌 UNKNOWN과 FALSE에 해당하는 레코드를 조회하기 때문에 NULL 값도 조회되는 것을 알 수 있다.

IS NULL, IS NOT NULL을 안 쓰고도 할 수 있는 방법이라 신기하긴 한데 만약 SQL에 익숙하지 않다면 낯설고 또 헷갈릴 수 있는 부분이 아닐까 싶다. 나도 그렇지만 대개 WHERE 절 안에는 조건을 <값> <비교 연산자> <값> 패턴으로 나열하고 끝나는게 대부분이었기 때문에 그런 것 같다.

신기한 것은 이 포스트를 읽어보면 위의 방식대로 작성했을 때 Antijoin 이라는 옵티마이저 기능을 사용할 수 있다고 한다. 어떤 내용인지는 더 알아봐야겠지만 성능상 이점이 있다고 하니 고려해볼만 하다.

결론

이 문제를 처음 발견했을 때는 이렇게 단순한 조회는 아니고 여러 테이블을 조인하고 십몇가지 조건을 걸어서 조회하던 쿼리기 때문에 처음에는 검색조건을 잘못 받았거나 WHERE 절에 잘못 추가된 게 있겠지 싶었다. 그런데 직접 몇 번 쿼리를 실행해보고 뭔가 결과가 이상하길래 살펴보니 뭔가 NULL 값이 빠져있었고 현재 쿼리에서는 NULL을 WHERE 조건에 그대로 사용하고 있는데 다른 코드에서는 IS NULL, IS NOT NULL 연산을 써서 NULL을 걸러냈던 것을 기억해냈다. 그래서 혹시 IN 절에 NULL이 들어가면 문제가 되는게 아닌가 해서 검색했더니 바로 위의 블로그 포스트를 찾을 수 있었다.

문제 자체는 쉽게 찾아서 해결했지만 이게 MySQL만의 함수, 방언도 아니고 표준 SQL 동작인데도 모르고 있었다는 사실이 스스로도 놀라웠고 자칫 큰 문제로도 이어질 수 있는 실수기 때문에 SQL을 진짜 잘 공부해야겠다고 다짐하게 되었다.

그래서 SQL을 공부하는김에 SQLD 자격시험도 신청하려고 했는데 10월 20일까지가 신청기간이었다. 이런…