데이터 베이스 NULL에 대한 기본적인 내용

 

이번 글에서는 NULL에 대한 기본적인 내용과 NULL에 대해 조심해야 할 점들을 간략히 적어보려고 한다. 이 글은 '개발자를 위한 인덱스 생성과 SQL 작성 노하우(이병국)'을 참고하여 작성하였다. 

 

위키백과에 의한 NULL의 정의는 다음과 같다.

 

'Null 또는 NULL은 구조적 질의언어(SQL)에서 데이터베이스 내의 데이터 값이 존재하지 않는다는 것을 지시하는 데 사용되는 특별한 표시어(special marker)이다'

 

위의 말처럼 NULL은 데이터가 존재하지 않는다는 것을 나타내며 알 수 없는 값이라는 의미도 가지고 있다. 0도 아니도 String 도 아니고 어느 타입도 아니고 단순히 NULL일 뿐이다. 일반적으로 테이블을 설정할 때 Primary Key는 반드시 NOTNULL로 설정된다. 다른 필드들도 NOTNULL 옵션을 추가할 수가 있다. 이렇게 설정한다면 NULL 값이 아니라 반드시 그 필드에 타입에 따라 어떤 값이라도 넣어야 한다. 

하지만 NULL을 허용하도록 설정을 하면 값을 추가하지 않으면 자동으로 NULL이 들어가지거나 NULL을 직접 넣을 수 있다. 이러한 NULL은 데이터가 없음을 표시할 때 매우 유용하고 자주 사용하게 된다. 

하지만 쉬우면서도 어려운 존재가 바로 NULL이다. 경험이 많은 개발자들도 NULL의 예상치 못한 결과에 당황하는 경우가 간혹 있다. NULL은 성능에도 영향을 미치며 잘못된 결괏값을 리턴하기도 한다. NULL은 프로젝트에서 의도하든 않든 많은 문제를 야기한다.

 

그렇기에 아래에는 NULL을 다루는데 있어서 기본적인 규칙들을 알아볼 것이다. 

 

 

사칙연산에서의 NULL

NULL은 사칙연산에서 실수를 할 경우가 많다. NULL을 0으로 생각하기 때문이다. 

아래의 상황을 보자.

SELECT NULL + 3 FROM DUAL 
SELECT NULL - 3 FROM DUAL 
SELECT NULL * 3 FROM DUAL
SELECT NULL / 3 FROM DUAL

 

 

위의 4개의 검색결과는 어떻게 나올까?

답은 모든 결과가 NULL로 나올 것이다.  NULL은 알 수 없는 값이기에 여기에 어떤 값을 더하거나 빼거나 곱하거나 나누거나 해도 결과는 모두 알 수 없는 값인 NULL이 된다. 

 

 

비교 연산에서의 NULL

비교 연산자(=,>,>=,!=) 사용 시 NULL을 이용한다면 어떤 결과를 줄까?

아래의 예시를 봐보자.

 

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

 

 

SELECT 번호 FROM 실적 WHERE 기준금액 = 목표금액 -------- 결과: 없음
SELECT 번호 FROM 실적 WHERE 기준금액 > 목표금액 -------- 결과: 없음
SELECT 번호 FROM 실적 WHERE 기준금액 < 목표금액 -------- 결과: 1
SELECT 번호 FROM 실적 WHERE 기준금액 <> 목표금액 ------- 결과: 1

 

 

왜 위와 같이 결과가 나오는지 확인해보자.

먼저 첫번째 쿼리는 기준금액과 목표금액이 같은 경우인데 표를 보면 숫자로는 같은 경우가 없고 똑같이 NULL인 경우는 있다. 하지만 둘 다 NULL이라고 해도 비교가 불가능하기에 결과가 나오지 않는다. 두 번째 퀴리도 NULL은 비교가 불가능 하기에 결과가 나오지 않았다. 

이렇듯 NULL 끼리의 비교도 할 수 없다는 것을 알 수 있다. 

 

 

집계함수에서의 NULL

집계 함수는 위에서의 예시와는 다른 결과가 나온다. 

아래의 예시를 봐보자. 

 

번호	주문금액
1	200
2	400
3	NULL

 

