MS SQL 개발팁 #1. ISNULL(NULLIF) 사용법(CASE WHEN 0 THEN 1)

안녕하세요. 「OkayS2」입니다.

 

오늘은 CASE WHEN 0 THEN ELSE ...을 간략히 작성할 수 있는 MS SQL 팁인 ISNULL와 NULLIF 을 활용한 사용법을 소개하고자 합니다.

 

MS SQL 에서 특정 값을 나누는 작업을 할때, 분모가 0이라면 '0으로 나누기 오류가 발생했습니다.' 의 에러 메시지가 출력되며, 에러를 방지하기 위해 분모자리에 CASE WHEN 0 THEN 1 ELSE '' END 와 같이 긴줄의 CASE문을 넣어야합니다.

 

계산문인 경우, 대부분이 분모에 0을 방지하고자 0일때 1로 계산되게끔 처리를 해야하기 때문에 CASE문을 쓰지 않는 방법에 대해 찾아보았고, 간단히 ISNULL과 NULLIF문으로 대체가 가능한 것을 찾아 공유하고자 합니다.

 

먼저, 테스트를 위해 TEMP TABLE 생성및 데이터 INSERT를 합니다.

 

CREATE TABLE TEMP 
(
    NAME VARCHAR(12),
    NUM1 NUMERIC(5,1),
    NUM2 NUMERIC(5,2)
)
 
INSERT INTO TEMP VALUES ('A',1,1)
INSERT INTO TEMP VALUES ('B',1,2)
INSERT INTO TEMP VALUES ('C',1,3)
INSERT INTO TEMP VALUES ('D',1,4)
INSERT INTO TEMP VALUES ('E',1,0) -- 0 오류를 위한 데이터

 

데이터를 생성한 후 나누기 계산을 처리하면 '0으로 나누기 오류가 발생했습니다.' 라는 에러메시지가 출력됩니다.

 

SELECT NAME,NUM1/NUM2 FROM TEMP

나누기 오류

 

이 오류를 처리하기 위해 CASE문을 사용하여 분모가 0일때 1로 계산되게 쿼리를 수정합니다.

 

SELECT NAME, NUM1/(CASE WHEN NUM2=0 THEN 1 ELSE NUM2 END) CAL FROM TEMP 

 

물론, 이렇게 처리하면 0일때 1로 치환되기 때문에 산출은 제대로 되지만 예제가 아닌 장문의 쿼리일때는 가독성이 떨어지고 복잡한 처리가 되기 때문에 ISNULL과 NULLIF 함수를 사용하여 수정해봅니다.

 

SELECT NAME, NUM1/ISNULL(NULLIF(NUM2,0),1) SUM FROM TEMP

 

반응형

 

먼저, 사용되는 함수에 대한 설명입니다.

 

NULLIF는 두 식이 같으면 Null 값을 반환하는 함수입니다.

 

NULLIF

 

ISNULL은 첫번째 식이 NULL일때, 지정된 대체값으로 변경하는 함수입니다.

 

ISNULL

 

사용 원리는, 먼저 NULLIF로 0인 경우 값을 NULL로 변경시킨 다음, NULL을 1로 치환하는 방법입니다.

 

두 방법에 대해 비교를 하자면,, 

보통 업무에서 사용할때는 예제의 NUM2이 긴 경우가 많기 때문에 ISNULL(NULLIF)를 사용하면 CASE문을 쫒는 시간을 좀 더 줄일 수 있는 방법이라고 생각합니다.

 

--CASE 사용
SELECT NAME, NUM1/(CASE WHEN NUM2=0 THEN 1 ELSE NUM2 END) CAL FROM TEMP
 
--ISNULL(NULLIF) 사용
SELECT NAME, NUM1/ISNULL(NULLIF(NUM2,0),1) SUM FROM TEMP

혹시나 저처럼 CASE문을 줄이기 위해 고민하는 분들에게 한번쯤은 사용해볼 수 있는 방법이지 않을까 싶습니다.

 

감사합니다.