WHERE email = NULL로 쿼리를 짰는데 결과가 없습니다. 분명히 NULL인 데이터가 있는데 왜 조회가 안 되는지 모릅니다. NULL = NULL은 TRUE가 아니라 UNKNOWN입니다. 이 3값 논리를 이해하지 못하면 NULL 관련 쿼리에서 계속 예상과 다른 결과를 보게 됩니다.

확대

NULL은 '알 수 없음'이다

NULL은 "값이 없다"가 아니라 "값을 알 수 없다"를 의미합니다. 이 차이가 SQL에서 NULL이 특별하게 동작하는 근본 이유입니다. "알 수 없는 값"과 어떤 값을 비교하면 그 결과 역시 "알 수 없음(UNKNOWN)"입니다. NULL = NULL조차 "두 개의 알 수 없는 값이 같은지 알 수 없다"는 이유로 UNKNOWN을 반환합니다.

NULL을 확인하는 유일한 올바른 방법은 IS NULL과 IS NOT NULL입니다.

SELECT
    NULL = NULL      AS "NULL = NULL",
    NULL != NULL     AS "NULL != NULL",
    NULL = 1         AS "NULL = 1",
    NULL != 1        AS "NULL != 1",
    NULL IS NULL     AS "NULL IS NULL",
    NULL IS NOT NULL AS "NULL IS NOT NULL";

실행 완료 또는 조회 결과가 표시됩니다.

결과: 처음 네 비교는 모두 NULL(UNKNOWN)이고, IS NULL만 TRUE, IS NOT NULL만 FALSE를 반환합니다.

3값 논리(3VL) — AND/OR 진리표

SQL은 TRUE/FALSE의 2값 논리가 아닌 TRUE/FALSE/UNKNOWN의 3값 논리를 사용합니다. UNKNOWN이 포함된 논리 연산 결과를 이해해야 WHERE 절의 동작을 예측할 수 있습니다.

AND 진리표 — FALSE가 하나라도 있으면 FALSE, 나머지는 UNKNOWN이 전파됩니다:

OR 진리표 — TRUE가 하나라도 있으면 TRUE, 나머지는 UNKNOWN이 전파됩니다:

WHERE 절은 결과가 TRUE인 행만 반환합니다. UNKNOWN은 FALSE와 같은 효과를 냅니다. 이것이 NULL 비교가 포함된 조건에서 예상치 못하게 행이 제외되는 이유입니다.

WHERE 절에서 NULL 함정

부서가 NULL인 직원이 "개발팀이 아닌 직원" 필터에서 누락되는 경우입니다. NULL != '개발팀'은 UNKNOWN이 되어 WHERE에서 제외됩니다.

CREATE TABLE employees (
    id         BIGSERIAL PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    salary     NUMERIC(10, 2)
);

INSERT INTO employees (name, department, salary) VALUES
    ('김철수', '개발팀',  5000000),
    ('이영희', '마케팅',  4500000),
    ('박민준', NULL,      3800000),
    ('최수진', '개발팀',  NULL);

SELECT * FROM employees WHERE department != '개발팀';

SELECT * FROM employees
WHERE department != '개발팀'
   OR department IS NULL;

첫 번째 쿼리는 박민준(department=NULL)을 누락합니다. 두 번째 쿼리에서 OR department IS NULL을 추가하면 박민준도 포함됩니다.

SELECT * FROM employees WHERE department = NULL;
SELECT * FROM employees WHERE salary != NULL;

SELECT * FROM employees WHERE department IS NULL;
SELECT * FROM employees WHERE salary IS NOT NULL;

NOT IN 함정 — 가장 위험한 NULL 버그

NOT IN 리스트에 NULL이 포함되면 전체 쿼리 결과가 0건이 됩니다. 이유는 x NOT IN (1, 2, NULL)이 x != 1 AND x != 2 AND x != NULL로 전개되는데, x != NULL이 항상 UNKNOWN이 되어 AND 전체가 UNKNOWN이 되기 때문입니다.

