NULL 처리 시 유의 사항 및 항상 헷갈리는 내용 총정리

 

출처 : https://dataonair.or.kr/db-tech-reference/d-lounge/expert-column/?mod=document&uid=52319

[Oracle] 오라클 NULL 사용시 주의사항 정리

 

개발자에게 NULL은 쉬우면서도 어려운 존재다. 경험이 많은 개발자라도 NULL의 예상치 못한 결과에 당황하는 경우가 간혹 있다. 또한 NULL은 성능에도 영향을 미치며 잘못된 결과값을 리턴 하기도 한다.

오라클에서 NULL은 길이가 0인 문자열과 동일한 의미를 가지거나, 알 수 없는 미지의 값을 의미 하기도 한다. 이러한 NULL은 프로젝트에서 의도하든 않든 간에 많은 문제를 야기하기도 한다. 특히 신입 개발자들은 NULL에 대해 두려움이 더 클 것이다.

NULL 문제를 극복하는 쉬운 방법은 없다. NULL과 싸워 이기든지 회피하든지 둘 중 하나다. 어차피 정면 돌파해야겠다고 마음먹었다면, 하루라도 빨리 돌파하자. 부딪치고 실수하고 상처받고 그렇게라도 하여 습득하자.

고참 개발자가 되었을까지도 NULL로 고생한다면 더 민망하고 아픈 결과가 기다릴 뿐이다. 이번 연재에서는 다양한 경우에 있어서의 NULL에 대한 특별한 규칙을 이해할 수 있도록 소개한다. 개발자로서 일하는 여러분들은 항상 이기는 전투를 할 수 있다. 앞으로 NULL과의 전투에서 항상 승리하기를 기원하면서 이야기를 풀어나간다.

사칙연산에서의 NULL

수식에서의 NULL 계산 결과는 개발자들을 헷갈리게 하는 대표적인 경우다. 많은 개발자는 NULL에 3을 더하면 3이 될 거라고 예상한다. 하지만 결과값은 NULL이다. 알 수 없는 값에 3을 더하면 알 수 없는 값일 뿐이다.

위 수식에서와 같이 사칙연산에서 NULL 계산 결과는 항상 NULL이다. 알 수 없는 값 NULL에 어떤 값을 더하거나 빼거나 곱하거나 나누거나 사칙연산의 계산 결과는 무조건 NULL이 됨을 이해하자.

비교연산에서의 NULL

비교연산자(=, >, >=, <, <=, <>, !=) 사용 시 NULL은 어떤 비교 결과값을 리턴할까

---------------------------------------
번호 기준금액 목표금액
---------------------------------------
1 100 200
2 100 NULL
3 NULL 200
4 NULL NULL
---------------------------------------

알 수 없는 값인 NULL은 어떤 비교를 하든 그 결과값은 NULL(알 수 없는 값)이다. 아래 쿼리 결과를 보면서 비교연산에서의 NULL의 의미를 확인해 보자.

위 쿼리 결과를 보면 NULL과의 비교는 무의미함을 알 수 있다. 심지어 NULL과 NULL의 비교에서도 그 결과값은 (같다, 다르다)가 아니라 알 수 없는 값임을 이해해야 한다.

집계함수에서의 NULL

집계함수 SUM, AVG, MAX, MIN에서 NULL은 집계 결과에 어떤 영향을 미칠까

---------------------------
번호 주문금액
---------------------------
1 200
2 400
3 NULL → 집계함수에서 NULL 값이 있는 레코드는 제외하고 계산한다.
---------------------------

위의 주문금액 컬럼에 NULL 값이 일부 존재한다. 이때 SUM(주문금액)의 값은 어떻게 나올까 아마 대부분의 개발자들은 SUM(주문금액) = 2 + 4 + NULL과 동일하게 인식한다. 따라서 결과값이 NULL이라고 생각할 것이다. 그러나 실제 결과값은 6이다. NULL인 레코드는 제외하고 집계 결과를 구했기 때문이다. 집계함수에 따라 쿼리 결과가 어떻게 나오는지 살펴보자.

솔직히 오랜 개발 경험이 있는 필자도 이런 상황을 이해하기 쉽지 않다. 알 수 없는 값들을 SUM 하는데 어떻게 6이 나오는지, 알 수 없는 값과 비교하는데 어떻게 가장 큰 값을 알 수 있으며 가장 작은 값을 알 수 있는지, 알 수 없는 값들의 평균값을 어떻게 구할 수 있는지 도무지 이해가 되지 않는다.

