Comparing with NULL in SQL

Question

ANSI-92 SQL indicates that comparisons with NULL are evaluated as false, for example:

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

Both do not return strings because NULL cannot be compared in this way. Instead, the IS NULL and IS NOT NULL predicates should be used:

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

Research showed me that Oracle ¹ PostgreSQL, MySQL, and SQLite support ANSI syntax. Add DB2 and Firebird to this list.

Besides the fact that SQL Server with ANSI_NULLS disabled, what other DBMS supports syntax other than ANSI?

¹ All empty string = NULL clutter.

Answer 1

Here is a good comparison of zero processing in SQLite, PostgreSQL, Oracle, Informix, DB2, MS-SQL, OCELOT, MySQL 3.23. 41, MySQL 4.0.16, Firebird, SQL Anywhere, and Borland Interbase

Answer 2

For what it's worth, comparing something with NULL is not strictly false, it is unknown. In addition, NOT unknown.

ANSI SQL-99 defines the IS [NOT] DISTINCT FROM predicate. This allows you to mix null values ​​and non-zero values ​​in comarisons and always get true or false. Null compared to null in this case is true, otherwise any non-zero compared to zero false. Thus, denial works as you probably expect.

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

MySQL has a similar operator with zero security <=> , which returns true if the operands are the same and false if they are different.

Oracle has the most difficult way. You must become creative using NVL() or Boolean expressions:

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

Ugh.