Handle Nulls Properly? (3)

Three Valued Results - MAYBE Queries

When columns containing nulls are accessed in a logical expression, relational systems use 3 Valued Logic (3VL). 3 Valued Logic has been criticized as being difficult to understand and counterintuitive. The main argument is that 3VL does not give the results expected with classical logic, which has 2 logic value - True and False. Using 2 Value logic, a query partitions the rows it processes into 2 sets:

Rows where the query is True; these rows are included in the result set.
Rows where the query is False; these rows are not included in the result set.

When the database contains rows with missing data (the row contains columns that are null), tests on these columns cannot yield True or False values because the information is missing - the system doesn't definitely know if the the test is true or false. In this case, a 3rd logical value is used - Unknown. A query using 3 Valued Logic partitions the rows into 3 sets:

Rows where the query is True; these rows are included in the result set.
Rows where the query is False; these rows are not included in the result set.
Rows where the query is Unknown; these rows are also not included in the result set.

This is a 3 Valued Result. Much of the confusion about 3VL comes from not recognizing that - results are 3 valued when information is missing. This is illustrated with the Example 3 query (FirstSQL Test Drive). This query returns rows for suppliers who supply part P1 but definitely in quantities of 1000, the NOT operator is removed before the EXISTS. Example 4 shows this formulation. Click on FirstSQL Test Drive: Example 4 for a demonstration. The correct result is a single row - S3.

Most RDBMSs do not provide a convenient way to retrieve suppliers who supply part P1 but it is not known if they supply P1 in quantities of 1000. FirstSQL provides the MAYBE operator for this purpose. Replacing the NOT operator with a MAYBE operator before the EXISTS in Example 3 will retrieve this result. Example 5 shows this formulation. Click on FirstSQL Test Drive: Example 5 for a demonstration. The correct result is a single row - S1.

The three forms of Date's query - 1) NOT EXISTS, 2) EXISTS without the NOT and 3) MAYBE EXISTS, retrieve the three distinct result sets implied by the original query. The three forms are illustrated with Examples 3, 4, 5 in FirstSQL Test Drive. Try these queries with your database system!

The relational model defines more than one type of null, representing more than one type of missing information. This facility is is unique to FirstSQL. Other RDBMSs only support one type of null. The next section, Two Types of Nulls, describes FirstSQL's innovative facility.

Return to Contents Page: Handle Nulls Properly? Return to Can your Database do this?