Null Handling in SQLite

NULL Handling in SQLite

Small. Fast. Reliable. Choose any three.

Home Menu About Documentation Download License Support Purchase

About Documentation Download Support Purchase

Search Documentation Search Changelog

NULL Handling in SQLite Versus Other Database Engines

The goal is to make SQLite handle NULLs in a standards-compliant way. But the descriptions in the SQL standards on how to handle NULLs seem ambiguous. It is not clear from the standards documents exactly how NULLs should be handled in all circumstances.

So instead of going by the standards documents, various popular SQL engines were tested to see how they handle NULLs. The idea was to make SQLite work like all the other engines. An SQL test script was developed and run by volunteers on various SQL RDBMSes and the results of those tests were used to deduce how each engine processed NULL values. The original tests were run in May of 2002. A copy of the test script is found at the end of this document.

SQLite was originally coded in such a way that the answer to all questions in the chart below would be "Yes". But the experiments run on other SQL engines showed that none of them worked this way. So SQLite was modified to work the same as Oracle, PostgreSQL, and DB2. This involved making NULLs indistinct for the purposes of the SELECT DISTINCT statement and for the UNION operator in a SELECT. NULLs are still distinct in a UNIQUE column. This seems somewhat arbitrary, but the desire to be compatible with other engines outweighed that objection.

It is possible to make SQLite treat NULLs as distinct for the purposes of the SELECT DISTINCT and UNION. To do so, one should change the value of the NULL_ALWAYS_DISTINCT #define in the sqliteInt.h source file and recompile.

Update 2003-07-13: Since this document was originally written some of the database engines tested have been updated and users have been kind enough to send in corrections to the chart below. The original data showed a wide variety of behaviors, but over time the range of behaviors has converged toward the PostgreSQL/Oracle model. The only significant difference is that Informix and MS-SQL both treat NULLs as indistinct in a UNIQUE column.

The fact that NULLs are distinct for UNIQUE columns but are indistinct for SELECT DISTINCT and UNION continues to be puzzling. It seems that NULLs should be either distinct everywhere or nowhere. And the SQL standards documents suggest that NULLs should be distinct everywhere. Yet as of this writing, no SQL engine tested treats NULLs as distinct in a SELECT DISTINCT statement or in a UNION.

The following table shows the results of the NULL handling experiments.

SQLite PostgreSQL Oracle Informix DB2 MS-SQL OCELOT

Adding anything to null gives null Yes Yes Yes Yes Yes Yes Yes

Multiplying null by zero gives null Yes Yes Yes Yes Yes Yes Yes

nulls are distinct in a UNIQUE column Yes Yes Yes No (Note 4) No Yes

nulls are distinct in SELECT DISTINCT No No No No No No No

nulls are distinct in a UNION No No No No No No No

"CASE WHEN null THEN 1 ELSE 0 END" is 0? Yes Yes Yes Yes Yes Yes Yes

"null OR true" is true Yes Yes Yes Yes Yes Yes Yes

"not (null AND false)" is true Yes Yes Yes Yes Yes Yes Yes

MySQL 3.23.41 MySQL 4.0.16 Firebird SQL Anywhere Borland Interbase

Adding anything to null gives null Yes Yes Yes Yes Yes

Multiplying null by zero gives null Yes Yes Yes Yes Yes

nulls are distinct in a UNIQUE column Yes Yes Yes (Note 4) (Note 4)

nulls are distinct in SELECT DISTINCT No No No (Note 1) No No

nulls are distinct in a UNION (Note 3) No No (Note 1) No No

"CASE WHEN null THEN 1 ELSE 0 END" is 0? Yes Yes Yes Yes (Note 5)

"null OR true" is true Yes Yes Yes Yes Yes

"not (null AND false)" is true No Yes Yes Yes Yes

Notes: 1. Older versions of firebird omit all NULLs from SELECT DISTINCT and from UNION.

2. Test data unavailable.

3. MySQL version 3.23.41 does not support UNION.

4. DB2, SQL Anywhere, and Borland Interbase do not allow NULLs in a UNIQUE column.

5. Borland Interbase does not support CASE expressions.

The following script was used to gather information for the table above.

-- 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 drh@hwaci.com, 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...

Null Handling in SQLite

Related Articles

(no title)

Scientists reverse brain aging, with a nasal spray

AI has torched the market for junior programmers

Al Vigier: Canada's AI strategy shouldn't include secret Palantir bills

The Anatomy of an AI-Native Org