SELECT * FROM products
WHERE category_id NOT IN (1, 2, NULL);

이 쿼리는 결과가 0건입니다. 리스트에 NULL이 포함되어 있으면 어떤 category_id 값도 UNKNOWN 비교를 피할 수 없습니다.

확대

SELECT * FROM orders
WHERE customer_id NOT IN (
    SELECT customer_id FROM blacklist
);

SELECT * FROM orders o
WHERE NOT EXISTS (
    SELECT 1 FROM blacklist b
    WHERE b.customer_id = o.customer_id
);

SELECT o.* FROM orders o
LEFT JOIN blacklist b ON o.customer_id = b.customer_id
WHERE b.customer_id IS NULL;

-- blacklist에 NULL이 섞이도록 한 건 삽입
INSERT INTO blacklist (customer_id) VALUES (NULL);

-- (버그) NOT IN — 리스트에 NULL이 있어 결과가 0건
SELECT count(*) AS not_in_result
FROM orders
WHERE customer_id NOT IN (SELECT customer_id FROM blacklist);

-- (정답) NOT EXISTS — NULL에 안전
SELECT count(*) AS not_exists_result
FROM orders o
WHERE NOT EXISTS (
    SELECT 1 FROM blacklist b WHERE b.customer_id = o.customer_id
);

 not_in_result
---------------
             0    -- 주문이 있어도 0건 (NULL 때문에 전부 UNKNOWN)

 not_exists_result
-------------------
            1280    -- 실제 블랙리스트 제외 주문 수

SELECT
    COUNT(*)                AS 전체직원수,
    COUNT(department)       AS 부서있는직원수,
    COUNT(salary)           AS 급여확정직원수,
    AVG(salary)             AS 평균급여_null제외,
    AVG(COALESCE(salary, 0)) AS 평균급여_null을0으로
FROM employees;

앞에서 3값 논리를 진리표·WHERE·NOT IN·집계로 나눠서 봤습니다. 이 조각들을 하나로 잇는 가장 좋은 방법은, salary가 NULL인 행 한 개가 쿼리를 위에서 아래로 통과하는 과정을 따라가 보는 것입니다. 그러면 그 행이 정확히 어느 단계에서 조용히 사라지고, 어느 단계에서 숫자를 왜곡하는지가 한눈에 보입니다. NULL 버그는 대부분 "값이 왜 빠졌지"와 "합계가 왜 안 맞지" 두 가지인데, 둘 다 이 흐름의 특정 지점에서 생깁니다.

salary = NULL 인 행 하나가 아래 쿼리를 통과한다
SELECT department, AVG(salary) FROM employees WHERE salary > 3000000 GROUP BY department
   │
   ① 값의 정체 — NULL = '값이 없음 / 알 수 없음'(미지)
   │     빈 문자열('')도 0도 아니다. "모른다"는 표식일 뿐
   │
   ② 산술·비교에 NULL 전파 — salary > 3000000 을 평가
   │     NULL > 3000000 = UNKNOWN (TRUE도 FALSE도 아님)
   │     NULL + 1, NULL = NULL, NULL != '개발팀' 도 전부 NULL/UNKNOWN
   │
   ③ WHERE는 TRUE만 통과 — UNKNOWN 행은 탈락
   │     이 행은 UNKNOWN이라 결과에서 조용히 사라진다(에러 없음)
   │     = NULL·!= 값 필터에서 NULL 행이 빠지는 바로 그 지점
   │
   ④ 집계는 NULL을 무시 — 살아남아도 COUNT/AVG에서 제외
   │     COUNT(salary)·AVG(salary)는 NULL 행을 세지 않는다
   │     COUNT(*)와 값이 갈리고, 평균의 분모가 달라진다
   │
   ⑤ 명시적 처리 — IS NULL / COALESCE 로 의도를 못박음
   ▼
   여기서 손대지 않으면 ②~④가 결과를 말없이 바꾼다

