Example 3 under the FirstSQL Test Drive demonstrates proper EXISTS processing. The query should return 1 row - S2. For a demonstration, pick FirstSQL Test Drive: Example 3. A system exhibiting the error will return two rows - S1 and S2 for the query in Example 3 using the sp database shown by Example 1. P1 is not a valid response to the query:
SELECT DISTINCT spx.sno
FROM sp spx
WHERE spx.pno = 'P1'
AND NOT EXISTS
( SELECT spy.qty
FROM sp spy
WHERE spy.sno = spx.sno
AND spy.pno = 'P1'
AND spy.qty = 1000) ;
Queries that access missing information, like Example 3, divide their
basic result set into 3 distinct partitions, returning the contents of
only one partition as the final result set. Because of 3 Valued Logic,
they produce 3 Valued Results. The next section examines
3 Valued Results - MAYBE Queries.
Return to Contents Page: Handle Nulls Properly? Return to Can your Database do this?