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

Created May 28, 2014. Send feedback on this help topic to Technical Publications: pubs@sap.com