How Joins are Processed

Knowing how joins are processed helps to understand them—and to figure out why, when you incorrectly state a join, you sometimes get unexpected results.

The first step in processing a join is to form the Cartesian product of the tables—all the possible combinations of the rows from each of the tables. The number of rows in a Cartesian product of two tables is equal to the number of rows in the first table multiplied by the number of rows in the second table.

The Cartesian product of the authors table and the publishers table is 69 (23 authors multiplied by 3 publishers). You can have a look at a Cartesian product with any query that includes columns from more than one table in the select list, more than one table in the from clause, and no where clause. For example, if you omit the where clause from the join used in any of the previous examples, SAP ASE combines each of the 23 authors with each of the 3 publishers, and returns all 69 rows.

select au_lname, au_fname
from authors, publishers

This Cartesian product does not contain any particularly useful information. It is actually misleading because it implies that every author in the database has a relationship with every publisher in the database—which is not true.

Including a where clause in the join specifies the columns to be matched and the basis on which to match them. It may also include other restrictions. Once SAP ASE forms the Cartesian product, it eliminates the rows that do not satisfy the join by using the conditions in the where clause.

For example, the where clause in the example cited (the Cartesian product of the authors table and the publishers table) eliminates from the results all rows in which the author’s city is not the same as the publisher’s city:

where authors.city = publishers.city