HAEJUN RECORDS

PostgreSQL NULL 처리와 3값 논리

10 min read

Summary

  • NULL은 “값이 없음”이 아니라 “값을 모름” 이다. 비교 연산자가 이상하게 동작하는 이유는 전부 여기서 나온다.
  • =, <>는 피연산자에 NULL이 끼면 참도 거짓도 아닌 NULL을 반환한다(3값 논리). null = null조차 참이 아니다.
  • WHERE는 결과가 TRUE인 행만 통과시키므로, NULL을 뱉는 조건식은 “거짓처럼” 동작해 행이 조용히 사라진다.
  • 해결은 두 층으로 갈린다. 조건식 층은 IS DISTINCT FROM으로 NULL을 값처럼 비교하고, 값 층은 COALESCE(NULL→값)와 NULLIF(값→NULL)로 치환한다.
  • 조건식 문제를 값 치환으로 우회하지 않는다. coalesce(status, '') <> 'done'은 센티널 값 보증과 인덱스 차단 같은 비용을 만든다.

들어가며

NULL은 “값이 없음”이 아니라 “값을 모름”이다. 이 한 문장이 아래 모든 동작의 원인이다.

모르는 값끼리 같은지 물으면 답은 “같다/다르다”가 아니라 “알 수 없다”가 되어야 한다. SQL은 이 “알 수 없다”를 세 번째 진리값 NULL로 표현하고, 이것을 3값 논리(three-valued logic)라고 한다.

문제는 조건식이다. WHERE와 JOIN ... ON은 결과가 TRUE인 행만 통과시킨다. NULL은 TRUE가 아니므로 탈락한다. 즉 실무에서 “알 수 없음”은 “거짓”과 구별되지 않는다. 그 결과 의도한 행이 결과 집합에서 소리 없이 빠진다. 에러가 나지 않기 때문에 더 위험하다.

이 글은 그 현상을 먼저 확인하고, 대응 도구를 조건식 층과 값 층으로 나눠 정리한다.

연산자별 동작 확인

NULL이 끼는 순간 비교 연산자와 술어의 동작이 갈린다. 아래 7개 쿼리로 그 경계를 확인한다.

NULL 관련 연산자 결과

select 8 <> 8;                     -- false
select 8 <> null;                  -- null
select null <> 8;                  -- null
 
select null is distinct from 8;    -- true
select 8 is distinct from null;    -- true
 
select null is null;               -- true
select null = null;                -- null
식결과이유
8 <> 8false양쪽 모두 값이므로 정상 비교
8 <> nullnull한쪽이 미지의 값이라 판정 불가
null <> 8null순서와 무관하게 동일
null = nullnull모르는 값 둘이 같은지도 모름
null is nulltrueIS NULL은 비교가 아니라 상태 검사
null is distinct from 8trueNULL을 하나의 값으로 보고 “다르다”로 판정
8 is distinct from nulltrue순서와 무관하게 NULL을 값으로 보고 판정

핵심 대비는 표의 아래 세 줄이다. =와 <>는 값을 비교하는 연산자라 NULL이 들어오면 판정을 포기한다. 반면 IS NULL과 IS DISTINCT FROM은 NULL 자체를 다루도록 만들어진 술어(predicate)라 절대 NULL을 반환하지 않는다.

조건식 층

a IS DISTINCT FROM b는 두 값이 서로 구별되는지를 묻는 술어다. NULL을 특별 취급하지 않고 값 하나로 보기 때문에 진리표가 다음처럼 닫힌다.

aba <> ba IS DISTINCT FROM b
88falsefalse
89truetrue
8NULLnulltrue
NULLNULLnullfalse

IS NOT DISTINCT FROM은 그 부정으로, NULL-safe 등가 비교다. null is not distinct from null은 true가 된다.

문제가 드러나는 지점

값 하나를 제외하려고 쓴 조건이 NULL 행까지 날려버린다.

<> 필터에서 NULL 행이 사라지는 경우

-- 의도: status가 'done'이 아닌 행 전부
select * from task where status <> 'done';
-- 실제: status가 NULL인 행은 조건식이 null이라 탈락한다
 
-- 의도대로
select * from task where status is distinct from 'done';

변경 감지에서도 같다. NULL이었던 컬럼에 값이 들어오는 것도 명백한 변경인데, <>로는 잡히지 않는다.

변경된 행만 갱신하기