아마 많은 개발자들이 이공계열의 공부를 했을 것이다. 이런 상황이 선뜻 이해되지 않을 것이다. NULL 규칙을 논리적으로 접근하면 이해하기 어려운 부분이 있다. 우리는 NULL 문제에 있어서는 논리적 측면으로 바라보지 말고 데이터베이스 고유의 규칙으로 바라 봐야 할 것 같다.
사실 수많은 레코드 중에서 극히 일부 레코드 값이 NULL이라고 해서 오라클에서 집계 처리 결과를 무조건 NULL로 리턴한다면, 개발자 입장에서는 사용자의 요구사항을 무시한 무책임한 처사라고 생각할 것이다. 아마 오라클도 이런 사정을 감안했을 것이다. 결국 값이 NULL인 일부의 레코드를 제외하고 집계하는 것으로 나름 규칙을 잡았을 것이다. 규칙은 이해의 대상이라기보다 지켜야 할 대상이다. NULL 값은 특별한 규칙을 따르고 있다는 것을 명심하자.

문자열 결합에서의 NULL

표준 SQL에서는 NULL과 문자열의 결합은 NULL이다. 알 수 없는 값인 NULL은 어떤 문자열과 결합하든지 NULL 값을 리턴한다. 이런 결과를 대부분의 개발자들은 당연하다고 생각할 것이다. 하지만 오라클의 문자열 결합에서 NULL은 특별한 규칙을 따른다. 길이가 0인 문자열과 동일하게 인식된다.

위의 문자열 결합에서 NULL 값은 알 수 없는 값이 아니라 길이가 0인 문자열로 인식됨을 알 수 있다. 이와 같이 NULL은 DB 종류에 따라서 서로 다른 규칙을 가지고 있다. 또한 동일한 DB에서도 상황에 따라 특별한 규칙을 따르는 것을 알 수 있다. 결국 이것은 개발자에게 짐이 될 것이다.

논리연산에서의 NULL

논리연산자는 AND(논리곱), OR(논리합), NOT(부정)이 있다. AND(논리곱)는 모든 조건을 만족하여야만 참이고, 어느 한 조건만 거짓이어도 거짓이다. 반면에 OR(논리합)는 모든 조건을 만족하지 않아야만 거짓이고, 어느 한 조건만 참이어도 참이다.

지금까지의 NULL 규칙을 정리하면 다음과 같다.

1. 사칙연산에서의 NULL 계산 결과는 의미가 없다. 결과값은 NULL이다.
2. 비교연산에서의 NULL 비교 결과는 의미가 없다. 결과값은 NULL이다.
3. 집계함수에서의 NULL 집계 결과는 의미가 있다. 결과값은 NULL이 아니다.
4. 문자열 결합에서의 NULL 결합 결과는 의미가 있다. 결과값은 NULL이 아니다.
5. 논리연산에서의 NULL 논리 결과는 의미가 있다/없다. 결과값은 NULL 이다/아니다.

INDEX에서의 NULL

인덱스가 있는 컬럼이라도 조건절에서 IS NULL 혹은 IS NOT NULL 구문 사용 시 인덱스를 사용하지 못한다. 해당 테이블을 FULL SCAN하게 된다. 그 이유는 인덱스는 기본적으로 NULL 정보를 보관하지 않기 때문이다.

여기 사이즈가 큰 주문 테이블이 있다. 최초에 주문 데이터가 INSERT될 때 배송일자 컬럼은 NULL 값을 가진다. 이후 배송이 완료되면 배송일자 컬럼에 CHAR(8) 값을 UPDATE한다. 대부분의 데이터는 배송이 완료되었으며, 배송일자 값이 있다. 최근에 주문이 들어온 일부 데이터는 배송 전이므로 배송일자 컬럼의 값이 NULL이다. 배송일자 컬럼에 인덱스가 존재하며, 다음과 같은 SQL 구문을 사용해 배송 못한 일부 데이터를 조회하고자 한다.

SELECT * FROM 주문 WHERE 배송일자 IS NULL

위에서 언급한 바와 같이 IS NULL 구문은 인덱스를 사용하지 못한다. 우리는 다른 해결책을 구해야 한다. 어떤 방법이 있을까

첫 번째는 NULL 회피 전략이다. 배송일자에 NULL 값을 사용하지 않고 의미 있는 다른 특별한 값을 사용하는 것이다. 예를 들어 ‘99991231’ 이라는 우리가 접해 볼 가능성이 전혀 없는 미래의 날짜를 사용하는 것이다. 플랜을 확인하면 INDEX가 사용됨을 알 수 있을 것이다. 혹시 배송일자 컬럼에 공백 사용을 검토하는 개발자도 있을 것이다. 하지만 필자는 권고하지는 않는다. 왜냐하면 날짜 함수인 TO_DATE 함수를 사용할 경우 오라클 에러(ORA-01841)가 발생하기 때문이다.