SELECT SUM(주문금액) FROM 주문 ---------------- 결과: 600
SELECT AVG(주문금액) FROM 주문 ---------------- 결과: 300
SELECT MAX(주문금액) FROM 주문 ---------------- 결과: 400
SELECT MIN(주문금액) FROM 주문 ---------------- 결과: 200

 

 

위와 예시를 보면 처음에는 이해가 가지 않을 수 있다. 왜냐하면 사칙연산에서 NULL을 사용할 경우 결괏값도 NULL이 나왔는데 위의 집합 연산을 사용했는데 결과가 NULL이 아닌 제대로 된 값이 나왔기 때문이다. 

여기서 중요한 것은 NULL 규칙을 논리적으로 접근하는 것이 아닌 데이터베이스 고유의 규칙으로 봐야한다는 것이다. 

집계 함수에서는 NULL을 제외한 것들로 계산을 하도록 규칙을 잡았을 것이다.

사실 수많은 레코드 중에서 극히 일부 레코드 값이 NULL이라고 해서 집계 결과를 NULL로 리턴한다면, 개발자 입장에서는 사용자의 요구사항을 무시한 처사라고 할 수 있다. 그렇기에 이러한 규칙을 정했을 것이다. 

그렇기에 NULL을 논리적으로 이해를 한다기보다는 규칙을 확인한다고 생각하면 좋을 것이다. 

 

 

문자열 결합에서의 NULL

표준 SQL에서는 NULL과 문자열의 결합은 NULL이다. 하지만 오라클에서는 문자열 결합에서 NULL에 대한 특별한 규칙을 따른다. NULL을 길이가 0인 문자열과 동일하게 인식한다. 그렇기에 NULL인 값을 가지고도 문자열 결합에 사용할 수 있는 것이다. 

이와 같이 NULL은 DB의 종류에 따라서 서로 다른 규칙을 갖고 있다는 것을 알아야 한다. 

MYSQL에서는 NULL을 문자열 결합에 사용하면 결과는 NULL이 나온다. 그렇기에 특수한 방법을 사용하는데 그 방법은 아래의 링크를 들어가서 확인하기 바란다. 

https://ponyozzang.tistory.com/223

MySQL 문자 결합(CONCAT,IFNULL) NULL 항목이 있는 경우 예제

 

 

논리 연산에서의 NULL

논리 연산자는 AND, OR, NOT 가 있다. 아래의 예시 상황을 확인해 보자.

 

NULL AND TRUE  ------------------ 결과: NULL
NULL AND FALSE ------------------ 결과: FALSE
NULL OR TRUE ------------------ 결과: TRUE
NULL OR FALSE ------------------ 결과: NULL
NOT(NULL) ------------------ 결과: NULL

 

논리 연산의 결과는 간단히 생각할 수  있다. AND는 하나만 FALSE일 경우 결과는 FALSE이기 때문에 만약 한쪽이 FALSE라면 다른 한쪽이 NULL이어도 결과는 FALSE가 나온다.

OR은 한쪽 만 TRUE라면 다른 한쪽에 상관없이 무조건 TRUE이기 때문에 다른 한쪽이 NULL이어도 결과 값은 TRUE 나오는 것이다. 

그렇기에 논리연산에서는 어느 논리 연산인지에 따라 어느 의미가 있을 수도 있고 없을 수도 있는 것이다.

 

 

인덱스에서 NULL

인덱스는 인덱스컬럼이 NULL이거나 IS NULL, IS NOT NULL 구문 사용 시 해당 인덱스를 사용할 수 없고 해당 테이블은 풀스캔을 하게 된다. 그 이유는 인덱스는 기본적으로 NULL 정보를 보관하지 않기 때문이다. 

그렇기에 인덱스를 사용할 때에는 IS NULL 구문을 사용할 수 없기에 다른 방식으로 검색을 해야한다. 

첫 번째는 NULL 회피 전략이다. 인덱스 칼럼에 NULL 대신 NULL과 비슷하게 의미 없는 값을 넣어버리는 것이다. 

두 번째는 함수 기반 인덱스를 활 요하는 것이다. 칼럼에 함수까지 포함시켜서 인덱스를 생성하면 된다. 실제 칼럼 값은 NULL이어도 인덱스에는 NULL이 아닌 다른 값이 들어가도록 설정하는 것이다. 