각 단계에서 무슨 일이 일어나고, 어떤 함정이 생기나:

즉 "NULL 행이 사라졌다"는 ② 전파 → ③ 탈락에서, "숫자가 안 맞다"는 ④ 집계 무시에서 옵니다. 그래서 진단은 두 단계를 나눠 봅니다 — 먼저 COUNT(*)와 COUNT(col)의 차이로 컬럼별 NULL 행 수를 세고(④ 확인), = NULL을 IS NULL로 바꿔 실제로 NULL 행이 있는지 봅니다(②③ 확인). 미지값이 필터·집계·NOT IN 중 어느 단계에 개입했는지를 짚으면, 원인을 한 곳으로 좁힐 수 있습니다.

NULL을 포함한 컬럼을 화면에 표시하면 "null"이라는 문자열이 보입니다. 정렬하면 NULL이 맨 앞에 올지 맨 뒤에 올지 DB마다 다릅니다. 나눗셈 분모에 NULL이 들어오면 오류가 납니다. 이런 상황에서 COALESCE, NULLIF, NULLS FIRST/LAST를 쓰면 애플리케이션 코드 없이 SQL에서 바로 처리할 수 있습니다.

COALESCE — NULL을 기본값으로 대체

COALESCE는 인자를 왼쪽부터 평가해 NULL이 아닌 첫 번째 값을 반환합니다. 모든 인자가 NULL이면 NULL을 반환합니다. 연락처 우선순위 폴백, 집계의 NULL을 0으로 처리, 디스플레이용 기본값 설정에 광범위하게 사용됩니다.

SELECT
    name,
    COALESCE(nickname, name)         AS 표시이름,
    COALESCE(phone, '연락처 없음')    AS 연락처,
    COALESCE(salary, 0)              AS 급여
FROM employees;

SELECT
    COALESCE(mobile_phone, office_phone, home_phone, '연락 불가') AS 대표연락처
FROM contacts;

SELECT
    SUM(COALESCE(bonus, 0))  AS 총보너스,
    AVG(COALESCE(score, 50)) AS 평균점수
FROM employees;

NULLIF — 특정 값을 NULL로 변환

NULLIF(a, b)는 a = b이면 NULL을 반환하고, 다르면 a를 반환합니다. 가장 일반적인 사용 패턴은 0으로 나누기 방지입니다.

SELECT
    NULLIF(status, 'unknown') AS clean_status,
    NULLIF(score, -1)         AS valid_score,
    NULLIF(phone, '')         AS phone_or_null
FROM users;

SELECT
    total_sales,
    total_orders,
    total_sales / NULLIF(total_orders, 0) AS avg_order_value
FROM monthly_stats;

SELECT
    completed_tasks,
    total_tasks,
    ROUND(completed_tasks * 100.0 / NULLIF(total_tasks, 0), 1) AS completion_rate
FROM projects;

total_orders가 0일 때 NULLIF(total_orders, 0)이 NULL을 반환하고, 나누기 결과도 NULL이 되어 Division by zero 오류를 방지합니다.

IS DISTINCT FROM — NULL 안전 비교

일반 != 연산자는 NULL이 포함되면 UNKNOWN을 반환합니다. IS DISTINCT FROM은 NULL을 하나의 구체적인 값처럼 비교해 항상 TRUE 또는 FALSE를 반환합니다. NULL IS DISTINCT FROM NULL은 FALSE (두 NULL은 같다)이고, 1 IS DISTINCT FROM NULL은 TRUE입니다.

SELECT
    a,
    b,
    a != b               AS 일반비교,
    a IS DISTINCT FROM b AS NULL안전비교
FROM (VALUES
    (1, 1),
    (1, 2),
    (1, NULL),
    (NULL, NULL)
) AS t(a, b);

