[MSSQL]SQL 작성시 NULL 데이터로 인해 발생할 수 있는 이슈사항 및 해결방법

서론

 

직장 후임분으로부터 질문을 받은 적이 있었습니다. 질문의 내용은 SQL로 문자열 처리를 했는데 결괏값이 예상과 다르게 NULL로 나온다는 것이었습니다. 원인을 찾아보니 문자열에 NULL이 더해서 NULL이 반환되었던 것이 이유였습니다. 이처럼 SQL을 작성할 때 NULL 데이터로 인해 간과할 수 있는 이슈사항들을 현업에서의 경험을 바탕으로 정리해보았습니다.

---

본론

 

문제 1 : 문자열을 더할 시 NULL이 합쳐질 경우

 

만약 아래와 같이 데이터가 주어졌을 때 '이름(이메일)'로 컬럼을 표현해야 하는 경우를 생각해봅시다. 만약 이메일이 입력되지 않았을 경우 '이름()'으로 표현해야 하는 컬럼이라고 가정을 해봅니다.

 

CREATE TABLE #TEMP_TEST_TABLE(
 ID NVARCHAR(10)--아이디
,NAME NVARCHAR(10)--이름
,EMAIL NVARCHAR(50)--이메일
)
INSERT INTO #TEMP_TEST_TABLE
SELECT 
'ID0001'
,'Alice'
,'Alice@gmail.com'
INSERT INTO #TEMP_TEST_TABLE
SELECT 
'ID0002'
,'Bob'
,NULL
INSERT INTO #TEMP_TEST_TABLE
SELECT 
'ID0003'
,'Sam'
,'Sam@gmail.com'
SELECT NAME+ '(' + EMAIL + ')' FROM #TEMP_TEST_TABLE

 

 

[조회 데이터 1]

 

 

[조회 데이터 1] 기준으로 아래와 같이 작성할 경우 Bob에 대한 데이터가 나오지 않게 됩니다. 위의 요구사항 조건에 따라 이메일이 입력되지 않았을 경우 '이름()'인 'Bob()'이 나와야 하므로 이는 잘못 작성된 쿼리라 볼 수 있습니다. 원인은 ['특정 문자열' + NULL]의 결괏값은 ['특정 문자열']이라고 생각할 수 있지만 [NULL]이 되기 때문입니다.

 

잘못된 문자열 처리 예 쿼리

 

SELECT NAME+ '(' + EMAIL + ')' AS COL FROM #TEMP_TEST_TABLE

 

 

[결과]

 

 

문제 1. 해결방법

 

1) NULL 값이 들어갈 수 있는 컬럼에 대해 ISNULL 처리

 

SELECT NAME+ '(' + ISNULL(EMAIL,'') + ')' AS COL FROM #TEMP_TEST_TABLE

 

2) NULL을 빈 문자열 ''로 암시적 변환해주는 CONCAT 함수를 이용

 

SELECT CONCAT(NAME, '(' , EMAIL , ')' ) AS COL FROM #TEMP_TEST_TABLE

 

[결과]

 

 

따라서 ISNULL처리를 해주거나 CONCAT 함수를 사용하여 문제를 해결할 수 있습니다.

 

문제 2. NULL데이터가 포함된 컬럼을 조회할 경우

 

NULL이 포함될 수 있는 컬럼을 WHERE 조건에 포함시킬 때 NULL인 데이터가 조회 결과에 나오지 않을 수 있습니다. 위의 [조회 데이터 1] 예시에서 EMAIL을 조회 조건으로 할 경우 아래와 같은 쿼리를 작성할 수 있습니다.

 

잘못된 조회 쿼리 예

 

DECLARE @EMAIL NVARCHAR(50)
IF(@EMAIL = '' OR @EMAIL IS NULL) BEGIN SET @EMAIL = '%' END

SELECT * FROM #TEMP_TEST_TABLE 
WHERE EMAIL LIKE @EMAIL

 

 

[결과]

 

 

LIKE '%'로 조회해서 [조회 데이터 1]에 해당하는 3행이 전부 조회 결과로 나올 거라 생각할 수 있으나 실제 결괏값은 EMAIL이 NULL인 2행을 제외한 행들이 조회됩니다. 만약 NULL인 데이터를 안 나오게 하는 것이 의도였다면 상관없으나 전체 행에 대한 반환을 목적으로 한 경우 잘못 작성된 쿼리라 볼 수 있습니다.

 

문제 2. 해결방법

 

1) WHERE 구문에 있는 NULL 값이 들어갈 수 있는 컬럼에 대해 ISNULL 처리

 

DECLARE @EMAIL NVARCHAR(50)
IF(@EMAIL = '' OR @EMAIL IS NULL) BEGIN SET @EMAIL = '%' END

SELECT * FROM #TEMP_TEST_TABLE 
WHERE ISNULL(EMAIL,'') LIKE @EMAIL

 

[결과]

 

 

따라서 조회 컬럼에 대해 ISNULL 처리를 해주면 원하는 전체 행 결괏값을 얻을 수 있습니다.

 

문제 3. 집계 함수가 NULL을 반환할 경우

 

아래와 같이 ID에 대한 수량 1(QTY1), 수량 2(QTY2) 컬럼이 있다고 가정해봅시다.

 

