NULL 함정 모음 - 집계·비교·NOT IN

이 장에서 배우는 것

NULL 은 "값이 없음"을 나타내는 상태이지 숫자 0 이나 빈 문자열이 아니다. 이 차이를 놓치면 비교 연산, 집계 함수, 서브쿼리에서 예상과 다른 결과를 얻는다. 특히 NOT IN 서브쿼리에 NULL 이 하나만 섞여도 전체 결과가 사라지는 현상은 실무에서 반복적으로 발생하는 사고 유형이다. 이 장에서는 온라인 서점 스키마의 리뷰·주문상세 데이터를 이용해 NULL 이 만드는 함정을 하나씩 재현하고 고친다.

• NULL 비교 연산의 결과가 TRUE·FALSE·UNKNOWN 중 무엇인지 판단한다
• COUNT(*) 와 COUNT(컬럼), AVG 가 NULL 을 어떻게 다르게 처리하는지 구분한다
• NOT IN 서브쿼리에 NULL 이 섞였을 때 결과가 전부 사라지는 원인을 설명하고 NOT EXISTS 로 고친다
• COALESCE 로 NULL 을 계산 가능한 값으로 치환한다
• MySQL 과 Oracle 에서 정렬 시 NULL 이 놓이는 위치 차이를 구분한다

문제 상황

서점 마케팅팀이 "SQL 실전 노트(book_id=2)를 아직 리뷰하지 않은 회원에게 쿠폰을 발송한다"는 캠페인을 준비했다. 담당자는 리뷰 테이블에서 해당 도서를 리뷰한 회원 목록을 뽑아 NOT IN 서브쿼리로 나머지 회원을 걸러내는 SQL 을 짰다. 그런데 결과가 0건이었다. 회원은 5명이고 리뷰를 남긴 회원은 1명뿐인데도 쿠폰 대상자가 한 명도 나오지 않은 것이다. 원인은 이 서점이 비회원 구매 후 리뷰 작성을 허용하면서 리뷰의 member_id 가 NULL 인 행이 생겼기 때문이다. 이런 사고는 비교 연산, 집계 함수, 정렬에서도 형태만 다르게 반복된다.

NULL 비교와 세 값 논리

SQL 의 비교 연산은 TRUE, FALSE 두 값이 아니라 TRUE, FALSE, UNKNOWN 세 값 논리(three-valued logic)로 동작한다. NULL 은 "알 수 없는 값"이므로 어떤 값과 비교해도 결과는 UNKNOWN 이 된다. WHERE 절은 UNKNOWN 을 FALSE 와 동일하게 취급해 해당 행을 결과에서 제외한다. 회원 테이블에서 전화번호가 없는 회원은 phone = NULL 로도, phone <> '010-1111-2222' 로도 걸러지지 않는다. NULL 여부를 확인하려면 반드시 IS NULL, IS NOT NULL 을 써야 한다.

같은 이유로 계산식에 NULL 이 섞이면 결과 전체가 NULL 이 된다. 주문상세의 discount_amount 가 NULL 인 행은 price - discount_amount 를 그대로 계산하면 결제 금액도 NULL 이 되어 버린다. 이때는 COALESCE(discount_amount, 0) 처럼 NULL 을 특정 값으로 치환한 뒤 계산해야 한다. 표준 SQL 과 MySQL 은 COALESCE 를 쓰고, Oracle 은 같은 목적의 NVL(discount_amount, 0) 함수를 함께 제공한다. 두 함수는 인수 형태만 다를 뿐 첫 번째 NULL 아닌 값을 반환한다는 점은 같다.

집계 함수와 NULL: COUNT · AVG

COUNT(*) 는 NULL 여부와 상관없이 행 수를 그대로 센다. 반면 COUNT(컬럼) 은 해당 컬럼이 NULL 이 아닌 행만 센다. 리뷰 테이블에서 별점 없이 코멘트만 남긴 리뷰가 있으면 두 값이 달라진다. AVG, SUM, MIN, MAX 같은 집계 함수도 NULL 행을 계산에서 자동으로 제외하므로, 평균 별점을 "리뷰수 대비"로 오해하면 실제보다 후한 점수로 읽을 수 있다.

리뷰 집계 시 NULL 별점이 COUNT · AVG 결과에 미치는 영향

