소개.
SQL Server에 관련된 Q/A 란을 접하다 보면, CASE 함수를 몰라서 난처해 하는 경우를
자주 보게 된다. 특히 오라클 개발자들의 경우 DECODE문과 같은 기능을 수행하는
CASE 함수를 몰라서 곧 잘 질문하는 것을 볼 수 있다.
이제 그런 고민을 하지 말자. CASE 함수는 T-SQL의 해결사들 중 하나다.
단순하지만, 강력한 위력을 발휘하는 CASE 함수를 이번 기회에 알아두도록 하자.
SQL Server에서 Transact-SQL, Batch, Stored Procedure, Function,
Trigger 프로그램을 하면서, 조건문이 필요한 경우 if-else 문을 사용할 수 있다.
그러나, if-else문은 표준 SQL구문이 아니다.
따라서 SELECT 구문 같은 DML 구문내에서 사용하지를 못한다.
이때 CASE 함수를 적용할 수 있다.
더욱이, CASE 함수는 말 그대로 함수이다. 그렇다면 함수가 사용될 수 있는 곳에
CASE문을 쓸 수 있다는 사실이다.
참고.
가능한 아래 예제 코드들은 쿼리 분석기에 복사해서 테스트해 보기 바란다.
단순히 텍스트로 보기 불편한 것 같다.
DECODE 보다 강력한 CASE 함수.
CASE 함수는 다중 if문과 같은 구조를 가진다. 더불어 SELECT 구문 같은 DML과
연동을 해서 행 단위의 조건 처리가 가능하다는 특징을 가지고 있기 때문에 복잡한
쿼리 작성 시에 필수적으로 사용이 된다.
오라클에는 전통적으로 DECODE 라는 명령을 통해서 = 비교에 따른 행 단위 조건
처리가 가능했었지만, SQL Server에서는 ANSI 표준이며 보다 더 강력한 형식의
CASE 함수를 사용한다. (물론 오라클에서도 된다. ANSI 표준!)
구문.
CASE 함수는 지정된 조건식을 평가하고, 조건에 만족하는 결과 중 하나를 리
턴한다.
CASE 함수는 다음과 같은 항목들로 구성된다.
CASE 키워드
변환할 열 이름
검색할 식을 지정하는 WHEN 절 및 이를 바꿀 식을 지정하는 THEN 절
END 키워드
CASE 함수의 별칭을 정의하는 선택적 AS 절
또한, 두 가지 형식으로 사용될 수 있다.
1. 단순 CASE 함수
조건식이 한 번만 지정되고 WHEN 절에는 조건식과 비교 할
상수값만 선언한다. 각각의 WHEN 절을 비교해서 조건에
만족하면 THEN절 다음에 구문이 실행된다.
syntax 1.
CASE input_expression
WHEN when_expression1 THEN result_expression1
WHEN when_expression2 THEN result_expression2
[ ...n ]
[ ELSE else_result_expression ]
END
2. 검색된 CASE 함수 (CASE 절에는 아무런 조건식도 오지 않는다.)
각 WHEN절에 필요한 조건식을 별도로 지정할 수 있다.
조건이 만족하면 THEN절 다음에 명령이 실행된다.
syntax 2.
CASE
WHEN Boolean_expression1 THEN result_expression1
WHEN Boolean_expression2 THEN result_expression2
[ ...n ]
[ ELSE else_result_expression ]
END
NOTE: THEN절 다음에는 일반 상수값 뿐만 아니라 함수나
심지어 또 다른 쿼리문이 올 수도 있다.
반드시 잊지 말 것은 result_expression은 반드시 하나의 값을
리턴해야 한다는 것이다.
그러나 행 단위로 각각의 검색 조건이 반복 실행되므로,
성능에 관한한 최상의 결과를 얻기는 힘들다.
*/
/* Examples 1. CASE 함수 형식 별 예제 */
-- 1. 단순 CASE
declare @i int, @j varchar(10)
set @i = 1
set @j = (case @i when 1 then 'true'
when 2
then 'false'
else 'else' end)
select @j
-- 'true'가 출력
-- 2. 검색된 CASE
set @i = 5
set @j = (case when @i <= 1 then 'true'
when @i = 5
then 'false'
else 'else' end)
select @j
-- 'false'가 출력
-- 3. 서브쿼리를 사용한 예
set @j = (case 1 when 1
then (select count
(*) from northwind..products)
end)
select @j
-- 'false'가 출력
-- 4. 함수로 사용한 예, 계산된 컬럼과 연동
create table tempdb.dbo.casetable (
grade char(1)
, msg AS (case grade when 'a' then '최고' when 'b' then '우
수' else '보통' end)
)
insert into tempdb.dbo.casetable
select 'a' union all select 'b' union all select 'c'
select * from tempdb.dbo.casetable
/*
지금까진 빙산의 일각이다
CASE를 사용한 조건 데이터 처리.
실제로 CASE문을 필요로 하는 것은 바로 DML 명령들이다.
select 구문을 처리되는 집합 중 특정 컬럼값에 기초한 조건을 기준으로
테이블에 없지만 새로운 집합에 포함되어야 할, 새로운 컬럼을 생성하고,
그 컬럼에 값을 추출, 가공, 연산하는 용도로 곧잘 사용된다.
즉, select되는 각각의 행(row, record)에 대해 조건을 부여하고, 조건에
만족하는 결과를 새로운 컬럼 값에 할당하는 것이다.
이제, CASE문을 조건 데이터 처리와 함께 사용한 경우에 두 가지 특징을
보게 될 것이다.
CASE 특징.
1. 행 단위의 조건 처리가 가능
2. 새로운 컬럼 생성
아래 예제들을 통해서 이러한 사실을 이해할 수 있게 되기를 바란다.
참고로 관계형 데이터를 집합으로서 접근하다 보면 두 가지의 처리 방법을
요구 하는 경우가 곧 잘 발생한다.
행->열, 열->행으로 각각 변경하는 작업이다.
이중 행->열로 변경하는 작업에 필요한 것이 바로 CASE문이다.
*/
USE Northwind
-- 상황 1
-- 상황 1-1. 우선, 아래 데이터를 보자
select top 3 productid, unitsinstock from dbo.products
/* 결과
productid unitsinstock
----------- ------------
1 39
2 17
3 13
4 53
......
*/
-- 상황 1-2. 위에서, 각 제품코드별로 unitsinstock의 값이
-- 20보다 작거나 값으면 '재고 없슴'
-- 50보다 작으면 '주문 요망'
-- 그렇치 않으면 '재고' 라는 새로운 컬럼을 요구한다.
/* 결과
productid unitsinstock state
----------- ------------ ---------
1 39 주문 요망
2 17 재고 없슴
3 13 재고 없슴
4 53 재고
......
*/
-- 상황 1-3. 아래와 같이, 간단하게 해결할 수 있다.
select productid, unitsinstock,
(case
when unitsinstock <= 20 then '재고 없슴'
when unitsinstock < 50 then '주문 요망'
else '재고'
end) state
from dbo.products
GO
-- 상황 2
-- 상황 2-1. 우선, 아래 데이터를 보자
USE Northwind
select productid, year(orderdate) as orderdate, quantity
from dbo.[order details] od join dbo.orders o
on od.orderid = o.orderid
order by productid
/* 결과
productid orderdate quantity
----------- ----------- --------
1 1996 45
1 1996 18
1 1996 20
1 1996 15
1 1996 12
......
*/
-- 상황 2-2. 위에서, 각 제품코드별로 년도별 주문수량의 합계를
-- 구하고자 한다면?
/* 결과
productid y1996 y1997 y1998
----------- ----------- ----------- -----------
1 125 304 399
2 226 435 396
3 30 190 108
4 107 264 82
......
*/
-- 상황 2-3. 아래와 같이, 간단하게 해결할 수 있다.
USE Northwind
select productid
, sum(case when year(orderdate) <= 1996 then quantity end) as
y1996
, sum(case when year(orderdate) = 1997 then quantity end) as
y1997
, sum(case when year(orderdate) >= 1998 then quantity end) as
y1998
from dbo.[order details] od join dbo.orders o
on od.orderid = o.orderid
group by productid
order by productid
/*
select productid
, sum(case when odate <= 1996 then quantity end) as y1996
, sum(case when odate = 1997 then quantity end) as y1997
, sum(case when odate >= 1998 then quantity end) as y1998
from (select orderid, sum(year(orderdate)) odate
from orders group by orderid) o inner merge join [order details] od
on o.orderid = od.orderid
group by productid
order by productid
*/
-- 상황 3
-- 상황 3-1. 우선, 아래 데이터를 보자
USE Pubs
select pub_name, state from dbo.publishers
order by state asc
/* 결과
pub_name state
---------------------------------------- -----
GGG&G NULL
Lucerne Publishing NULL
Algodata Infosystems CA
Binnet & Hardley DC
Five Lakes Publishing IL
......
*/
-- 상황 3-2. 위에서, state 컬럼을 오름차순으로 출력하되
-- NULL 값은 맨 뒤에 나와야 한다면?
/* 결과
pub_name state
---------------------------------------- -----
Algodata Infosystems CA
Binnet & Hardley DC
Five Lakes Publishing IL
New Moon Books MA
Scootney Books NY
Ramona Publishers TX
GGG&G NULL
Lucerne Publishing NULL
*/
-- 상황 2-3. 아래와 같이, 간단하게 해결할 수 있다.
select pub_name, state from pubs.dbo.publishers
order by (case when state is null then 1
else 0 end), state
/*
마무리.
CASE 함수는 다양한 상황에서 문제 해결에 극적으로 활용될 수 있다.
CASE 함수의 적용 예를 통해서 여러분의 T-SQL 프로그램 능력을 향상시킬 수 있을 것이
다.
추가로, T-SQL에는 CASE와 유사한 COALESCE() 함수가 있다.
SQL Server에 관련된 Q/A 란을 접하다 보면, CASE 함수를 몰라서 난처해 하는 경우를
자주 보게 된다. 특히 오라클 개발자들의 경우 DECODE문과 같은 기능을 수행하는
CASE 함수를 몰라서 곧 잘 질문하는 것을 볼 수 있다.
이제 그런 고민을 하지 말자. CASE 함수는 T-SQL의 해결사들 중 하나다.
단순하지만, 강력한 위력을 발휘하는 CASE 함수를 이번 기회에 알아두도록 하자.
SQL Server에서 Transact-SQL, Batch, Stored Procedure, Function,
Trigger 프로그램을 하면서, 조건문이 필요한 경우 if-else 문을 사용할 수 있다.
그러나, if-else문은 표준 SQL구문이 아니다.
따라서 SELECT 구문 같은 DML 구문내에서 사용하지를 못한다.
이때 CASE 함수를 적용할 수 있다.
더욱이, CASE 함수는 말 그대로 함수이다. 그렇다면 함수가 사용될 수 있는 곳에
CASE문을 쓸 수 있다는 사실이다.
참고.
가능한 아래 예제 코드들은 쿼리 분석기에 복사해서 테스트해 보기 바란다.
단순히 텍스트로 보기 불편한 것 같다.
DECODE 보다 강력한 CASE 함수.
CASE 함수는 다중 if문과 같은 구조를 가진다. 더불어 SELECT 구문 같은 DML과
연동을 해서 행 단위의 조건 처리가 가능하다는 특징을 가지고 있기 때문에 복잡한
쿼리 작성 시에 필수적으로 사용이 된다.
오라클에는 전통적으로 DECODE 라는 명령을 통해서 = 비교에 따른 행 단위 조건
처리가 가능했었지만, SQL Server에서는 ANSI 표준이며 보다 더 강력한 형식의
CASE 함수를 사용한다. (물론 오라클에서도 된다. ANSI 표준!)
구문.
CASE 함수는 지정된 조건식을 평가하고, 조건에 만족하는 결과 중 하나를 리
턴한다.
CASE 함수는 다음과 같은 항목들로 구성된다.
CASE 키워드
변환할 열 이름
검색할 식을 지정하는 WHEN 절 및 이를 바꿀 식을 지정하는 THEN 절
END 키워드
CASE 함수의 별칭을 정의하는 선택적 AS 절
또한, 두 가지 형식으로 사용될 수 있다.
1. 단순 CASE 함수
조건식이 한 번만 지정되고 WHEN 절에는 조건식과 비교 할
상수값만 선언한다. 각각의 WHEN 절을 비교해서 조건에
만족하면 THEN절 다음에 구문이 실행된다.
syntax 1.
CASE input_expression
WHEN when_expression1 THEN result_expression1
WHEN when_expression2 THEN result_expression2
[ ...n ]
[ ELSE else_result_expression ]
END
2. 검색된 CASE 함수 (CASE 절에는 아무런 조건식도 오지 않는다.)
각 WHEN절에 필요한 조건식을 별도로 지정할 수 있다.
조건이 만족하면 THEN절 다음에 명령이 실행된다.
syntax 2.
CASE
WHEN Boolean_expression1 THEN result_expression1
WHEN Boolean_expression2 THEN result_expression2
[ ...n ]
[ ELSE else_result_expression ]
END
NOTE: THEN절 다음에는 일반 상수값 뿐만 아니라 함수나
심지어 또 다른 쿼리문이 올 수도 있다.
반드시 잊지 말 것은 result_expression은 반드시 하나의 값을
리턴해야 한다는 것이다.
그러나 행 단위로 각각의 검색 조건이 반복 실행되므로,
성능에 관한한 최상의 결과를 얻기는 힘들다.
*/
/* Examples 1. CASE 함수 형식 별 예제 */
-- 1. 단순 CASE
declare @i int, @j varchar(10)
set @i = 1
set @j = (case @i when 1 then 'true'
when 2
then 'false'
else 'else' end)
select @j
-- 'true'가 출력
-- 2. 검색된 CASE
set @i = 5
set @j = (case when @i <= 1 then 'true'
when @i = 5
then 'false'
else 'else' end)
select @j
-- 'false'가 출력
-- 3. 서브쿼리를 사용한 예
set @j = (case 1 when 1
then (select count
(*) from northwind..products)
end)
select @j
-- 'false'가 출력
-- 4. 함수로 사용한 예, 계산된 컬럼과 연동
create table tempdb.dbo.casetable (
grade char(1)
, msg AS (case grade when 'a' then '최고' when 'b' then '우
수' else '보통' end)
)
insert into tempdb.dbo.casetable
select 'a' union all select 'b' union all select 'c'
select * from tempdb.dbo.casetable
/*
지금까진 빙산의 일각이다
CASE를 사용한 조건 데이터 처리.
실제로 CASE문을 필요로 하는 것은 바로 DML 명령들이다.
select 구문을 처리되는 집합 중 특정 컬럼값에 기초한 조건을 기준으로
테이블에 없지만 새로운 집합에 포함되어야 할, 새로운 컬럼을 생성하고,
그 컬럼에 값을 추출, 가공, 연산하는 용도로 곧잘 사용된다.
즉, select되는 각각의 행(row, record)에 대해 조건을 부여하고, 조건에
만족하는 결과를 새로운 컬럼 값에 할당하는 것이다.
이제, CASE문을 조건 데이터 처리와 함께 사용한 경우에 두 가지 특징을
보게 될 것이다.
CASE 특징.
1. 행 단위의 조건 처리가 가능
2. 새로운 컬럼 생성
아래 예제들을 통해서 이러한 사실을 이해할 수 있게 되기를 바란다.
참고로 관계형 데이터를 집합으로서 접근하다 보면 두 가지의 처리 방법을
요구 하는 경우가 곧 잘 발생한다.
행->열, 열->행으로 각각 변경하는 작업이다.
이중 행->열로 변경하는 작업에 필요한 것이 바로 CASE문이다.
*/
USE Northwind
-- 상황 1
-- 상황 1-1. 우선, 아래 데이터를 보자
select top 3 productid, unitsinstock from dbo.products
/* 결과
productid unitsinstock
----------- ------------
1 39
2 17
3 13
4 53
......
*/
-- 상황 1-2. 위에서, 각 제품코드별로 unitsinstock의 값이
-- 20보다 작거나 값으면 '재고 없슴'
-- 50보다 작으면 '주문 요망'
-- 그렇치 않으면 '재고' 라는 새로운 컬럼을 요구한다.
/* 결과
productid unitsinstock state
----------- ------------ ---------
1 39 주문 요망
2 17 재고 없슴
3 13 재고 없슴
4 53 재고
......
*/
-- 상황 1-3. 아래와 같이, 간단하게 해결할 수 있다.
select productid, unitsinstock,
(case
when unitsinstock <= 20 then '재고 없슴'
when unitsinstock < 50 then '주문 요망'
else '재고'
end) state
from dbo.products
GO
-- 상황 2
-- 상황 2-1. 우선, 아래 데이터를 보자
USE Northwind
select productid, year(orderdate) as orderdate, quantity
from dbo.[order details] od join dbo.orders o
on od.orderid = o.orderid
order by productid
/* 결과
productid orderdate quantity
----------- ----------- --------
1 1996 45
1 1996 18
1 1996 20
1 1996 15
1 1996 12
......
*/
-- 상황 2-2. 위에서, 각 제품코드별로 년도별 주문수량의 합계를
-- 구하고자 한다면?
/* 결과
productid y1996 y1997 y1998
----------- ----------- ----------- -----------
1 125 304 399
2 226 435 396
3 30 190 108
4 107 264 82
......
*/
-- 상황 2-3. 아래와 같이, 간단하게 해결할 수 있다.
USE Northwind
select productid
, sum(case when year(orderdate) <= 1996 then quantity end) as
y1996
, sum(case when year(orderdate) = 1997 then quantity end) as
y1997
, sum(case when year(orderdate) >= 1998 then quantity end) as
y1998
from dbo.[order details] od join dbo.orders o
on od.orderid = o.orderid
group by productid
order by productid
/*
select productid
, sum(case when odate <= 1996 then quantity end) as y1996
, sum(case when odate = 1997 then quantity end) as y1997
, sum(case when odate >= 1998 then quantity end) as y1998
from (select orderid, sum(year(orderdate)) odate
from orders group by orderid) o inner merge join [order details] od
on o.orderid = od.orderid
group by productid
order by productid
*/
-- 상황 3
-- 상황 3-1. 우선, 아래 데이터를 보자
USE Pubs
select pub_name, state from dbo.publishers
order by state asc
/* 결과
pub_name state
---------------------------------------- -----
GGG&G NULL
Lucerne Publishing NULL
Algodata Infosystems CA
Binnet & Hardley DC
Five Lakes Publishing IL
......
*/
-- 상황 3-2. 위에서, state 컬럼을 오름차순으로 출력하되
-- NULL 값은 맨 뒤에 나와야 한다면?
/* 결과
pub_name state
---------------------------------------- -----
Algodata Infosystems CA
Binnet & Hardley DC
Five Lakes Publishing IL
New Moon Books MA
Scootney Books NY
Ramona Publishers TX
GGG&G NULL
Lucerne Publishing NULL
*/
-- 상황 2-3. 아래와 같이, 간단하게 해결할 수 있다.
select pub_name, state from pubs.dbo.publishers
order by (case when state is null then 1
else 0 end), state
/*
마무리.
CASE 함수는 다양한 상황에서 문제 해결에 극적으로 활용될 수 있다.
CASE 함수의 적용 예를 통해서 여러분의 T-SQL 프로그램 능력을 향상시킬 수 있을 것이
다.
추가로, T-SQL에는 CASE와 유사한 COALESCE() 함수가 있다.
작성자. 김정선(visualdb@unitel.co.kr)