NULL 다루기

값이 아니라 "모른다" 입니다

NULL 은 값이 없다는 표시입니다. 0 도 아니고 빈 문자열도 아닙니다. 셋은 뜻이 서로 다릅니다.

SELECT DATALENGTH(N'')                        AS 빈문자열_바이트,
       DATALENGTH(CAST(NULL AS NVARCHAR(10))) AS NULL_바이트;

빈 문자열은 0바이트짜리 값이라 길이를 잴 수 있습니다. NULL 은 잴 것 자체가 없어 길이도 NULL 입니다.

참도 거짓도 아닌 세 번째

모르는 값끼리는 같은지 다른지도 알 수 없습니다. 그래서 비교하면 참도 거짓도 아닌 알 수 없음이 나오고, WHERE 는 참인 행만 남기므로 그 행은 결과에서 빠집니다.

SELECT CASE WHEN NULL = NULL  THEN 'equal' ELSE 'unknown' END AS eq_test,
       CASE WHEN NULL <> NULL THEN 'diff'  ELSE 'unknown' END AS ne_test,
       CASE WHEN NULL IS NULL THEN 'yes'  ELSE 'no'      END AS is_test;

같지도 않고 다르지도 않습니다. 그래서 값이 없는 행을 찾으려면 IS NULL · IS NOT NULL 이라는 전용 문법을 사용해야 합니다. 1.6 에서 = NULL 이 한 건도 찾지 못한 까닭입니다.

<> 로 걸러도 값이 없는 행은 빠집니다. 1.6 에서 20명 가운데 <> 'hong@example.com' 이 19가 아니라 15를 낸 것이 이 때문입니다.

셈에 섞이면 결과가 사라집니다

SELECT 100 + NULL AS 더하기, 100 * NULL AS 곱하기;

모르는 값에 100 을 더해도 여전히 모릅니다. 문자열을 + 로 이을 때 전체가 NULL 이 되던 것(1.5)과 같은 까닭입니다.

값이 없을 때 대신 낼 것 — ISNULL 과 COALESCE

화면에 NULL 을 그대로 내보낼 수는 없으니 대신 낼 값을 정합니다. 함수가 둘인데 같아 보이지만 다릅니다.

SELECT TOP 1
    ISNULL(email, N'(없음)')   AS isnull_결과,
    COALESCE(email, N'(없음)') AS coalesce_결과,
    DATALENGTH(ISNULL(email, N'(없음)'))   AS isnull_바이트,
    DATALENGTH(COALESCE(email, N'(없음)')) AS coalesce_바이트
FROM Member.USERS
WHERE email IS NULL
ORDER BY num;

ISNULL 쪽에서 한글이 깨졌습니다. ISNULL 은 첫 인자의 형식에 결과를 맞추는데, email 이 varchar 라 한글을 담지 못합니다(1.4). COALESCE 는 두 인자를 함께 보고 더 넓은 형식을 선택하므로 nvarchar 가 유지됩니다.

형식은 길이도 자릅니다.

SELECT ISNULL(CAST(NULL AS VARCHAR(3)), 'abcdefghij')   AS isnull_잘림,
       COALESCE(CAST(NULL AS VARCHAR(3)), 'abcdefghij') AS coalesce_안잘림;

VARCHAR(3) 에 맞추느라 abc 만 남았습니다. 오류 없이 잘립니다.

COALESCE 는 여러 개를 봅니다

앞에서부터 보다가 처음 만나는 값이 아닌 것을 냅니다.

SELECT COALESCE(NULL, NULL, N'셋째가 나옵니다', N'넷째') AS 결과;

COALESCE 를 기본으로 사용하십시오. 글자를 다룰 때 값이 조용히 깨지거나 잘리는 일을 피할 수 있고, 다른 데이터베이스로 옮겨도 그대로 동작합니다.

같으면 NULL 로 — NULLIF

거꾸로 어떤 값을 NULL 로 바꾸는 함수도 있습니다. 0 으로 나누는 것을 막을 때 흔히 사용합니다.

SELECT NULLIF(10, 10) AS 같으면_NULL,
       NULLIF(10, 20) AS 다르면_원래값;

-- 나누는 값이 0 이면 오류가 나는 대신 NULL 이 나옵니다
SELECT total / NULLIF(cnt, 0) FROM …

세는 함수는 NULL 을 빼고 셉니다

SELECT COUNT(*) AS 전체행, COUNT(email) AS 값있는행
FROM Member.USERS;

COUNT(*) 는 행을 세고, COUNT(열) 은 그 열에 값이 있는 행만 셉니다. 전자 메일이 없는 넷이 빠져 16 이 됐습니다.

SUM · AVG 도 마찬가지로 NULL 을 빼고 셈합니다. 평균을 낼 때 특히 주의하십시오 — 값이 없는 행을 0 으로 치고 싶다면 AVG(COALESCE(열, 0)) 처럼 채워 넣어야 합니다. 집계는 2.2 에서 다룹니다.

NOT IN 은 한 건도 내지 않을 수 있습니다

이 자리가 가장 찾기 어렵습니다. 목록에 NULL 이 하나라도 섞이면 NOT IN 은 아무것도 내지 않습니다.

-- 아이디가 "누군가의 전자 메일"과 같지 않은 회원을 세려는 문장입니다
SELECT COUNT(*) AS not_in_결과
FROM Member.USERS
WHERE user_id NOT IN (SELECT email FROM Member.USERS);

아이디와 전자 메일은 생김새부터 다르니 20명 모두 나와야 할 것 같은데 0 입니다.

NOT IN 은 속으로 이렇게 따집니다 — user_id <> 값1 AND user_id <> 값2 AND … 그런데 목록에 NULL 이 있으므로 그 자리가 알 수 없음이 되고, AND 로 이어진 조건은 하나라도 알 수 없으면 전체가 참이 되지 못합니다.

NOT EXISTS 로 바꾸면 뜻한 대로 나옵니다.

SELECT COUNT(*) AS not_exists_결과
FROM Member.USERS AS U
WHERE NOT EXISTS (
    SELECT 1 FROM Member.USERS AS X WHERE X.email = U.user_id
);

NOT EXISTS 는 있는지 없는지만 보므로 NULL 에 흔들리지 않습니다. 값이 없을 수 있는 열을 목록으로 사용할 때는 이쪽을 선택하십시오. 서브쿼리는 2.4 에서 다룹니다.

목록에서 NULL 을 빼는 방법도 있습니다 — WHERE email IS NOT NULL 을 안쪽에 더하면 NOT IN 도 제대로 동작합니다.

직접 해보기