SQL Null 에 대해서
Null 은 특별한 존재다.
일반적인 등호, 부등호 비교들은 아무런 결과를 반환하지 못한다.
테스트용 테이블을 만들고
create table #Stat(rdate varchar(10), qty int)
insert #Stat values('20031001', 100)
insert #Stat values('20031002', 150)
insert #Stat values('20031003', 130)
insert #Stat values('20031004', null)
insert #Stat values('20031005', 80)
하단 쿼리를 실행해보자
select COUNT(*) as [All Rows], COUNT(qty) as [Qty Rows], sum(qty) as [sum qty], sum(qty) / COUNT(qty) as [Avg qty real], avg(qty) as [Avg qty], max(qty) as [Max qty], min(qty) as [Min qty] from #Stat
All Rows는 행의 개수를 세는것이므로 null 과는 무관한 결과를 보인다. 하지만 나머지 연산들에서 null 은 열외다. 값을 알수없으므로 이 값이 다른수보다 크거나 작은지 알 방법이 없기 때문이다.
select 100 + null as [컬럼1], 'abc' + null as [컬럼2]
null에 대한 사칙연산과 문자열 연산도 마찬가지로 null 이 나온다.
Null 처리를 위한 함수
ISNULL : NULL 값 치환
오라클에서는 NVL 함수와 같으며, 이 함수의 역할은 값이 만일 null 인경우 지정된 다른 값으로 반환해준다.
select ISNULL(null,'empty') as [컬럼1], ISNULL(10,'empty') as [컬럼2]
ISNULL 함수는 컬럼의 원래크기에 영향을 받으므로, 치환할값이 원래컬럼의 크기보다 크다면 잘려서 나온다.
COALESCE : 이중에 NULL이 아닌값을 찾기
이 함수는 여러 값 목록들을 받아서 이중에 null 이 아닌 최초의 값을 반환한다. 인자는 가변목록으로 왼쪽에서부터 처리되기 시작한다.
create table #customers (
userID varchar(20) primary key,
tel varchar(20) null,
hp varchar(20) null,
fax varchar(20) null
)
begin
insert #customers values('david', '02-456-4566', null, null )
insert #customers values('toto', null, '02-456-4522', null )
insert #customers values('shean', '02-4346-4563', null, null )
insert #customers values('vivid', null, null, '02-452-2246' )
insert #customers values('theriault', '032-326-7766', null, null )
insert #customers values('kevin', null, null, null)
end
위의 샘플데이터는 집전화, 핸드폰, 팩스번호의 정보가 들어있으며, 세 컬럼 모두 null 을 허용할수 있다. 이 경우, 고객들에게 연락하기 위해 하나의 대표 번호가 필요하다고 가정한다.
집전화가 없으면 핸드폰, 핸드폰이 없으면 팩스 아무것도 없을때는 “No Phone” 이라는 문자열을 출력하기로 한다.
select userID, coalesce(tel, hp, fax, 'No Phone') as Phone from #customers
일반적으로 3컬럼중에 null이 아닌 번호정보 하나라도 있는 경우 조회하고자 하면, 쿼리조건은 OR로 묶이거나 NOT이 들어간 AND쿼리가 될것이다.
select * from #customers where (tel is not null) or (hp is not null) or (fax is not null)
하지만 COALESCE함수를 쓰면 더 깔끔해진다.
select * from #customers where coalesce(tel, hp, fax) is not null
Null 인 경우 NOT을 빼주면 된다.
select * from #customers where coalesce(tel, hp, fax) is null
프로시저 코드와 COALESCE 함수를 같이 사용하면 재밌는 작업을 할수있다.
CSV(Comma separated value) 라는 파일이 있다.
이것은 여러값들을 “,” 콤마로 구분하여 하나의 값으로 치환하는 것이다. 즉 여러값들이 단일값으로 가지게 된다는 의미이다. 이를 Multi-Valued Column 이라고 부른다.
여러 로우들의 값을 단일 CSV 값으로 표현하고자 할때 상식적으로 While 루프에서 코드 값을 받아내야 하고 IF문에서 사용해야만 하겟지만. COALESCE솔루션을 사용하면 변수하나만 선언하는것으로도 해당 코드를 단순화 시킬수 있다.
COALESCE 함수는 또한 동적인 쿼리조건을 결정지을수도 있다.
이 위의 CSV쿼리와 유사한것이다. 변수의 값이 정해지지 않았을경우에는 초기값으로 Null을 가지게 된다. 이를 이용하면 Where 조건에서 동적쿼리를 구성할수 있다.
declare @fname varchar(100) declare @lname varchar(100) set @fname = 'paolo' select * from pubs..employee where fname = coalesce(@fname, fname) and lname = coalesce(@lname, lname)
쿼리에서 @fname 만이 ‘Paolo’라는 값이 주어졌다. @lname에는 값이 주어지지 않았다. 그러면 조건은 결국 fname=’Paolo’ and lname = lname 인 Where 절이 성립하게 된다.
NULLIF: 같은 값이면 NULL
이 함수는 첫번째 인자 값과 두번째 인자 값이 같으면 NULL 을 반환하는 역할을 수행한다.
select nullif(1,1) as '결과값'
NULL 관련 함수중에서 COALESCE 의 경우, 여러가지면에서 유용한 함수지만 한가지 약점이 있다. 값이 NULL을 반환하지는 않지만, 의미적으로 NULL인 경우에는 처리할수 없다. 많은 경우 NULL을 허용하지 않고 대신에 빈 문자열이나 0과 같은 기본값으로 NULL을 대신하는 경우가 많이 있다. 이 경우에 COALESCE 함수를 사용할수 없다.
따라서 이런 경우에 컬럼의 값이 특정값이면 NULL을 반환해주는 함수가 필요하다.
create table #CustOrders(
userID varchar(10),
payAmt1 money default 0.0,
payAmt2 money default 0.0,
payAmt3 money default 0.0
)
insert #CustOrders values('user1', 0.0, 100, 0.0)
insert #CustOrders values('user1', 0.0, 0.0, 150)
insert #CustOrders values('user1', 25, 0.0, 0.0)
insert #CustOrders values('user1', 0.0, 0.0, 0.0)
위의 상황은 한 고객이 PayAmt1에서 PayAmt3까지의 결제금액들을 가지고있다.
모든 값들은 기본값으로 0.0을 가지고있다. null값을 허용하지만 삽입하지는 않았다. 이 경우 COALESCE 함수를 사용해서 이중에서 0이 아닌 값들 하나를 반환할수 있겠는가?
select COALESCE( nullif(PayAmt1, 0.0), nullif(PayAmt2, 0.0), nullif(PayAmt3, 0.0), 0.0) PayAmt from #CustOrders
COALESCE 함수와 더불어 조건 판단의 양념이 되도록 NullIF 를 사용할수있다. 쿼리를 보면 값이 0인경우는 무시하고 0이 아닌 값을 찾아서 이 값을 반환하도록 하고있다.
NULL과 NOT IN
쿼리를 작성할때 해당 조건이 포함된 컬럼에 null 값의 유무를 확인한다는것은 복잡하고 괴로운일이다. 하지만 null은 그렇게 간단히 넘어갈 문제가 아니다. null이 포함되므로 인해 쿼리는 특수한 처리가 필요해진다. 특히 in연산이나 join연산에서는 부지불식간에 null이 문제를 발생시킬수 있다.
create table NULL_TEST( c1 varchar(10) null)
insert NULL_TEST values(‘A’)
insert NULL_TEST values(‘B’)
insert NULL_TEST values(‘C’)
insert NULL_TEST values(null)
위 테이블은 4개의 값이 포함되며, 3개의 문자값들과 null값이다. null을 아무런 문자도 입력하지 않은 경우라고 생각했다.
값이 없거나 “C” 인 값들을 모두 포함시키는 IN쿼리를 작성하면 다음과 같다.
select *
from null_test
where c1 in (null, 'c')
원하는 결과가 나오지 않았다. 이런결과가 어떻게 나온것인가? 기본적으로 IN 연산은 Equal operator “=” 으로 치환된 OR 연결 연산이다. 실행계획은
Where : ([NULL_TEST].[c1] = 'C' OR [NULL_TEST].[c1] = NULL))
보이다 시피 Column = null 로 되어있다. 더 심각한 문제는 IN이 아닌 NOT IN 연산에 있다. IN연산의 경우 OR절로 묶이는 것이므로 NULL을 제외하고는 그래도 결과가 나타난다. 하지만 NOT IN은 결과 자체가 없다.
select *
from null_test
where c1 not in (null, 'c')
NOT IN 연산은 다음과 같다. NOT(C1 = NULL OR C1 = ‘C’)이다. 이것은 결국 C1<>NULL AND C1<>’C’ 라는 조건으로 풀릴수있다. 이중의 어떤 조건과도 매치되지 않아야만 결과에 포함될수 있다.
NULL은 비교되지 않으므로 무조건 Unknown이다. 따라서 결과는 하나도 없다.
Where : ([NULL_TEST].[c1] <>'C' AND [NULL_TEST].[c1] <> NULL))
이것은 서브쿼리나 Join 연산에 있어서도 마찬가지다.
select * from null_test where c1 in ( select null union all select 'C') select * from null_test where c1 not in ( select null union all select 'C')
원하는 대로의 결과를 도출하려면 쿼리를 변경하고, null을 연산할수 있도록 조건을 변경해야 한다.
select *
from null_test
where c1 not in('C') and c1 is not null
select *
from null_test
where c1 in('C') or c1 is null
select *
from null_test
where c1 in (select null union all select 'C')or c1 is null
select *
from null_test
where c1 not in (
select c1 from (
select null c1 union all select 'C'
) as x
where x.c1 is not null)
and c1 is not null
select *
from null_test as a
inner join null_test as b
on a.c1 = b.c1 or (a.c1 is null and b.c1 is null)
관계형 데이터베이스에서 null 값의 허용이나 null 을 사용하는 것은 최소한으로 줄이는것이 가장 좋은 방법이다.
운좋겟도 SQL서버에서는 NULL 값을 특정한 비트맵으로 변경하여 데이터로 저장하고있다. 이 덕분에 다른 데이터베이스와는 다르게 NULL이 포함된 인덱스에서 조회가 가능하다. 다른데이터베이스에서는 NULL 값은 인덱스를 사용하지 못할수도 있다.
NULL이 들어간경우 SQL서버는 별도의 연산을 처리해야 하고, NULL이 포함된 경우 인덱스 오버헤드가 발생한다. 물론 그 영향은 미미 하지만 이것들이 모이면 성능저하가 일어나기 마련이다.
따라서 NULL을 최소화 함으로써 얻을수 있는 이익이 훨씬 많으며, NULL을 사용하면 수고해야 할 부분이 많아진다는 것이 결론이다.
상기의 내용은 손호성님의 “SQL Server 2005 완벽가이드 책”을 보며 필기한 내용입니다.
No Comments