CREATE TABLE #TEMP_TEST_TABLE(
ID NVARCHAR(10)--아이디
,QTY1 INT--수량1
,QTY2 INT--수량2
)
INSERT INTO #TEMP_TEST_TABLE
SELECT 
'ID0001'
,1
,2
INSERT INTO #TEMP_TEST_TABLE
SELECT 
'ID0002'
,3
,NULL
INSERT INTO #TEMP_TEST_TABLE
SELECT 
'ID0003'
,NULL
,4
SELECT * FROM #TEMP_TEST_TABLE 

 

 

[조회 데이터 2]

 

 

위의 [조회 데이터 2]에서 ID값이 'ID1'로 시작하는 데이터에 대해 MAX(QTY1)을 구한다고 가정해봅시다. 'ID1'로 시작하는 데이터는 없으므로 NULL을 반환할 것입니다.

 

잘못된 집계 값 예 쿼리

 

SELECT ISNULL(MAX(QTY1),0) AS COL 
FROM #TEMP_TEST_TABLE 
WHERE ID LIKE 'ID1'+'%'

 

[결과]

 

 

이와 같이 조건에 맞는 데이터가 존재하지 않을 경우 집계 함수(COUNT, AVG, MAX, MIN, SUM)는 NULL을 반환합니다. 따라서 DB에서 조회한 값을 코드단에서 (예 : C#, JAVA 등...) NULL을 허용하지 않는 형식으로 형 변환할 경우 (예 : 숫자로 형 변환) Exception이 발생할 수가 있기에 이에 대한 예외처리를 고려해야 합니다.

 

 

문제 3. 해결방법

 

1. 집계 함수에 대한 ISNULL 처리

 

SELECT ISNULL(MAX(QTY1),0) AS COL 
FROM #TEMP_TEST_TABLE 
WHERE ID LIKE 'ID1'+'%'

 

 

[결과]

 

 

따라서 NULL이 되어도 0이 되므로 코드단에서 숫자 형 변환 작업을 거쳐도 Exception이 발생하지 않게 됩니다.

 

 

문제 4. 집계 함수 안에서 숫자 컬럼들을 더할 때 NULL 컬럼이 존재할 경우

 

보통 숫자 컬럼은 Default를 0으로 설정하지만 특정한 임시 테이블이나 테이블 반환 함수에서 조회된 숫자 컬럼 값이 NULL이 들어가는 경우가 발생할 때 있습니다. 만약 숫자 컬럼인 컬럼 1과 컬럼 2의 합계를 나타낼 경우 NULL이 포함되어 있으면 집계 함수는 계산을 하지 않기 때문에 이로 인한 잘못된 값이 집계될 수 있습니다.

 

위의 예시인 [조회 데이터 2]에서 QTY1과 QTY2의 합을 구하는 쿼리를 다음과 같이 작성할 경우 문제가 생길 수 있습니다.

 

잘못된 집계 값 예 쿼리

 

SELECT SUM(QTY1 + QTY2) AS SUM_QTY FROM #TEMP_TEST_TABLE

 

 

[결과]

 

 

전체 데이터 행에 대한 QTY1, QTY2 합을 계산한 기댓값 1+2+3+4 = 10을 원했지만 NULL을 제외한 데이터만 집계되었으므로 실제 결괏값은 3이 됩니다.

 

문제 4. 해결방법

 

각 숫자 컬럼에 대한 ISNULL 처리

 

SELECT SUM(ISNULL(QTY1,0)+ISNULL(QTY2,0)) AS SUM_QTY 
FROM #TEMP_TEST_TABLE

 

 

집계 함수를 분리하여 ISNULL 처리

 

SELECT ISNULL(SUM(QTY1),0)+ISNULL(SUM(QTY2),0) AS SUM_QTY 
FROM #TEMP_TEST_TABLE

 

[결과]

 

 

따라서 기댓값인 10이 나옴을 알 수 있습니다.

 

문제 5. AVG 집계 함수를 사용할 때 NULL로 인해 잘못된 평균이 나오는 경우

 

위의 [조회 데이터 2]로 예시를 다시 들면 수량 2(QTY2)에 대한 평균을 낼 경우 3개의 행에 대한 평균이므로 6/3 = 2로 예상이 되지만 실제로는 3이라는 결괏값이 나오게 됩니다.

 

잘못된 집계 값 예 쿼리

 

SELECT AVG(QTY2) AS SUM_QTY FROM #TEMP_TEST_TABLE

 

잘못된 결과

 

 

이는 ID가 'ID0002'인 2행의 QTY2 값이 NULL이기에 집계 함수는 NULL에 대해 제외하고 처리하므로 3개 행이 아닌 2개 행에 대한 평균인 6/2 = 3 인 결괏값이 나오게 됩니다.

 

문제 5. 해결방법

 

AVG 함수 안 숫자 컬럼을 ISNULL 처리하여 NULL이어도 계산되도록 처리

 

SELECT AVG(ISNULL(QTY2,0)) AS SUM_QTY FROM #TEMP_TEST_TABLE

 

올바른 결과

 

 

수량 2(QTY2)가 NULL이었던 행에 0을 넣음으로써 집계 함수가 계산하도록 처리하여 기댓값인 6/3 = 2가 나옴을 알 수 있습니다.

---

결론

 

언젠가는 다뤄보고 싶었던 주제를 드디어 정리하여 올리게 되었습니다. 이를 통해 SQL 작성 시 NULL데이터로 인해 발생할 수 있는 이슈들을 정리해볼 수 있는 좋은 계기가 되었습니다. 데이터에 NULL이 포함되어있을 경우 위와 같은 이슈사항들이 발생할 수 있기에 위의 내용들을 되돌아보며 우리의 소중한 데이터 친구? 들을 더욱 조심히 다뤄야겠네요!