Actually, the wrong results in the example query are not produced by EXISTS but by the WHERE clause within the EXISTS subquery. ANSI SQL defines the WHERE clause as returning True or False, a 2 valued result rather than a 3 valued result including Unknown. In the subquery,
(SELECT * FROM sp spy WHERE spy.sno = spx.sno AND spy.sno = 'P1' AND spy.qty = 1000)The final sub-expression (spy.qty = 1000) produces an Unknown result if spy.qty is null, as when the variable spy is (S1,P1,NULL). This causes the result of the WHERE expression to be Unknown. Since the WHERE clause in ANSI SQL cannot return Unknown, it is transformed to False, causing S1 to be incorrectly included in the result set. The error is caused by the action of the WHERE clause, not the EXISTS!
Some ramifications of the larger problem are:
SELECT DISTINCT sno FROM sp WHERE pno = 'P1' AND sno NOT IN ( SELECT sno FROM sp WHERE pno = 'P1' AND qty = 1000) ;This query exhibits the same error as the EXISTS query for ANSI SQL and most RDBMSs. In other words, virtually any SQL operation involving a subquery can have this flaw when columns can be null.
Return to Contents Page: DBMSs Process Subqueries Correctly? Return to Issues Page