개발자에게 NULL은 쉬우면서도 어려운 존재다. 경험이 많은 개발자라도 NULL의 예상치 못한 결과에 당황하는 경우가 간혹 있다. 오라클에서 NULL은 길이가 0인 문자열과 동일한 의미를 가지거나, 알 수 없는 미지의 값을 의미하기도 한다. 이러한 NULL은 프로젝트에서 의도하든 않든 간에 많은 문제를 야기하기도 한다.
1. 사칙연산에서의 NULL
테스트 데이터
create table test ( id number, name varchar(10), location varchar(10), age number );
create table test2 ( id number, name varchar(10), location varchar(10), age number );
insert into test values (1, '혜리','광주', 30) ;
insert into test values (2, '미연','인천', NULL) ;
insert into test values (3, '리정','서울', 26) ;
insert into test values (4, '예원','서울', 25) ;
insert into test values (5, '채원','서울', 24) ;
insert into test values (6, '파트리샤','광주', 22) ;
insert into test2 values(1, '혜리', '광주', 30);
insert into test2 values(2, '미연', '인천', 27);
insert into test2 values(3, '리정', '서울', 26);
insert into test2 values(4, '예원', '서울', NULL);
insert into test2 values(5, '채원', '서울', 24);
insert into test2 values(6, '파트리샤', '광주', 22);
## test
ID NAME LOCATION AGE
1 혜리 광주 30
2 미연 인천 [NULL]
3 리정 서울 26
4 예원 서울 25
5 채원 서울 24
6 파트리샤 광주 22
## test2
ID NAME LOCATION AGE
1 혜리 광주 30
2 미연 인천 27
3 리정 서울 26
4 예원 서울 [NULL]
5 채원 서울 24
6 파트리샤 광주 22
사칙연산에서 NULL 계산 결과는 항상 NULL이다. 알 수 없는 값 NULL에 어떤 값을 더하거나 빼거나 곱하거나 나누거나 사칙연산 계산 결과는 무조건 NULL이 됨을 이해해야한다.
SELECT a.ID,
a.AGE,
b.ID,
b.AGE,
(a.age + b.age),
(a.age - b.age),
(a.age * b.age),
(a.age / b.age)
FROM test a, test2 b
WHERE a.id = b.id ;
-- # ORACLE
ID AGE ID AGE (A.AGE+B.AGE) (A.AGE-B.AGE) (A.AGE*B.AGE) (A.AGE/B.AGE)
1 30 1 30 60 0 900 1
2 [NULL] 2 27 [NULL] [NULL] [NULL] [NULL]
3 26 3 26 52 0 676 1
4 25 4 [NULL] [NULL] [NULL] [NULL] [NULL]
5 24 5 24 48 0 576 1
6 22 6 22 44 0 484 1
-- # PostgreSQL
ID AGE ID AGE (A.AGE+B.AGE) (A.AGE-B.AGE) (A.AGE*B.AGE) (A.AGE/B.AGE)
1 30 1 30 60 0 900 1
2 [NULL] 2 27 [NULL] [NULL] [NULL] [NULL]
3 26 3 26 52 0 676 1
4 25 4 [NULL] [NULL] [NULL] [NULL] [NULL]
5 24 5 24 48 0 576 1
6 22 6 22 44 0 484 1
2. 비교연산에서의 NULL ( IN 조건 포함)
알 수 없는 값인 NULL은 어떤 비교를 하든 그 결과값은 NULL(알 수 없는 값)이다.
SELECT의 결과가 반환될 때는 WHERE절의 결과가 TRUE가 될 때 뿐이다. FALSE와 NULL의 경우는 반환되지 않는다.
-- =
SELECT a.ID FROM test a
WHERE a.age = NULL ;
--아무것도 조회되지 않는다.
-- >
select b.* from test a, test2 b
where a.id = 2 and a.age > b.age ;
--아무것도 조회되지 않는다.
-- <>
SELECT a.* FROM test a
WHERE a.age <> '50' ;
id name location age
1 혜리 광주 30
3 리정 서울 26
4 예원 서울 25
5 채원 서울 24
6 파트리샤 광주 22
-- age가 널값인 미연의 데이터는 나오지 않는다.
-- in 조건
SELECT a.* FROM test a, test b
WHERE a.age IN (b.age) ;
id name location age
1 혜리 광주 30
3 리정 서울 26
4 예원 서울 25
5 채원 서울 24
6 파트리샤 광주 22
-- age가 널값인 미연의 데이터가 나오지 않는다.
SELECT a.* FROM test a
WHERE a.age IN (30 , NULL);
id name location age
1 혜리 광주 30
-- age가 널값인 미연의 데이터가 나오지 않는다.
3. 집계함수에서의 NULL
집계함수 : sum, avg, max, min 과 같은 함수들에서는 NULL 값이 있는 레코드는 제외하고 계산한다.
select sum(age), avg(age), max(age), min(age) from test;
sum avg max min
127 25.4 30 22
select count(age) from test;
5
-- age가 널값인 미연은 제외하고 count 되었다.
4. 논리연산에서의 NULL
논리연산자는 AND, OR, NOT이 있다.
5. INDEX에서의 NULL
오라클에서는 인덱스가 있는 컬럼이라도 조건절에서 IS NULL 혹은 IS NOT NULL 구문 사용 시 인덱스를 사용하지 못하고 해당 테이블을 FULL SCAN 하게 된다. 그 이유는 인덱스는 기본적으로 NULL 정보를 보관하지 않기 때문이다. (PostgreSQL에선 NULL이 존재하더라도 인덱스를 사용할 수 있다.)
그러나, 인덱스가 복합 인덱스로 구성되어있고, NOT NULL인 컬럼이 있다면 인덱스 정보를 저장할 수 있기 때문에 인덱스 스캔이 가능하다.
--## ORACLE
CREATE INDEX test_idx_1 ON test ( age );
SELECT /*+ index(a test_idx_1) */ * FROM test a WHERE age = 30 ;
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 1 |00:00:00.01 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST | 1 | 1 | 23 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |
|* 2 | INDEX RANGE SCAN | TEST_IDX_1 | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 |
--------------------------------------------------------------------------------------------------------------------------------------------
SELECT /*+ index(a test_idx_1) */ * FROM test a WHERE age IS null;
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 23 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
-- ## PostgreSQL
-- PostgreSQL 같은 경우엔 is null 조건이여도 index scan이 가능하다.
postgres=# /*+ IndexScan(a) */
explain select * from test a where age = 30;
QUERY PLAN
--------------------------------------------------------------------------
Index Scan using test_idx_1 on test a (cost=0.13..8.15 rows=1 width=84)
Index Cond: (age = 30)
(2 rows)
postgres=# /*+ IndexScan(a) */
explain select * from test a where age is null;
QUERY PLAN
--------------------------------------------------------------------------
Index Scan using test_idx_1 on test a (cost=0.13..8.15 rows=1 width=84)
Index Cond: (age IS NULL)
(2 rows)
6. 함수에서의 NULL
오라클에서 사용하는 대표적인 NULL 관련 함수는 NVL과 NVL2 이다.
SELECT sum(age) FROM test; -- 127
SELECT sum(NVL(age, 0)) FROM test; -- 127
SELECT NVL(sum(age), 0) FROM test; -- 127
위의 결과는 다 같지만 우리는 세번째 방법을 사용하여 쿼리를 작성해야 한다. 첫번째 방식에선 집계함수 같은 경우 NULL 제외하고 계산이 되기 때문에 올바른 값이 나오긴 하지만 모든 값이 NULL인 경우 NULL 값을 반환하게 된다.
두번째 방법은 집계함수에서는 NULL 값이 있는 레코드는 제외하고 계산하기 때문에 NVL 사용의 의미가 없다. 오히려 레코드 수만큼 함수를 호출함에 따라 부하가 발생한다. 세번째 방법은 집계할 코드가 없더라도 0값을 리턴 하고 NVL함수는 최종 집계 결과에 대해서 한번만 호출 되었다. 당연히 세번째 방법을 사용해야 한다.