반응형
/****************************************************************************************************************
-- Title : [SQL2012] Difference between ISNULL() and COALESCE()
-- Title : [SQL2012] Difference between ISNULL() and COALESCE()
-- Reference : sqlmag.com/t-sql
-- Key word : isnull coalesce 차이점
****************************************************************************************************************/
use tempdb;
-- Key word : isnull coalesce 차이점
****************************************************************************************************************/
use tempdb;
-- 테이블 생성-1
create table ttt
( a nvarchar(4)
, b nvarchar(8)
);
-- 데이터 입력-1
insert into ttt
select 'aaaa', null union all
select null, 'bbbbbbbb';
-- 확인-1
select *
from ttt;
-- 테스트-1
select isnull(a,b) "isnull(a,b)", coalesce(a,b) "coalesce(a,b)"
, isnull(b,a) "isnull(b,a)", coalesce(b,a) "coalesce(b,a)"
from ttt;
isnull(a,b) coalesce(a,b) isnull(b,a) coalesce(b,a)
----------- ------------- ----------- -------------
aaaa aaaa aaaa aaaa
bbbb bbbbbbbb bbbbbbbb bbbbbbbb
-- 테이블 생성-2
create table ttt2
( a nchar(4)
, b nchar(8)
);
-- 데이터 입력-2
insert into ttt2
select 'aa', null union all
select null, 'bbbbbb';
-- 확인-2
select *
from ttt2;
-- 테스트-2
select isnull(a,b) "isnull(a,b)", coalesce(a,b) "coalesce(a,b)"
, isnull(b,a) "isnull(b,a)", coalesce(b,a) "coalesce(b,a)"
from ttt2;
isnull(a,b) coalesce(a,b) isnull(b,a) coalesce(b,a)
----------- ------------- ----------- -------------
aa aa aa aa
bbbb bbbbbb bbbbbb bbbbbb
-- 테스트-3
SELECT ISNULL('abc', 1);
-- 테스트-4
SELECT ISNULL(null, null);
-- 테스트-3
SELECT COALESCE('abc', 1);
메시지 245, 수준 16, 상태 1, 줄 1
varchar 값 'abc'을(를) 데이터 형식 int(으)로 변환하지 못했습니다.
SELECT ISNULL('abc', 1);
-- 테스트-4
SELECT COALESCE(null, null);
메시지 4127, 수준 16, 상태 1, 줄 2
COALESCE의 인수 중 적어도 하나는 NULL 상수가 아닌 식이어야 합니다.
SELECT ISNULL(null, null);
반응형