Comparisons with NULLs in SQL

sql null ansi-sql
NullUserException picture NullUserException · Sep 9, 2011 · Viewed 8.5k times · Source

ANSI-92 SQL mandates that comparisons with NULL evaluate to "falsy," eg:

SELECT * FROM table WHERE field = NULL
SELECT * FROM table WHERE field != NULL

Will both return no rows because NULL can't be compared like that. Instead, the predicates IS NULL and IS NOT NULL have to be used instead:

SELECT * FROM table WHERE field IS NULL
SELECT * FROM table WHERE field IS NOT NULL

Research has shown me that Oracle 1, PostgreSQL, MySQL and SQLite all support the ANSI syntax. Add to that list DB2 and Firebird.

Aside from SQL Server with ANSI_NULLS turned off, what other RDBMS support the non-ANSI syntax?

1 The whole empty string = NULL mess notwithstanding.

Answer

Bill Karwin picture Bill Karwin · Sep 9, 2011

For what it's worth, comparing something to NULL is not strictly false, it's unknown. Furthermore, NOT unknown is still unknown.

ANSI SQL-99 defines a predicate IS [NOT] DISTINCT FROM. This allows you to mix nulls and non-null values in comarisons, and always get a true or false. Null compared to null in this way is true, otherwise any non-null compared to null is false. So negation works as you probably expect.

PostgreSQL, IBM DB2, and Firebird do support IS [NOT] DISTINCT FROM.

MySQL has a similar null-safe comparison operator <=> that returns true if the operands are the same and false if they're different.

Oracle has the hardest path. You have to get creative with use of NVL() or boolean expressions:

WHERE a = b OR (a IS NULL AND b IS NULL)

Yuck.

Related questions

Standard SQL boolean operator IS vs. equals (=) operator

On the Wikipedia page for SQL there are some truth tables about boolean logic in SQL. [1] The Wikipedia page seems to source the SQL:2003 standard. The truth table for the equals operator (=) is different from the IS operator from the …

Read More
How to use NULL or empty string in SQL

I would like to know how to use NULL and an empty string at the same time in a WHERE clause in SQL Server. I need to find records that have either null values or an empty string. Thanks.

Read More
Not equal <> != operator on NULL

Could someone please explain the following behavior in SQL? SELECT * FROM MyTable WHERE MyColumn != NULL (0 Results) SELECT * FROM MyTable WHERE MyColumn <> NULL (0 Results) SELECT * FROM MyTable WHERE MyColumn IS NOT NULL (568 Results)

Read More