SQL COUNT(*)와 COUNT(컬럼)은 NULL에서 왜 다를까?

SQL에서 행 수를 셌는데 COUNT(*)와 COUNT(email)의 결과가 다르다면 NULL이 들어 있는 행부터 확인해야 한다. COUNT(*)는 조건을 통과한 행을 세고, COUNT(컬럼)은 그중 해당 표현식이 NULL이 아닌 행만 센다.

이 차이는 단순 문법이 아니라 지표의 의미를 바꾼다. 전체 가입자 수를 구할 때 이메일 컬럼을 세면 이메일이 비어 있는 회원이 제외된다. 반대로 이메일을 등록한 회원 수가 목적이라면 COUNT(email)이 정확한 식이다.

먼저 같은 데이터로 결과를 비교하기

다음 회원 테이블에는 네 행이 있고, 이메일이 없는 회원이 두 명 있다.

CREATE TABLE members (
    id INTEGER PRIMARY KEY,
    team TEXT NOT NULL,
    email TEXT
);

INSERT INTO members (id, team, email) VALUES
    (1, 'backend', 'a@example.com'),
    (2, 'backend', NULL),
    (3, 'frontend', 'c@example.com'),
    (4, 'frontend', NULL);

두 집계를 한 쿼리에서 실행하면 차이가 바로 보인다.

SELECT
    COUNT(*) AS all_rows,
    COUNT(email) AS email_rows
FROM members;

all_rows | email_rows
---------+-----------
4        | 2

COUNT(*)에는 네 행이 모두 들어간다. COUNT(email)은 이메일 값이 있는 두 행만 센다. 빈 문자열은 NULL이 아니므로 값이 비어 보이더라도 집계에 포함된다. 빈 문자열까지 제외해야 한다면 저장 규칙을 정하거나 조건을 따로 써야 한다.

GROUP BY에서는 차이가 더 잘 드러난다

팀별 전체 회원 수와 이메일 등록 회원 수를 함께 보면 집계의 의미를 구분하기 쉽다.

SELECT
    team,
    COUNT(*) AS member_count,
    COUNT(email) AS email_count,
    COUNT(*) - COUNT(email) AS missing_email_count
FROM members
GROUP BY team
ORDER BY team;

 

team     | member_count | email_count | missing_email_count
---------+--------------+-------------+--------------------
backend  | 2            | 1           | 1
frontend | 2            | 1           | 1

COUNT(*) - COUNT(email)은 해당 그룹에서 이메일이 NULL인 행 수가 된다. 다만 조인 뒤에는 행이 중복될 수 있으므로, 이 식을 쓰기 전에 조인 결과의 한 행이 실제로 무엇을 뜻하는지 먼저 확인해야 한다.

특히 일대다 조인에서는 회원 한 명이 주문 수만큼 반복될 수 있다. 회원 수가 목적이라면 COUNT(DISTINCT member_id)처럼 기준 키를 명시하는 편이 안전하다.

COUNT(DISTINCT 컬럼)도 NULL은 제외한다

중복을 제거해 고유한 이메일 수를 구하려면 DISTINCT를 집계 안에 넣는다.

SELECT
    COUNT(email) AS registered_rows,
    COUNT(DISTINCT email) AS unique_emails
FROM members;

COUNT(DISTINCT email)은 중복 이메일을 하나로 계산하고 NULL은 제외한다. 전체 행의 중복을 제거한다는 뜻이 아니므로, 여러 컬럼 조합을 고유 기준으로 삼아야 한다면 DBMS가 지원하는 문법이나 서브쿼리로 기준 행을 먼저 만든다.

또한 COUNT(1)을 COUNT(*)보다 빠른 별도 문법으로 볼 필요는 없다. 전체 행 수라는 의도를 드러낼 때는 COUNT(*)가 가장 분명하다. 성능은 실제 실행 계획과 인덱스, 테이블 통계로 확인해야 한다.

조건별 개수는 조건을 집계에 드러내기

PostgreSQL처럼 FILTER를 지원하는 DBMS에서는 조건별 행 수를 한 번에 계산할 수 있다.

SELECT
    COUNT(*) AS all_rows,
    COUNT(*) FILTER (WHERE email IS NOT NULL) AS has_email,
    COUNT(*) FILTER (WHERE email IS NULL) AS no_email
FROM members;

FILTER를 사용할 수 없는 환경이라면 CASE 식으로 같은 의도를 표현할 수 있다.

SELECT
    COUNT(*) AS all_rows,
    SUM(CASE WHEN email IS NOT NULL THEN 1 ELSE 0 END) AS has_email,
    SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) AS no_email
FROM members;

조건부 집계를 여러 개 나란히 둘 때는 각 조건이 겹치는지 확인한다. IS NULL과 IS NOT NULL처럼 서로 배타적인 조건은 합계가 전체 행 수와 같아야 하므로 검증하기 쉽다.

어떤 COUNT를 선택할까?

전체 행 수가 필요하면 COUNT(*), 특정 값이 채워진 행 수가 필요하면 COUNT(컬럼), 고유한 비결측 값 수가 필요하면 COUNT(DISTINCT 컬럼)을 사용한다. 쿼리를 읽는 사람이 집계 기준을 바로 알 수 있도록 별칭에도 member_count, email_count처럼 의미를 적는다.

결과가 예상보다 작다면 먼저 NULL과 빈 문자열을 구분하고, 그다음 WHERE 조건과 조인 중복을 확인한다. 같은 COUNT라도 무엇을 세는지 명확히 정해야 지표가 흔들리지 않는다.