Oracle AVG(NVL())을 썼는데 왜 NULL이 나올까?

Reading time ~2 minutes

Oracle에서 평균을 구하다가 예상하지 못한 결과를 만났습니다.

NULL을 0으로 바꾸려고 NVL까지 넣었는데도 결과가 NULL이었습니다.

SELECT AVG(NVL(score, 0))
FROM student_score
WHERE class_id = 999;

처음에는 이런 생각이 들었습니다.

NVL(score, 0)을 했는데 왜 NULL이 나오지?

score가 NULL이면 0으로 바뀔 테니 AVG 결과도 적어도 0이 나올 것 같았습니다.

그런데 여기에는 한 가지 놓치기 쉬운 경우가 있습니다.

NULL인 값이 있는 것과 조회된 행 자체가 없는 것은 다릅니다.

값이 NULL인 경우

조건에 맞는 데이터가 세 건 있고 score가 모두 NULL이라고 해보겠습니다.

이 상태에서

SELECT AVG(NVL(score, 0))
FROM student_score;

NVL이 각 NULL을 0으로 바꾸기 때문에 실제 계산은 다음과 같습니다.

AVG(0, 0, 0) = 0

결과는 예상대로 0입니다.

SELECT AVG(NVL(score, 0))
FROM student_score
WHERE class_id = 999;

class_id = 999인 행이 한 건도 없다고 해보겠습니다.

이때는 score가 NULL인 게 아닙니다. NVL을 적용할 행 자체가 없습니다.

NVL(score, 0)은 존재하는 행의 score가 NULL일 때 0으로 바꿀 뿐, 없는 행을 만들어주지는 않습니다. 결국 AVG가 계산할 값이 없으므로 결과는 NULL입니다.

flowchart TD; A[WHERE 조건으로 조회] --> B{행이 있는가?}; B -->|있음| C[NVL로 NULL을 0으로 변경]; C --> D[AVG 계산]; B -->|없음| E[NVL을 적용할 값이 없음]; E --> F[AVG 결과 NULL];

AVG(NVL(score, 0))에서 안쪽의 NVL이 처리하는 대상은 조회된 행 안의 NULL 값입니다. 조회된 행이 없는 경우까지 처리하는 것은 아닙니다.

집계 결과에도 NVL을 적용하면 됩니다.

SELECT NVL(AVG(NVL(score, 0)), 0)
FROM student_score
WHERE class_id = 999;

두 NVL의 역할은 다릅니다.

NVL(score, 0)    -> 행에 있는 NULL 값을 0으로 변경
AVG(...)         -> 평균 계산
NVL(AVG(...), 0) -> AVG 결과가 NULL이면 0으로 변경

AVG, SUM, MIN, MAX는 대상 행이 없을 때 NULL을 반환할 수 있지만 COUNT(*)는 0을 반환합니다.

SELECT COUNT(*)
FROM student_score
WHERE class_id = 999;

집계 쿼리를 작성할 때는 컬럼에 NULL이 들어올 수 있는지만 볼 게 아니라 조건에 맞는 행이 아예 없을 수도 있는지도 같이 확인해야 합니다.

AVG(NVL(score, 0))은 조회된 행의 NULL 값을 처리합니다. 조회 결과가 0건이면 NVL을 적용할 대상도 없고 AVG 결과는 NULL입니다.

이 경우까지 0으로 처리하려면 집계 결과에도 NVL을 적용합니다.

NVL(AVG(NVL(score, 0)), 0)

결국 기억할 것은 하나입니다.

NULL인 값과 행이 없는 것은 다르다.

NVL을 썼는데도 집계 결과가 NULL이라면, 컬럼 값만 보지 말고 먼저 조회된 행이 실제로 있는지 확인해보면 됩니다.