Comparisons with NULLs in SQL

Question

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 ¹, 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?

¹ The whole empty string = NULL mess notwithstanding.

Quiz

Oracle (11g here) does exactly as you describe in both cases. – Fasciation

Answer 1

Here is a nice comparison of null handling in SQLite, PostgreSQL, Oracle, Informix, DB2, MS-SQL, OCELOT, MySQL 3.23.41, MySQL 4.0.16, Firebird, SQL Anywhere, and Borland Interbase

Auraaural

Answer 2

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.

Weisburgh

The writing between the lines is that there is no standard way to disable standard behavior. – Hukill

@Daniel I wasn't looking for a standard way at all; just any way (like SQL Server's SET ANSI_NULLS OFF) – Quiz

I'm not aware of similar configuration options in other RDBMS. I was just trying to suggest other solutions for doing null-safe comparisons. – Weisburgh

If you use Bill's solution, you get the behavior you want in a standard way. If you instead find vendor-specific ways of disabling standard behavior and do that, you get code that's doubly unportable: once for depending on a vendor extension, and again for using the wrong kinds of comparisons in all your statements. Strongly not recommended. – Hukill

afaik, the distinct from predicate is not exposed in LUW DB2. This is the flavor of db2 a lot of people see. – Elyseelysee

You can also use DECODE with Oracle: WHERE DECODE(a,b,1)=1 – Subulate