update target t
   set val = s.val
  from source s
 where t.id = s.id
   and t.val is distinct from s.val;   -- NULL ↔ 값 전환도 변경으로 인식

주의할 점

IS DISTINCT FROM은 편리하지만 실행계획과 집합 연산에서 두 가지 제약이 따른다.

인덱스와 집합 연산

  • IS DISTINCT FROM은 일반 비교 연산자가 아니라서 B-tree 인덱스를 타지 못하는 경우가 많다. 대량 테이블의 필터 조건으로 쓸 때는 EXPLAIN으로 실행계획을 확인한다.
  • EXCEPT, UNION, GROUP BY, DISTINCT 등 집합 연산은 이미 내부적으로 NULL을 같은 값으로 취급한다(IS NOT DISTINCT FROM 의미론). 조건식과 집합 연산의 NULL 규칙이 다르다는 점을 기억한다.

집계 결과에서 롤업으로 생긴 NULL과 원본 NULL이 섞이는 문제는 grouping-sets에서 GROUPING()으로 다룬다.

값 층

값 층의 두 함수는 서로 반대 방향으로 동작한다. COALESCE는 NULL을 값으로 바꾸고, NULLIF는 값을 NULL로 바꾼다. 조건식을 손대지 않고 데이터 자체를 정규화할 때 쓴다.

COALESCE

COALESCE(a, b, ...)는 인자를 왼쪽부터 훑어 NULL이 아닌 첫 값을 반환한다. NULL을 대체값으로 치환하는 도구다.

NULL을 기본값으로 치환

select coalesce(col1, change_value) as fitted_col1
from tbl;

집계나 출력에서 NULL을 기본값으로 메울 때 쓴다.

표시명 폴백과 집계 보정

select name, coalesce(nickname, name) as display_name
from member;
 
select sum(coalesce(amount, 0)) from orders;
  • 인자는 여러 개를 이어 붙일 수 있고, 전부 NULL이면 결과도 NULL이다.
  • 인자들의 타입은 서로 호환되어야 한다.
  • 왼쪽부터 평가하다 값을 찾으면 나머지는 평가하지 않는다.

NULLIF

NULLIF(a, b)는 a = b이면 NULL을, 아니면 a를 반환한다. COALESCE와 반대 방향으로, 특정 값을 NULL로 되돌린다.

0으로 나누기 회피가 대표적인 용례다.

0 나누기 회피

select total / nullif(cnt, 0) from stat;   -- cnt가 0이면 에러 대신 NULL

빈 문자열을 NULL로 정규화할 때도 쓴다.

빈 문자열 정규화

select nullif(trim(memo), '') from note;

내부적으로 = 비교를 쓴다는 점만 유의한다. a가 NULL이면 비교 자체가 NULL이 되어 결과는 그대로 NULL이다.

층을 섞지 않는다

세 도구는 모두 NULL을 다루지만 작동하는 층이 다르다.

도구층하는 일
IS DISTINCT FROM조건식(boolean)NULL이 섞여도 TRUE/FALSE만 반환하게 만든다
COALESCE(a, b)값(scalar)NULL을 대체값으로 치환한다
NULLIF(a, b)값(scalar)특정 값을 NULL로 되돌린다

COALESCE로 조건식 문제를 우회하는 관용구가 흔하지만 권장하지 않는다.

권장하지 않는 우회

select * from task
 where coalesce(status, '') <> 'done';

이 방식은 세 가지 비용을 만든다. 첫째, 실제 데이터에 절대 등장하지 않는 센티널 값을 개발자가 보증해야 한다. 둘째, 컬럼에 함수를 씌워 인덱스 사용을 막는다. 셋째, 타입마다 센티널을 새로 정해야 한다.

의도가 "NULL도 다른 값으로 쳐라"라면, 그것을 그대로 말하는 `IS DISTINCT FROM`이 정확하다.

마치며

  • NULL이 끼는 순간 =와 <>는 판정을 포기하고 NULL을 낸다. 조건식에서 NULL은 통과하지 못하므로 행이 사라진다.
  • NULL을 값으로 취급해 비교하려면 IS DISTINCT FROM / IS NOT DISTINCT FROM을 쓴다.
  • NULL 자체를 없애려면 COALESCE, 특정 값을 NULL로 만들려면 NULLIF를 쓴다.
  • 조건식 문제를 값 치환으로 우회하지 않는다. 층에 맞는 도구를 쓴다.

Reference

Graph View