두 번째는 함수기반 인덱스(Function Based Index)을 이용하는 것이다. 컬럼에 함수까지 포함시켜서 인덱스를 생성하면 된다. 인덱스는 NULL 값을 보관하지 않지만, 함수기반 인덱스는 함수를 이용해 변환된 값을 보관하기 때문에 가능하다. 이 경우 배송일자 컬럼에 NULL 값이 있어도 되기 때문에 유용하다. 아래 SQL문에서 붉은색 부분을 함수기반 인덱스로 생성해 사용하면 된다.

SELECT * FROM 주문 WHERE NVL2(배송일자, 배송일자, ‘99991231’) = ‘99991231’

지금까지 IS NULL 구문의 경우에 대해서 두 가지의 해결책을 보았다. 만약 다음과 같이 IS NOT NULL 구문의 경우에는 어떤 해결책이 있을까

SELECT * FROM 주문 WHERE 배송일자 IS NOT NULL

주문 테이블의 전체 데이터 건수에서 배송일자 값이 IS NOT NULL인 경우가 대부분이라면, 어떠한 방법도 없음을 우리는 안다. 이 경우 인덱스가 존재하더라도 무의미하며 테이블 FULL SCAN 이 더 효율적이다. 만약 전체 데이터 건수에서 배송일자 값이 IS NOT NULL인 경우가 적다면 우리는 다음과 같이 BETWEEN 구문을 이용해 NULL 사용을 대체할 수 있다.

SELECT * FROM 주문 WHERE 배송일자 BETWEEN ‘00010101’ AND ‘99991231’

이때는 인덱스를 사용할 수 있게 된다. 조회 구간 범위가 적어서 테이블 FULL SCAN보다 인덱스를 통한 접근이 더 효율적이다.

검색에서의 NULL

SQL문 작성시 검색 조건에서 NULL과 관련해 조회하는 경우가 많다. 아래 예시는 우리가 흔히 접하는 조건절이다. 어떤 검색 조건이 올바른지 혹은 어떤 검색 조건이 틀린지 살펴 보자.

조건절에 IS NULL 혹은 IS NOT NULL 이외의 조건절은 잘못된 조건절이다. 위의 예시에서 유의할 점은 문자열 ‘NULL’ 검색 조건이다. 이 조건은 NULL 값을 조회하는 것이 아니라 문자열(‘NULL’) 값을 조회하는 것임에 유의하자.

함수에서의 NULL

NVL, NVL2 함수는 오라클에서 대표적인 NULL 관련 함수이다. 해당 컬럼의 값이 NULL이면 특정값으로 치환해야 하는 경우 사용한다. 아래는 NVL, NVL2 함수의 사용 용법이다.

가장 빈번히 사용되는 NVL 함수는 집계함수와 같이 사용시 주의해야 할 점이 있다. 예를 보자.

---------------------------
번호 주문금액
---------------------------
1 200
2 400
3 NULL -> 집계함수에서 NULL 값이 있는 레코드는 제외하고 계산한다.
---------------------------

위의 주문금액 컬럼에 NULL 값이 일부 존재한다. 집계함수 사용시 NULL인 레코드는 제외하고 집계 결과를 구한다. 이때 NVL 함수와 SUM 함수를 같이 사용한 아래 SQL문을 살펴보자.

첫 번째 SQL문은 올바르게 사용하였으나 만약 집계할 레코드가 없는 경우 NULL 값을 리턴한다. 두 번째 SQL문은 잘못된 사용법이다. 집계함수에서는 NULL 값이 있는 레코드는 제외하고 계산하기 때문에 NVL 사용의 의미가 없다. 오히려 레코드 수만큼 NVL 함수를 호출함에 따라 부하가 발생한다. 세 번째 SQL문은 올바른 사용법이다. 만약 집계할 레코드가 없더라도 0 값을 리턴한다. 이때 NVL 함수는 최종 집계 결과에 대해서 한번만 호출되었다. 당연히 세 번째 방법을 사용해야 한다.

다음으로 NVL 함수와 AVG 함수를 같이 사용한 아래 SQL문에 대해서도 살펴보자.

첫 번째 SQL문은 올바르게 사용하였으나 만약 집계할 레코드가 없는 경우 NULL 값을 리턴한다. 두 번째 SQL문은 잘못된 사용법이다. NULL 값이 있는 레코드가 0으로 치환되어 분자의 값은 변함이 없으나 분모의 값을 크게 만들어서 평균값이 낮아지는 결과를 초래했다. 만약 이것이 업무 요건에 맞다고 판단한다면 사용해도 무방하다. 세 번째 SQL문은 올바른 사용법이다. 만약 집계할 레코드가 없더라도 0 값을 리턴한다. 우리는 세 번째 방법으로 해야 한다.

조인에서의 NULL

OUTER JOIN에서 연결되지 않는 레코드의 컬럼 값은 NULL이다.