book_id	COUNT(*)	COUNT(rating)	AVG(rating)
1	2	1	5.0000
2	2	2	4.5000
5	1	1	4.0000

book_id 1 은 리뷰가 2건이지만 그중 1건은 별점이 비어 있어 COUNT(rating) 은 1 이고 AVG(rating) 은 남은 한 건의 점수인 5.0000 으로 계산된다. "리뷰수 2건에 평균 5점"이라고 그대로 보고하면 별점을 매긴 사람이 1명뿐이라는 사실이 감춰진다. NULL 처리 규칙은 MySQL 공식 문서에도 정리되어 있다.

NOT IN 서브쿼리와 NULL

NOT IN (서브쿼리) 는 내부적으로 서브쿼리 결과의 각 값과 <> 비교를 AND 로 연결한 것과 같다. 서브쿼리 결과에 NULL 이 하나라도 있으면 그 값과의 비교가 UNKNOWN 이 되고, AND 로 연결된 전체 조건도 UNKNOWN 이 되어 모든 행이 결과에서 빠진다. 리뷰 테이블은 비회원 리뷰를 허용해 member_id 가 NULL 인 행이 존재하므로, 이 컬럼을 그대로 NOT IN 서브쿼리에 사용하면 결과가 통째로 사라진다.

이 문제는 두 가지 방법으로 고칠 수 있다. 서브쿼리에 WHERE member_id IS NOT NULL 을 추가해 NULL 을 애초에 제거하거나, NOT IN 대신 NOT EXISTS 를 쓰는 것이다. NOT EXISTS 는 행 단위 존재 여부만 확인하므로 서브쿼리 결과에 NULL 이 섞여 있어도 영향을 받지 않는다. 실무에서는 서브쿼리 대상 컬럼이 NULL 을 허용하는지 확신할 수 없는 경우가 많으므로, 습관적으로 NOT EXISTS 를 우선 고려하는 편이 안전하다.

완성 코드

-- 스키마: 회원, 도서, 주문, 주문상세, 리뷰
CREATE TABLE 회원 (
  member_id INT PRIMARY KEY,
  name VARCHAR(30) NOT NULL,
  phone VARCHAR(20)
);

CREATE TABLE 도서 (
  book_id INT PRIMARY KEY,
  title VARCHAR(50) NOT NULL,
  price INT NOT NULL,
  category VARCHAR(20)
);

CREATE TABLE 주문 (
  order_id INT PRIMARY KEY,
  member_id INT NOT NULL,
  order_date DATE NOT NULL,
  FOREIGN KEY (member_id) REFERENCES 회원(member_id)
);

CREATE TABLE 주문상세 (
  order_id INT,
  book_id INT,
  quantity INT NOT NULL,
  discount_amount INT,
  PRIMARY KEY (order_id, book_id),
  FOREIGN KEY (order_id) REFERENCES 주문(order_id),
  FOREIGN KEY (book_id) REFERENCES 도서(book_id)
);

CREATE TABLE 리뷰 (
  review_id INT PRIMARY KEY,
  book_id INT NOT NULL,
  member_id INT,
  rating INT,
  comment VARCHAR(200),
  FOREIGN KEY (book_id) REFERENCES 도서(book_id),
  FOREIGN KEY (member_id) REFERENCES 회원(member_id)
);

INSERT INTO 회원 (member_id, name, phone) VALUES
  (1, '박서연', '010-1111-2222'),
  (2, '이준호', NULL),
  (3, '최유나', '010-3333-4444'),
  (4, '정민석', NULL),
  (5, '한소희', '010-5555-6666');

INSERT INTO 도서 (book_id, title, price, category) VALUES
  (1, '데이터 모델링 입문', 28000, '컴퓨터'),
  (2, 'SQL 실전 노트', 25000, '컴퓨터'),
  (3, '가벼운 산문집', 15000, '에세이'),
  (4, '경제 읽는 습관', 19000, '경제'),
  (5, 'SQLD 기출 문제집', 32000, '컴퓨터');

INSERT INTO 주문 (order_id, member_id, order_date) VALUES
  (101, 1, '2026-06-01'),
  (102, 2, '2026-06-03'),
  (103, 3, '2026-06-05'),
  (104, 1, '2026-06-10'),
  (105, 4, '2026-06-12');

