COUNT(column)으로 셌더니 전체 행보다 적게 나온 이유

문제 발생

글마다 댓글 수를 세는 쿼리에서, 댓글이 하나도 없는 글의 개수가 1로 나왔습니다.

SELECT p.id, COUNT(*) AS comment_count
FROM dev_posts p
LEFT JOIN dev_comments c ON c.dev_post_id = p.id
GROUP BY p.id;

반대로 다른 쿼리에서는 전체 사용자 수를 세려고 컬럼을 넣었더니 실제보다 적게 나왔습니다.

SELECT COUNT(phone) FROM users;   -- 전화번호가 없는 사용자가 빠진다

원인 분석

두 형태는 세는 대상이 다릅니다.

첫 번째 쿼리가 틀린 이유가 여기 있습니다. LEFT JOIN은 짝이 없어도 왼쪽 행을 남기고 오른쪽 컬럼을 NULL로 채웁니다. 그 행도 행이기는 하므로 COUNT(*)는 1로 셉니다. 세고 싶은 것은 "실제로 붙은 댓글"이므로 오른쪽 테이블의 컬럼을 세야 합니다.

두 번째는 반대 방향의 실수입니다. phone이 NULL인 사용자는 COUNT(phone)에서 빠집니다.

빈 결과에서의 반환값도 함수마다 다릅니다.

• COUNT(...)는 0을 돌려줍니다.
• SUM(...), AVG(...)는 NULL을 돌려줍니다.

집계 함수는 별도 언급이 없는 한 NULL을 무시한다는 것이 공통 규칙입니다.

해결 방안

1. LEFT JOIN 뒤에는 오른쪽 컬럼을 셉니다.

SELECT p.id, COUNT(c.id) AS comment_count
FROM dev_posts p
LEFT JOIN dev_comments c ON c.dev_post_id = p.id
GROUP BY p.id;

2. 전체 행 수는 COUNT(*)로 셉니다. 컬럼 하나를 골라 넣는 습관은 그 컬럼이 나중에 nullable이 되는 순간 조용히 틀립니다.
3. 조건부 집계에 이 성질을 활용합니다. CASE가 NULL을 돌려주면 세지 않으므로, 한 번의 스캔으로 여러 개수를 낼 수 있습니다.

SELECT
  COUNT(*) AS total,
  COUNT(CASE WHEN status = 'published' THEN 1 END) AS published,
  COUNT(CASE WHEN deleted_at IS NOT NULL THEN 1 END) AS deleted
FROM dev_posts;

4. SUM의 NULL을 감쌉니다. 합계를 화면에 그대로 쓰면 결과가 없을 때 NULL이 나옵니다.

SELECT COALESCE(SUM(amount), 0) FROM orders WHERE user_id = ?;

5. 중복이 생기는 조인에서는 COUNT(DISTINCT ...)를 씁니다. 여러 테이블을 조인하면 행이 곱해져서 단순 카운트가 부풀려집니다. 다만 DISTINCT는 비용이 있으므로, 조인을 서브쿼리로 분리하는 편이 나은 경우도 많습니다.

공식 문서

• MySQL: 집계 함수 — COUNT()
• MariaDB: COUNT()
• MySQL: NULL 값 다루기