Oracle 널값(null)에 대하여 정리

1. NULL값 정의

 

RDBMS에서 NULL은 0 이나 공백이 아닌 값을 알수가 없다의 의미입니다.

이말은 값이 없다 입니다. 이는 Oracle 상에서 수치를 계산할때 문제가 됩니다.

null이 포함된 수식 계산에서는 무조건 null이 출려되어 잘못된 결과가 나오기 때문입니다.

특히 수치계산일때는 테이블 생성시 not null로 해주거나 nvl()함수로 제대로 치환해줘야

합니다.

 

2. NULL값 잘못된 처리

 

숫자의 경우

 

create table jun1(

a number,

b number);

insert into jun1 values(null,12);

select a+b from jun1;

 

라고 한다면 12가 나올것 같지만 null값이 포함된 계산식은 무조건 null이 나옵니다.

그래서 nvl()를 사용하거나 not null을 사용하는게 좋습니다.

 

문자열의 경우

create table jun2(

a varchar2(10),

b varchar2(10));

insert into jun2 values(null,'11');

select concat(a,b) from jun2;

 

라고 한다면  11이 나옵니다. mysql에서는 문자열도 null이지만 oracle에서는

문자열은 null이 들어가도 null로 출력되지 않고 그대로 연결됩니다.

 

''(빈공백)을 null로 인식하는 오라클 테스트 예제

 

create table test

(

 a varchar2(10),

 b varchar2(10)

)

 

insert into test values ('',null);

insert into test values ('test','test');

commit;

 

select count(*) from test where a = '';
--0개출력

select count(*) from test where a is null;
--1개출력

select count(*) from test where b = '';
--0개출력

select count(*) from test where b is null;

--1개출력

 

※ 오라클에서는 ''(빈공백)을 NULL값으로 인식합니다.

※ 오라클에서는 '' 을 null로 인식하며 '' 는 = '' 가 아닌 IS NULL 로 조회해야 검색가능하다.

※ 오라클에서는 NULL값이나 ''(빈공백)값은 널연산자외에 연산자로는 조회 불가능합니다.

이말은 널연산자외에 연산자에서는 널값을 조회대상에서 제외한다는 애기입니다.

 

3.많은 양의 null처리

 

상당히 많은 양의 레코드에 null이 있다면 계산하는데 문제가 많다.

update table_name set num=0 where num is null; -- 정수의 경우

update table_name set num=' ' where num is null; -- 문자열의 경우

와 같이 null값을 지정된 숫자로 일괄로 바꾸어준다.

 

table을 만들시에 모든컬럼에 not null 제약조건을 주는게 좋습니다.

 

※nvl()함수를 이용한다.

null이면 지정된 값으로 출력합니다. 실제 데이타는 바뀌지 않으며 단지 출력용입니다.

 

select nvl(comm,0) from emp;

--comm컬럼의 값이 null이면 0을 출력하고 값이 있다면 해당 값을 출력한다.

select nvl(hiredate,'01-JAN-97') from emp;--hiredate컬럼의 값이 null이면 01-JAN-97을 출력하고 값이 있다면 해당 값을 출력한다.

select nvl(job,'not') from emp;

--job컬럼의 값이 null이면 job을 출력하고 값이 있다면 해당 값을 출력한다.

select ename,sal,comm,(sal*12)+nvl(comm,0) from emp;

--수치계산에서는 null이 포함되면 결과는 null이지만 nvl()로 처리되어 모두 계산되어진다.

select ename,sal,comm,(sal*12)+comm from emp;

--수치계산에서는 null이 포함되면 결과는 null이다 그래서 comm에 null이 포함된 레코드는

--결과값이 null로 나옵니다.

 

4.mysql과 다른 oracle의 범위

 

mysql에서는 select * from table_name where name is null하면 실제 컬럼에 null이 들어가고 공백이나 실제로 값이 있는 컬럼은 빼고 검색하지만

oracle에서는 select * from table_name where name is null하면 실제컬럼에 공백이나 빈공간이 null로 인식되어 검색됩니다.

 

※name은 예를든 컬럼

mysql null범위

1.데이타값이 null인값 -> ''의 문자값은 = ''로 비교를 해야하고 name is null로 하면 안됩니다.

 

oracle null범위

1.데이타값이 빈공간이나('') 값  -> '' 의 문자값은 = ''로 비교가 안되고 name is null로 합니다.

 

5. 널값은 비교대상이 될수 없다.

 

SELECT '널값' test FROM dual WHERE '' <> '널값';

-- 아무것도 출력안됨

-- 오라클에서 ''은 null입니다. null은 비교 대상이 될수 없으므로 is not null 연산자를

-- 이용해서 비교해야 정상적으로 자료가 출력됩니다.

-- 한마디로 널값은 =, <> 등으로 비교할 수 없습니다.

 

SELECT '널값' test FROM dual WHERE nvl('','널값') <> '널값';

-- 널값

-- 널값을 nvl 함수로 치환후 비교하면 정상출력됨

 

SELECT '널값' test FROM dual WHERE '' is null;

-- 널값

-- is null 연산자를 이용해서 정상출력됨