MySQL에서 처음 NULL 값을 찾을 때 많이들 WHERE 칼럼명 = NULL 이라고 씁니다. 오류는 안 나는데 결과가 한 줄도 안 나옵니다. NULL은 "값이 없음"을 뜻하는 특별한 표시라서, NULL과 무언가를 = 로 비교하면 참도 거짓도 아닌 NULL이 되고, WHERE 절은 참인 행만 돌려주기 때문입니다. <> 나 != 도 마찬가지라서 WHERE 칼럼명 <> '' 같은 조건에도 NULL 행은 빠집니다.

NULL을 찾는 두 가지 방법

-- NULL인 행
where 칼럼명 is NULL
where 칼럼명 <=> NULL

-- NULL이 아닌 행
where 칼럼명 is not NULL

IS NULL 이 가장 흔히 쓰는 표준 문법입니다. <=> 는 MySQL의 NULL-safe 비교 연산자로, 양쪽이 모두 NULL이면 1(참), 한쪽만 NULL이면 0(거짓)을 돌려줍니다. 고정된 NULL 과 비교할 때는 IS NULL 과 결과가 같아서 굳이 쓸 일이 없지만, 진가는 두 컬럼을 비교할 때 나옵니다. 예를 들어 a.tel <=> b.tel 이라고 쓰면 둘 다 비어 있는(NULL) 경우도 같은 값으로 취급해 줍니다. = 로 비교했다면 이런 행은 조인에서 빠지죠. PHP 쪽에서 바인딩한 값이 NULL일 수도 있는 조건을 만들 때도 <=> 를 쓰면 분기 없이 한 문장으로 처리됩니다.

광고

NULL과 빈 문자열은 다르다

화면에서는 둘 다 비어 보이지만 DB에서는 다른 값입니다. '' 는 길이가 0인 문자열이라 IS NULL 에 걸리지 않고, NULL은 = '' 에 걸리지 않습니다. 그래서 한 컬럼에 둘이 섞여 있으면 "비어 있는 행"을 찾을 때마다 조건을 두 개 써야 합니다.

이럴 때 한쪽으로 정리해 두면 편합니다. 아래는 그누보드 방문자 테이블에서 URL이 NULL인 행을 빈 문자열로 바꾸는 쿼리입니다.

UPDATE `g5_visit` SET `vi_url`='' WHERE `g5_visit`.`vi_url` is NULL

한 번 정리해 두면 이후로는 vi_url = '' 조건 하나로 빈 값을 찾을 수 있습니다. 반대로 빈 문자열을 NULL로 맞추고 싶다면 SET vi_url = NULL WHERE vi_url = '' 로 하면 되고, 컬럼 정의에서 NOT NULL DEFAULT '' 를 주면 앞으로는 NULL이 아예 들어오지 않습니다.

조회 결과에서만 NULL을 다른 값으로 보여 주고 싶다면 데이터를 고치지 않고 COALESCE(vi_url, '') 나 IFNULL(vi_url, '') 를 SELECT 목록에 쓰면 됩니다. 다만 WHERE 절에서 컬럼을 이런 함수로 감싸면 그 컬럼의 인덱스를 못 타니, 검색 조건에는 IS NULL 을 그대로 쓰는 편이 빠릅니다.

NOT IN 서브쿼리에 NULL이 섞이면

NULL 때문에 가장 당황스러운 경우가 NOT IN 입니다. WHERE id NOT IN (SELECT ref_id FROM 다른테이블) 에서 ref_id 에 NULL이 하나라도 있으면 결과가 통째로 0건이 됩니다. id <> NULL 비교가 참이 될 수 없어서 모든 행이 걸러지기 때문입니다. 서브쿼리 쪽에 WHERE ref_id IS NOT NULL 을 붙이거나 NOT EXISTS 로 바꾸면 의도대로 나옵니다.

집계와 정렬에서의 NULL

집계 함수도 NULL을 건너뜁니다. COUNT(*) 는 모든 행을 세지만 COUNT(칼럼명) 은 그 칼럼이 NULL이 아닌 행만 세고, SUM·AVG 도 NULL 행은 빼고 계산합니다. 그래서 NULL이 몇 건인지 정리 전에 확인하고 싶을 때는 이렇게 한 번에 볼 수 있습니다.

SELECT COUNT(*) AS total, COUNT(vi_url) AS has_url, SUM(vi_url IS NULL) AS null_url
FROM g5_visit;

MySQL에서 vi_url IS NULL 은 참이면 1, 거짓이면 0이라 그대로 더하면 개수가 됩니다. 정렬할 때 NULL은 오름차순에서 맨 앞, 내림차순에서 맨 뒤로 갑니다. 날짜 칼럼에 NULL이 섞여 있는데 ORDER BY 날짜 ASC 로 정렬하면 빈 날짜 행이 목록 맨 위에 몰려 보이는 이유가 이것입니다. 이 행들을 뒤로 보내고 싶다면 ORDER BY 날짜 IS NULL, 날짜 처럼 NULL 여부를 먼저 정렬 기준으로 주면 됩니다.