NULL 데이터가 포함된 데이터 사용 시 주의점
- Version : SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016
SQL Server에서 NULL은 0 값도 아니며 공백도 아닌 그냥 빈 값이다. 데이터를 조회할 때 NULL 값이 포함된 경우 사용자가 원하는 값이 조회되지 않을 수 있다. NULL이 포함된 데이터 조회시 어떻게 값이 다르게 표현되는지 알아본다.
실습에 사용된 옵션은 시스템 기본값인 SET ANSI_NULLS ON 상태이다.
아래 스크립트는 두 개의 테이블을 생성하여 #TestTableB 테이블에만 NULL값이 포함된 데이터를 입력 한다.
--Creating the first temporary table for testing IF(OBJECT_ID('tempdb..#TestTableA') IS NOT NULL) DROP TABLE #TestTableA
CREATE TABLE #TestTableA (ID INT)
--Creating the second temporary table for testing IF(OBJECT_ID('tempdb..#TestTableB') IS NOT NULL) DROP TABLE #TestTableB
CREATE TABLE #TestTableB (ID INT)
--Inserting data INSERT INTO #TestTableA(ID) VALUES (1),(2),(5),(9),(3) INSERT INTO #TestTableB(ID) VALUES (2),(7),(NULL),(9) |
[NOT IN 주의]
쿼리는 서브쿼리를 사용하여 #TestTableB 테이블에 포함되어 있지 않는 데이터를 #TestTableA 에서 조회한다. 조회된 결과가 예상했던 결과인가? #TestTableB 테이블의 NULL 값으로 인해 빈 결과 집합을 생성한다.
--Checking the result of the query SELECT ID FROM #TestTableA WHERE ID NOT IN (SELECT ID FROM #TestTableB) |
[집계 함수 사용시 주의]
NULL 값이 포함된 경우 집계 함수에서 결과가 다르게 표시될 수 있다. Count(*) 과 Count(Column) 의 조회 결과가 어떻게 다른지 알아본다. 테스트 테이블을 생성한다.
--Creating temporary table for testing IF(OBJECT_ID('tempdb..#TestTable') IS NOT NULL) DROP TABLE #TestTable GO
CREATE TABLE #TestTable ( ID INT ) |
Count(id), count(*)의 집계 결과를 조회한다. 결과가 다른 것을 확인할 수 있다. 집계 함수에 컬럼을 사용할 경우 NULL 값은 무시되는 것을 확인할 수 있다.
--Inserting data for testing INSERT INTO #TestTable(ID) VALUES (1),(2),(NULL),(4)
SELECT COUNT(ID) AS 'The result of Count(ID)' FROM #TestTable
SELECT COUNT(*) AS 'The result of Count(*)' FROM #TestTable |
--Inserting only NULLs TRUNCATE TABLE #TestTable
INSERT INTO #TestTable(ID) VALUES (NULL),(NULL),(NULL),(NULL)
SELECT COUNT(ID) AS 'The result of Count(ID) when ID column contains only NULL values' FROM #TestTable
SELECT COUNT(*) AS 'The result of Count(*) when ID column contains only NULL values' FROM #TestTable |
--Min , Max, AVG TRUNCATE TABLE #TestTable
INSERT INTO #TestTable(ID) VALUES (1),(2),(NULL),(4),(5)
SELECT AVG(ID) AS Average FROM #TestTable |
[변수 사용시 주의]
SET을 사용하여 변수에 값을 할당 할 때 SELECT 방법에 따라 결과 값이 다르게 나타난다.
DECLARE @var INT= 0
--Creating temporary table for testing IF(OBJECT_ID('tempdb..#TestTable') IS NOT NULL) DROP TABLE #TestTable
CREATE TABLE #TestTable (ID INT)
--Inserting data for testing INSERT INTO #TestTable(ID) VALUES (1),(2),(4)
--Using SELECT SELECT @var=ID FROM #TestTable WHERE ID=3
SELECT @var AS 'Variable value after SELECT'
--Using SET SET @var = ( SELECT ID FROM #TestTable WHERE ID=3 )
SELECT @var AS 'Variable value after SET' |
DECLARE @var1 INT, @var2 INT=7
SET @var2 = @var1 + @var2
SELECT @var2 As Variable2 |
[Group by 주의]
NULL을 포함한 그룹화에 NULL 값은 결과 집합의 하나로 포함된다.
IF(OBJECT_ID('tempdb..#TestTable') IS NOT NULL) DROP TABLE #TestTable GO
CREATE TABLE #TestTable ( ID INT, Value INT )
INSERT INTO #TestTable(ID, Value) VALUES (1, 10),(2, 20),(NULL, 70),(NULL, 90),(4, 50),(5, 60)
SELECT ID, AVG(Value) AS Average, MAX(Value) As Maximum FROM #TestTable GROUP BY ID |
[<> (!=) 비교]
비교 연산자의 경우 대부분 NULL 값과 상수 비교 시 동일하지 않기 때문에 아래 결과가 ture 일거라고 생각한다. 하지만 알 수 없는 값과 비교 시 항상 false를 반환한다. (SET ANSI_NULLS OFF 경우 결과는 다르게 나타난다.)
DECLARE @a INT=1, @b INT
SELECT @a AS a, @b AS b
IF(@a <> @b) SELECT 1 AS result ELSE SELECT 0 AS result |
NULL 작업을 할 때 일부 혼란스러운 경우가 있으므로 반드시 NULL 데이터 여부를 확인하고 원하는 값을 조회 할 수 있도록 특성을 파악해서 사용하는 것이 중요하다.
[참고자료]
강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp
