SQL NULL 비교, = NULL이 행을 찾지 못하는 이유

문제 발생

deleted_at = NULL 조건을 넣었는데 삭제되지 않은 행이 한 건도 나오지 않습니다. NULL은 빈 문자열이나 0이 아니라 ‘알 수 없음’을 나타내므로 일반 동등 비교 결과도 true가 아닌 unknown이 됩니다.

SQL의 NULL은 알 수 없음을 나타내므로 = NULL은 true가 아니며 IS NULL, IS DISTINCT FROM 등 의도한 three-valued logic을 사용해야 합니다.

원인 분석

WHERE는 조건이 true인 행만 남깁니다. deleted_at = NULL과 deleted_at <> NULL은 모두 unknown이므로 행이 제외됩니다. NULL 포함 동등성을 직접 다뤄야 할 때는 DB 제품이 지원하는 IS DISTINCT FROM의 의미를 확인합니다.

SELECT
  NULL = NULL AS ordinary_equal,
  NULL IS NULL AS is_null,
  NULL IS NOT DISTINCT FROM NULL AS null_safe_equal;

PostgreSQL에서는 결과가 각각 NULL, true, true입니다. 다른 DB 제품의 NULL-safe 연산자 문법은 다를 수 있습니다.

해결 방안

NULL 여부는 IS NULL과 IS NOT NULL로 표현합니다. 두 nullable 값을 NULL까지 같은 값으로 비교하려면 PostgreSQL의 IS NOT DISTINCT FROM을 사용할 수 있습니다.

SELECT id, email
FROM users
WHERE deleted_at IS NULL;

SELECT *
FROM changes
WHERE before_value IS DISTINCT FROM after_value;

NULL, 빈 문자열, 0, 실제 값이 각각 들어간 테스트를 만들고 기대 행 수를 확인합니다. application에서 NULL을 임의의 빈 문자열로 바꾸면 의미가 달라질 수 있으므로 schema와 API 계약도 함께 맞춥니다.

공식 문서

• PostgreSQL: Comparison Functions and Operators (새 창에서 열림)

사용 중인 DB 제품과 version의 three-valued logic 및 NULL-safe 비교 문법을 확인합니다.