These 2 queries should return identical results, but because of an incorrect definition of EXISTS in ANSI SQL 89, the EXISTS formulation returns incorrect results when nulls are present. ANSI SQL 89 defines EXISTS as returning only 2 possible values - True or False. EXISTS should return 3 possible values - True, False or Unknown. This is the EXISTS bug.
Actually, the SQL problem is larger than Date indicates, for two reasons :
is converted to,
Note: there are other aspects to this conversion that are not shown here.
These RDBMS's may exhibit the EXISTS bug on other subqueries.
The SQL query,
There are RDBMS's supporting SQL that don't have these weaknesses. The FirstSQL DBMS does not have the EXISTS (or other subquery) bug. It correctly produces 3 possible logic values for EXISTS and other subqueries. FirstSQL also supports the MAYBE operator for properly detecting Unknown logic results. The MAYBE operator can be used anywhere that NOT can be used, including within constructs - MAYBE BETWEEN, MAYBE IN, MAYBE LIKE.
These problems with SQL should be corrected in all RDBMS's, otherwise Date's predictions may come true - planes will fall, bridges will collapse, etc.. Even SQL RDBMS's not supporting ANSI SQL may need repair.
SQL-92 does support a form of MAYBE - IS UNKNOWN. MAYBE can be transformed into SQL-92 as follows,
In the next section, various schemes for Default Values are discussed.
Return to Contents Page: In Defense of Nulls Return to Issues Page