INSERT INTO 주문상세 (order_id, book_id, quantity, discount_amount) VALUES
  (101, 1, 1, 2000),
  (101, 2, 1, NULL),
  (102, 2, 2, 1000),
  (103, 5, 1, NULL),
  (104, 3, 1, NULL),
  (105, 4, 1, 1500);

INSERT INTO 리뷰 (review_id, book_id, member_id, rating, comment) VALUES
  (1, 1, 1, 5, '입문서로 좋았다'),
  (2, 1, 3, NULL, '그림이 많아 이해가 쉬웠다'),
  (3, 2, 2, 4, '실무 예제가 많다'),
  (4, 2, NULL, 5, '비회원 구매 후 작성'),
  (5, 5, 1, 4, '기출 유형이 잘 정리됨');

-- ① NULL 비교: = 와 IS NULL 의 차이
SELECT member_id, name, phone
FROM 회원
WHERE phone = '010-1111-2222';

SELECT member_id, name, phone
FROM 회원
WHERE phone IS NULL;

-- ② COUNT(*) 와 COUNT(컬럼), AVG 의 NULL 처리
SELECT book_id,
       COUNT(*)     AS 리뷰수,
       COUNT(rating) AS 별점입력수,
       AVG(rating)   AS 평균별점
FROM 리뷰
GROUP BY book_id
ORDER BY book_id;

-- ③ NOT IN 함정: 서브쿼리에 NULL 이 섞이면 결과가 사라진다
SELECT member_id, name
FROM 회원
WHERE member_id NOT IN (
  SELECT member_id FROM 리뷰 WHERE book_id = 2
);

-- ④ NOT EXISTS 로 고친 버전
SELECT m.member_id, m.name
FROM 회원 m
WHERE NOT EXISTS (
  SELECT 1 FROM 리뷰 r WHERE r.book_id = 2 AND r.member_id = m.member_id
)
ORDER BY m.member_id;

-- ⑤ COALESCE 로 할인 금액의 NULL 을 0 으로 치환해 결제 금액 계산
SELECT d.order_id, d.book_id, d.quantity,
       d.discount_amount,
       (b.price * d.quantity) - COALESCE(d.discount_amount, 0) AS 결제금액
FROM 주문상세 d
JOIN 도서 b ON b.book_id = d.book_id
ORDER BY d.order_id, d.book_id;

-- ⑥ 정렬 시 NULL 위치: MySQL 은 오름차순에서 NULL 을 가장 앞에 둔다
SELECT order_id, book_id, discount_amount
FROM 주문상세
ORDER BY discount_amount, order_id;

줄별 해설

① 첫 번째 SELECT 는 phone = '010-1111-2222' 조건으로 전화번호가 정확히 일치하는 회원만 찾는다. 두 번째 SELECT 는 IS NULL 로 전화번호가 없는 회원을 찾는다. phone = NULL 을 썼다면 두 쿼리 모두 원하는 결과를 얻지 못했을 것이다.

② COUNT(*) 는 그룹의 행 수를, COUNT(rating) 은 rating 이 NULL 이 아닌 행 수를 센다. 두 값의 차이가 곧 "별점을 남기지 않은 리뷰 수"다. AVG(rating) 은 NULL 행을 분모에서 제외하고 계산한다.

③ book_id 2 에 대한 리뷰의 member_id 는 {2, NULL} 이다. NOT IN 은 이 리스트의 모든 값과 <> 비교를 AND 로 묶으므로, NULL 과의 비교가 UNKNOWN 이 되어 전체 조건이 UNKNOWN 이 되고 결과는 0건이다.

④ NOT EXISTS 는 상관 서브쿼리로 회원별 리뷰 존재 여부만 확인한다. member_id 가 NULL 인 리뷰 행은 애초에 r.member_id = m.member_id 조건에서 일치 대상이 되지 못하므로 결과에 영향을 주지 않는다.

⑤ discount_amount 가 NULL 인 행은 할인이 없다는 뜻이므로 COALESCE(d.discount_amount, 0) 로 0 을 대신 사용해 결제 금액을 계산한다. COALESCE 없이 뺄셈만 했다면 해당 행의 결제 금액이 NULL 로 나온다.

⑥ discount_amount 로 오름차순 정렬하면 MySQL 은 NULL 을 가장 작은 값으로 취급해 맨 앞에 배치한다. 같은 discount_amount 값 안에서는 order_id 로 다시 정렬해 결과 순서를 고정했다.