첫 번째 SQL은 오라클의 OUTER JOIN 형식이고, 두 번째 SQL은 표준 SQL의 OUTER JOIN 형식이다. 어떤 형식을 사용하든지 연결되지 않는 레코드의 컬럼 값은 NULL이다. 위의 쿼리는 주문 내역을 조회하는 것이다. 조인을 통해 고객 정보를 조회하는데, 고객정보로 관리되는 내부 고객일 수도 있고 고객 정보로 관리되지 않는 임시 고객일 수도 있다. 일부 소규모 쇼핑몰에서는 고객의 회원 가입 없이 주문이 가능한 경우도 있으므로 이러한 SQL문이 사용된다. 이 경우 연결되지 않는 고객 레코드의 컬럼값은 NULL이 된다.

개발자를 힘들게 하는 NULL

지금까지 NULL과 관련해 상황별로 특별한 규칙들에 대해 살펴 보았다. 아마 많은 개발자가 NULL의 일관적이지 않는 서로 다른 규칙들을 보면서 놀라워하거나 오히려 더 혼란스러워 할 수도 있지 않을까 이처럼 NULL의 특별한 규칙을 잘 이해하고 따른다는 것은 결코 쉬운 일이 아니다.

이러한 NULL 규칙들이 다른 데이터베이스에서도 동일하게 적용되는 것은 아니다. 지금까지의 내용은 오라클에서의 NULL 규칙을 설명한 것에 불과하다. 다양한 상황에 따라서 NULL 규칙이 일관적이지 않는 부분도 개발자를 힘들게 하지만, 각각의 데이터베이스(ORACLE, MS SQL Server, SYBASE 등) 종류별로 NULL에 대해 상이한 규칙을 갖는 것도 개발자를 힘들게 한다. 결국 이 모든 것이 개발자에게 짐이 되며, 잘못된 NULL 사용으로 인하여 치명적인 결과를 초래할 수도 있다. NULL 오류를 온전히 개발자만의 몫으로 남기는 것은 부당하다.

NULL 회피전략

개발자들이 NULL에 대해서 완벽하게 이해하고 사용하는 것이 최선의 방법임에는 틀림이 없다. 하지만 NULL을 반드시 사용해야 하는 것은 아니다. 최선의 방법이 곤란하면 차선의 방법을 선택하면 된다. NULL에 대해서 충분히 이해하고 사용함에 전혀 부족함이 없다면 NULL을 사용하면 될 것이다. 만약 그렇지 않다면 NULL을 회피할 수도 있다. 테이블 생성시 NOT NULL 컬럼으로 규정하거나 NULL 대신에 특별한 의미 있는 값을 사용하면 된다. NULL 사용은 정책의 문제이지 반드시 지켜야 하는 금과옥조는 아니라고 생각한다.

이번 연재에서는 다양한 경우에 있어서의 NULL에 대한 특별한 규칙을 이해할 수 있도록 소개했다. 다음 연재에서는 알면 유용한 여러 가지 오라클 기능에 대해 자세히 알아 보겠다.

용기를 갖자

오라클 DB뿐 아니라 대부분의 DB 구성 알고리즘은 ‘어느 날 하늘에서 뚝 떨어져 새로 만들어진 것’이 아니라 실생활에서 이용되는 혹은 이미 상식 수준에서 인지되는 그런 보편적인 원리를 바탕으로 만들어졌으므로 쉽게 접근하고 이해할 수 있다. 서두에서 말했듯이 ‘레몬시장이론’을 상기하며 DB를 지레짐작으로 어려워하지 말고 용기를 내고 하나씩 터득해 나가기를 바란다.

이 글은 DB 전문가 수준의 이해를 요구하지는 않는다. 단지 DB에 대해서 더 친숙하고 더 쉽게 이해하고 접근하길 바랄 뿐이다. 이 글을 읽으면서 궁금하거나 의문 나는 점이 있으면, 댓글을 달아주실 것을 적극 바란다. 아무리 어렵고 힘든 일이더라고 ‘관계’와 ‘소통’으로 풀어나갈 수 있음을 다시 한 번 믿으며...

[지난 문제의 정답과 풀이]

원리를 이해하고 논리로 풀어가는, 쉬어가는 DB 문제

지난 연재에 출제한 ‘원리를 이해하고 논리로 풀어가는, 쉬어가는 DB 문제’에 대한 정답과 해설은 아래와 같다. 문제를 풀면서 DB 원리를 하나씩 배우고 이해할 수 있다.

[이번 호 문제]

원리를 이해하고 논리로 풀어가는, 쉬어가는 DB 문제

각 연재의 말미에 간단하면서도 재미있고 생각해 볼 만한 문제를 제시한다. DB의 원리를 이해할 수 있는 문제로 출제할 예정이다. 문제를 풀면서 DB 원리를 하나씩 배우고 이해할 수 있다. 정답과 그에 대한 설명은 다음 연재에서 한다.