함수 기반 인덱스는 https://khdscor.tistory.com/48 를 참고하길 바란다.

인덱스의 유형과 특징(2. 함수기반 인덱스)

 

물론 테이블 내에서 대부분의 레코드를 조회하는 경우 인덱스 존재하더라도 무의미하기에 테이블 풀스캔이 더 효율적일 것이다. 

 

 

검색에서의 NULL

SQL 문 작성시 검색조건에서 NULL과 관련해 조회하는 경우가 많다.  검색에서의 NULL은 어떤 경우들을 가질까? 아래의 예시를 봐보자.

 

WHERE 컬럼 IS NULL  ------------------ 결과: 올바른 NULL 검색 조건
WHERE 컬럼 IS NOT NULL  -------------- 결과: 올바른 NULL 검색 조건
WHERE 컬럼 = NULL  ------------------- 결과: 틀린 NULL 검색 조건
WHERE 컬럼 <> NULL  ------------------ 결과: 틀린 NULL 검색 조건
WHERE 컬럼 = 'NULL'  ----------------- 결과: 문자열 'NULL' 검색 조건
WHERE 컬럼 LIKE '%NULL%'  ------------ 결과: 문자열 'NULL' 검색 조건
WHERE 컬럼 IN (NULL)  ---------------- 결과: 틀린 NULL 검색 조건
WHERE 컬럼 IN ('NULL')  --------------- 결과: 문자열 'NULL' 검색 조건

 

 

위와 같이 조건절에 IS NULL이나 IS NOT NULL 이외의 조건절은 잘못된 조건절이다. 여기서 주의하지 말아야 할 것은 IS NULL구문을 쓰면 인덱스가 사용불가인 거지 잘못된 경우가 아니다. 

 

 

함수에서의 NULL

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

 

NVL(컬럼, NULL이면 치환할 값)
NVL2(컬럼, NULL이 아니면 치환할 값, NULL이면 치환할 값)

 

 

NVL함수는 집계 함수와 같이 사용 시 주의해야 할 점이 있다. 아래의 예시를 보자.

 

번호	주문금액
1	200
2	400
3	NULL

 

 

SELECT SUM(주문금액)         FROM 주문 -------------- 결과: 600
SELECT SUM(NVL(주문금액,0))  FROM 주문 -------------- 결과: 600
SELECT NVL(SUM(주문금액), 0) FROM 주문 -------------- 결과: 600

SELECT AVG(주문금액)         FROM 주문 -------------- 결과: 300
SELECT AVG(NVL(주문금액,0))  FROM 주문 -------------- 결과: 200
SELECT NVL(AVG(주문금액), 0) FROM 주문 -------------- 결과: 300

 

 

첫 번째 쿼리는 올바르게 사용했지만 만약 집계할 레코드가 하나도 없다면 NULL을 리턴한다. 

두 번째 쿼리는 집계 함수에서는 NULL값이 있는 레코드를 제외하고 계산하기에 똑같은 결과가 나온다.

세 번째 쿼리는 집계할 레코드가 없는 경우 0을 리턴한다. 1, 2, 3 쿼리중 세번째 쿼리가 가장 올바른 쿼리다. 

네 번째 쿼리는 올바르게 작성했지만 만약 집계할 레코드가 없으면 NULL을 리턴한다. 

다섯 번째 쿼리는 잘못된 사용법인데 NULL값이 있는 레코드가 0으로 치환돼 분자의 값은 변함이 없으나 분모의 값을 크게 만들어서 평균값이 낮아지는 결과를 초래하였다. 

여섯 번째 쿼리는 올바른 사용법이다. 만약 집계할 레코드가 없더라도 0 값을 리턴한다. 이 방식을 사용해야 한다. 

 

 

조인에서의 NULL

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

 

 

지금까지 NULL과 관련하여 상황별로 규칙을 살펴보았다. NULL 규칙은 모든 데이터베이스에 동일하게 적용되는 것이 아니고 위의 내용들은 일부분에 불과할 뿐이니 NULL에 관한 문제에 대해선 더욱 열심히 찾아야 할 것이다.