SQLite의 NULL 처리

SQLite의 NULL 처리 (다른 데이터베이스 엔진과의 비교)

SQLite는 NULL을 표준에 부합하는 방식으로 처리하는 것을 목표로 해요. 하지만 SQL 표준 문서의 NULL 처리 설명은 모호해 보여요. 이 문서는 다양한 SQL 엔진 실험을 통해 SQLite가 NULL을 어떻게 처리하는지 보여줘요.

출처: NULL Handling in SQLite Versus Other Database Engines 문서

본문

목표는 SQLite가 NULL을 표준에 부합하는 방식으로 처리하게 하는 거예요. 하지만 SQL 표준에서 NULL 처리 방법에 대한 설명은 모호해 보여요. 표준 문서만으로는 모든 상황에서 NULL을 정확히 어떻게 처리해야 하는지 명확하지 않아요.

그래서 표준 문서를 따르는 대신, 여러 인기 SQL 엔진이 NULL을 어떻게 처리하는지 테스트했어요. 아이디어는 SQLite가 다른 모든 엔진처럼 동작하게 만드는 것이었어요. SQL 테스트 스크립트가 개발되어 자원봉사자들이 다양한 SQL RDBMS에서 실행했고, 그 테스트 결과로 각 엔진이 NULL 값을 어떻게 처리하는지 추론했어요. 원래 테스트는 2002년 5월에 실행됐어요. 테스트 스크립트 사본은 이 문서 끝에 있어요.

SQLite는 원래 아래 차트의 모든 질문에 대한 답이 "Yes"가 되도록 코딩됐어요. 하지만 다른 SQL 엔진에서 실행한 실험은 어느 것도 이렇게 동작하지 않는다는 것을 보여줬어요. 그래서 SQLite는 Oracle, PostgreSQL, DB2와 동일하게 동작하도록 수정됐어요. 여기에는 SELECT DISTINCT 문과 SELECT의 UNION 연산자 목적상 NULL을 구별하지 않게(indistinct) 만드는 것이 포함됐어요. NULL은 여전히 UNIQUE 열에서는 구별돼요. 이는 다소 임의적으로 보이지만, 다른 엔진과 호환되려는 바람이 그 반대 의견보다 우세했어요.

SELECT DISTINCT와 UNION의 목적상 NULL을 구별하도록(distinct) SQLite를 만들 수도 있어요. 그러려면 sqliteInt.h 소스 파일에 있는 NULL_ALWAYS_DISTINCT #define 값을 바꾸고 다시 컴파일하면 돼요.

업데이트 2003-07-13: 이 문서가 원래 작성된 이후 테스트된 일부 데이터베이스 엔진이 업데이트되었고, 사용자들이 아래 차트에 정정을 보내주었어요. 원래 데이터는 매우 다양한 동작을 보여줬지만, 시간이 지나면서 동작 범위는 PostgreSQL/Oracle 모델 쪽으로 수렴했어요. 유일한 중요한 차이는 Informix와 MS-SQL이 모두 UNIQUE 열에서 NULL을 구별하지 않게 취급한다는 점이에요.

UNIQUE 열에서는 NULL이 구별되지만 SELECT DISTINCT와 UNION에서는 구별되지 않는다는 사실은 여전히 의아해요. NULL은 모든 곳에서 구별되거나 어디에서도 구별되지 않아야 할 것 같아요. 그리고 SQL 표준 문서는 NULL이 모든 곳에서 구별되어야 한다고 시사해요. 하지만 이 글을 쓰는 시점에 테스트한 어떤 SQL 엔진도 SELECT DISTINCT 문이나 UNION에서 NULL을 구별하지 않아요.

다음 표는 NULL 처리 실험 결과를 보여줘요.

위 표의 정보를 수집하는 데 사용된 스크립트는 다음과 같아요.

-- I have about decided that SQL's treatment of NULLs is capricious and cannot be
-- deduced by logic.  It must be discovered by experiment.  To that end, I have 
-- prepared the following script to test how various SQL databases deal with NULL.
-- My aim is to use the information gathered from this script to make SQLite as
-- much like other databases as possible.
--
-- If you could please run this script in your database engine and mail the results
-- to me at [email protected], that will be a big help.  Please be sure to identify the
-- database engine you use for this test.  Thanks.
--
-- If you have to change anything to get this script to run with your database
-- engine, please send your revised script together with your results.
--

-- Create a test table with data
create table t1(a int, b int, c int);
insert into t1 values(1,0,0);
insert into t1 values(2,0,1);
insert into t1 values(3,1,0);
insert into t1 values(4,1,1);
insert into t1 values(5,null,0);
insert into t1 values(6,null,1);
insert into t1 values(7,null,null);

-- Check to see what CASE does with NULLs in its test expressions
select a, case when b<>0 then 1 else 0 end from t1;
select a+10, case when not b<>0 then 1 else 0 end from t1;
select a+20, case when b<>0 and c<>0 then 1 else 0 end from t1;
select a+30, case when not (b<>0 and c<>0) then 1 else 0 end from t1;
select a+40, case when b<>0 or c<>0 then 1 else 0 end from t1;
select a+50, case when not (b<>0 or c<>0) then 1 else 0 end from t1;
select a+60, case b when c then 1 else 0 end from t1;
select a+70, case c when b then 1 else 0 end from t1;

-- What happens when you multiply a NULL by zero?
select a+80, b*0 from t1;
select a+90, b*c from t1;

-- What happens to NULL for other operators?
select a+100, b+c from t1;

-- Test the treatment of aggregate operators
select count(*), count(b), sum(b), avg(b), min(b), max(b) from t1;

-- Check the behavior of NULLs in WHERE clauses
select a+110 from t1 where b<10;
select a+120 from t1 where not b>10;
select a+130 from t1 where b<10 OR c=1;
select a+140 from t1 where b<10 AND c=1;
select a+150 from t1 where not (b<10 AND c=1);
select a+160 from t1 where not (c=1 AND b<10);

-- Check the behavior of NULLs in a DISTINCT query
select distinct b from t1;

-- Check the behavior of NULLs in a UNION query
select b from t1 union select b from t1;

-- Create a new table with a unique column.  Check to see if NULLs are considered
-- to be distinct.
create table t2(a int, b int unique);
insert into t2 values(1,1);
insert into t2 values(2,null);
insert into t2 values(3,null);
select * from t2;

drop table t1;
drop table t2;

더 알아보기 (Learn more)

• SQLite의 Quirks, Caveats, and Gotchas
• SELECT DISTINCT 문
• UNIQUE 제약