실행 결과

mysql> SELECT member_id, name, phone FROM 회원 WHERE phone = '010-1111-2222';
+-----------+--------+----------------+
| member_id | name   | phone          |
+-----------+--------+----------------+
|         1 | 박서연 | 010-1111-2222  |
+-----------+--------+----------------+
1 row in set (0.00 sec)

mysql> SELECT member_id, name, phone FROM 회원 WHERE phone IS NULL;
+-----------+--------+-------+
| member_id | name   | phone |
+-----------+--------+-------+
|         2 | 이준호 | NULL  |
|         4 | 정민석 | NULL  |
+-----------+--------+-------+
2 rows in set (0.00 sec)

mysql> SELECT book_id, COUNT(*) AS 리뷰수, COUNT(rating) AS 별점입력수, AVG(rating) AS 평균별점
    -> FROM 리뷰 GROUP BY book_id ORDER BY book_id;
+---------+--------+------------+-----------+
| book_id | 리뷰수 | 별점입력수 | 평균별점  |
+---------+--------+------------+-----------+
|       1 |      2 |          1 |   5.0000  |
|       2 |      2 |          2 |   4.5000  |
|       5 |      1 |          1 |   4.0000  |
+---------+--------+------------+-----------+
3 rows in set (0.00 sec)

mysql> SELECT member_id, name FROM 회원
    -> WHERE member_id NOT IN (SELECT member_id FROM 리뷰 WHERE book_id = 2);
Empty set (0.00 sec)

mysql> SELECT m.member_id, m.name FROM 회원 m
    -> WHERE NOT EXISTS (SELECT 1 FROM 리뷰 r WHERE r.book_id = 2 AND r.member_id = m.member_id)
    -> ORDER BY m.member_id;
+-----------+--------+
| member_id | name   |
+-----------+--------+
|         1 | 박서연 |
|         3 | 최유나 |
|         4 | 정민석 |
|         5 | 한소희 |
+-----------+--------+
4 rows in set (0.00 sec)

mysql> SELECT order_id, book_id, discount_amount FROM 주문상세
    -> ORDER BY discount_amount, order_id;
+----------+---------+-----------------+
| order_id | book_id | discount_amount |
+----------+---------+-----------------+
|      101 |       2 |            NULL |
|      103 |       5 |            NULL |
|      104 |       3 |            NULL |
|      102 |       2 |            1000 |
|      105 |       4 |            1500 |
|      101 |       1 |            2000 |
+----------+---------+-----------------+
6 rows in set (0.00 sec)

실무에서 자주 틀리는 것

NULL 을 = 으로 비교하기

-- 틀린 코드: 전화번호가 없는 회원을 찾으려 했지만 항상 0건이다
SELECT * FROM 회원 WHERE phone = NULL;

-- 고친 코드
SELECT * FROM 회원 WHERE phone IS NULL;

NOT IN 서브쿼리에 NULL 걸러내지 않기

-- 틀린 코드: 리뷰의 member_id 에 NULL 이 있으면 결과가 통째로 사라진다
SELECT * FROM 회원
WHERE member_id NOT IN (SELECT member_id FROM 리뷰 WHERE book_id = 2);

-- 고친 코드 1: NULL 을 서브쿼리에서 제거
SELECT * FROM 회원
WHERE member_id NOT IN (
  SELECT member_id FROM 리뷰 WHERE book_id = 2 AND member_id IS NOT NULL
);

-- 고친 코드 2: NOT EXISTS 사용
SELECT m.* FROM 회원 m
WHERE NOT EXISTS (
  SELECT 1 FROM 리뷰 r WHERE r.book_id = 2 AND r.member_id = m.member_id
);

COUNT(*) 와 COUNT(컬럼) 을 같은 값으로 착각하기

-- 틀린 코드: 리뷰수를 별점 입력수로 오해해 보고
SELECT book_id, COUNT(*) AS 별점입력수 FROM 리뷰 GROUP BY book_id;

-- 고친 코드: 별점이 실제로 입력된 행만 센다
SELECT book_id, COUNT(rating) AS 별점입력수 FROM 리뷰 GROUP BY book_id;

정렬 시 NULL 위치를 DBMS 마다 같다고 가정하기

