ALLBASE/SQL Reference Manual (36216-90216)
Chapter 3 115
SQL Queries
Simple Queries
In the example above, NULLs are included in the tables to show the difference between the
behavior of NULLs in the production of the Cartesian product and the behavior of NULLs
when a common column is speciļ¬ed in the WHERE clause join predicate.
Consider the following query:
SELECT p.PartNumber, PartName, c.PartNumber, Color
FROM Parts p, Colors c
WHERE p.PartNumber = c.PartNumber
The query result for the query is as follows:
SELECT p.PartNumber, PartName, c.PartNumber, Color FROM Parts p, Colors c....
---------------+------------+----------------+-------------------
PARTNUMBER |PARTNAME |PARTNUMBER |COLOR
---------------+------------+----------------+-------------------
3 |NULL | 3|Green
The only rows selected for the query result are those rows for which the join predicate
(p.PartNumber = c.PartNumber) evaluates to true. Because NULL has an undetermined
value, for the cases where the values of the predicate are NULL = NULL, the value of the
predicate is undetermined, and the row is not selected.
However, for the Cartesian product shown in the prior example, due to the absence of a
join predicate, rows with NULLs in the common column are selected because the operation
is the simple concatenation of the rows, regardless of value.