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?