같은 ORDER BY discount_amount 라도 MySQL 과 Oracle 은 NULL 을 다른 위치에 놓는다. 두 DBMS 를 오가며 작업할 때는 기본 동작에 기대지 말고 위치를 명시하는 편이 안전하다.

ORDER BY 오름차순 정렬에서 NULL 이 놓이는 위치 비교

DBMS	기본 오름차순(ASC)	기본 내림차순(DESC)	위치를 강제하는 방법
MySQL 8	NULL 이 맨 앞	NULL 이 맨 뒤	ORDER BY (컬럼 IS NULL), 컬럼
Oracle	NULL 이 맨 뒤	NULL 이 맨 앞	ORDER BY 컬럼 NULLS FIRST / NULLS LAST

-- MySQL 에서 discount_amount 가 NULL 인 행을 맨 뒤로 보내고 싶을 때
SELECT order_id, book_id, discount_amount
FROM 주문상세
ORDER BY (discount_amount IS NULL), discount_amount;

-- Oracle 이라면 같은 목적을 NULLS LAST 로 표현한다
-- ORDER BY discount_amount NULLS LAST

한눈에 보기

NULL 관련 함정과 대응 방법 요약

상황	함정	대응
비교 연산자	= NULL, <> NULL 은 항상 UNKNOWN	IS NULL / IS NOT NULL 사용
COUNT	COUNT(*) 와 COUNT(컬럼) 값이 다름	NULL 제외 여부를 먼저 확인
AVG · SUM	NULL 행은 집계에서 자동 제외	COALESCE 로 기본값을 명시할지 결정
NOT IN	서브쿼리 결과에 NULL 이 있으면 전체가 0건	NOT EXISTS 사용 또는 IS NOT NULL 로 필터링

연습 문제

1. 회원 테이블에서 전화번호가 '010-3333-4444' 가 아닌 회원을 WHERE phone <> '010-3333-4444' 로 조회했다. 전화번호가 NULL 인 이준호, 정민석 회원이 결과에 포함되지 않는 이유를 설명하라.
2. 리뷰 테이블에서 도서별로 "별점을 입력하지 않은 리뷰 수"를 구하는 SQL 을 COUNT(*) 와 COUNT(rating) 을 이용해 작성하라.
3. 주문상세를 discount_amount 로 오름차순 정렬했을 때 MySQL 과 Oracle 에서 첫 번째로 나오는 행의 discount_amount 값이 다른 이유를 설명하라.
4. WHERE member_id NOT IN (SELECT member_id FROM 리뷰 WHERE book_id = 2) 가 0건을 반환하는 이유를 서술하고, NOT EXISTS 를 이용한 대체 쿼리를 작성하라.

정답과 해설

1. <> 비교도 NULL 과 만나면 UNKNOWN 이 된다. phone 이 NULL 인 두 회원의 phone <> '010-3333-4444' 는 UNKNOWN 으로 평가되어 WHERE 절이 두 행을 제외한다. NULL 을 포함하려면 WHERE phone <> '010-3333-4444' OR phone IS NULL 처럼 조건을 추가해야 한다.

2.

SELECT book_id, COUNT(*) - COUNT(rating) AS 별점미입력수
FROM 리뷰
GROUP BY book_id;

COUNT(*) 는 전체 리뷰 수, COUNT(rating) 은 별점이 입력된 리뷰 수이므로 그 차이가 별점 없이 등록된 리뷰 수가 된다.

3. MySQL 은 오름차순 정렬에서 NULL 을 가장 작은 값으로 취급해 맨 앞에 놓는다. Oracle 은 오름차순 정렬의 기본값이 NULLS LAST 라서 NULL 이 맨 뒤로 간다. 같은 정렬 방향이라도 DBMS 기본 규칙이 반대이므로 결과의 첫 행이 다르게 나온다.

4. book_id 2 리뷰의 member_id 목록은 {2, NULL} 이다. NOT IN 은 이 목록의 모든 값과 <> 비교를 AND 로 묶는데, NULL 과의 비교가 UNKNOWN 이 되어 전체 조건이 UNKNOWN 으로 평가되고 모든 행이 걸러진다. 대체 쿼리는 다음과 같다.

SELECT m.member_id, m.name
FROM 회원 m
WHERE NOT EXISTS (
  SELECT 1 FROM 리뷰 r WHERE r.book_id = 2 AND r.member_id = m.member_id
);