변경 감지 로직에서 IS DISTINCT FROM은 특히 유용합니다. 이전/신규 값 모두 NULL일 수 있는 컬럼을 비교할 때 일반 !=를 쓰면 NULL 컬럼의 변경이 감지되지 않습니다.

UPDATE users
SET
    profile_updated_at = NOW(),
    version = version + 1
WHERE
    old_email IS DISTINCT FROM new_email
    OR old_name IS DISTINCT FROM new_name;

ORDER BY에서 NULL 위치 지정

NULL을 포함한 컬럼을 정렬할 때 NULL이 어디 위치할지는 DBMS마다 다릅니다. PostgreSQL은 ASC 정렬에서 NULL이 마지막, DESC 정렬에서 NULL이 처음에 옵니다. NULLS FIRST와 NULLS LAST로 명시적으로 지정하면 DBMS 간 차이 없이 일관된 동작을 보장합니다.

SELECT name, last_login FROM users
ORDER BY last_login DESC NULLS LAST;

SELECT name, salary FROM employees
ORDER BY salary ASC NULLS FIRST;

SELECT name, salary FROM employees
ORDER BY COALESCE(salary, 999999999) ASC;

실전 NULL 처리 패턴 모음

SELECT * FROM contacts
WHERE NULLIF(TRIM(phone), '') IS NOT NULL;

SELECT * FROM products
WHERE (category IS NULL OR category = '미분류')
  AND is_active = true;

SELECT
    data->>'phone'          AS json_phone,
    (data->>'phone') IS NULL AS is_missing
FROM user_profiles;

SELECT
    COUNT(*)                                                AS total_rows,
    COUNT(department)                                       AS has_dept,
    COUNT(DISTINCT department)                              AS unique_dept,
    COUNT(DISTINCT COALESCE(department, '미배정'))           AS with_null_dept
FROM employees;

NULLIF(TRIM(phone), '')는 공백만 있는 문자열도 NULL로 처리해 "진짜 빈 연락처"를 제외합니다. COUNT(department)는 NULL을 제외하고, COUNT(*)는 포함하므로 두 값의 차이가 NULL 행 수입니다.

지금까지 NULL의 3값 논리가 WHERE·NOT IN·집계에서 어떻게 함정을 만드는지 봤습니다. 그런데 같은 논리가 제약조건(constraint)에서도 그대로 작동합니다 — 그래서 제약을 걸어 두고도 막았다고 믿은 데이터가 새어 나갑니다.

• CHECK는 UNKNOWN을 통과시킵니다. CHECK 제약은 조건이 명시적으로 FALSE인 행만 거부합니다. CHECK (price > 0)가 걸려 있어도 price가 NULL이면 price > 0이 UNKNOWN이 되어 거부되지 않고 저장됩니다. 값의 범위를 강제해도 NULL은 예외가 되는 셈입니다. NULL까지 막으려면 NOT NULL을 함께 걸거나 CHECK (price > 0 AND price IS NOT NULL)로 명시합니다.
• UNIQUE는 NULL을 서로 다른 값으로 봅니다. 표준 SQL에서 UNIQUE 제약은 두 NULL을 같지 않다고 판단하므로, UNIQUE 컬럼이라도 NULL인 행은 여러 개 존재할 수 있습니다. email에 UNIQUE를 걸어도 email이 비어(NULL) 있는 계정은 중복으로 생성됩니다.
• FOREIGN KEY는 NULL을 검사하지 않습니다. FK 컬럼이 NULL이면 참조 무결성 검사를 건너뜁니다(참조 대상이 없어도 통과). 선택적 관계에서는 유용하지만, 반드시 부모가 있어야 한다면 FK만으로는 부족하고 NOT NULL이 필요합니다.

정리하면, 제약조건은 값이 있을 때의 규칙이고 NULL은 그 규칙의 사각지대입니다. 필수 값에는 NOT NULL을 1순위로 두고, 그 위에 CHECK·UNIQUE·FK를 얹어야 의도한 무결성이 완성됩니다.