Cartesian Products

If a join condition is missing, you will end up selecting all rows from one table joined to all the rows in the other table, a situation known as a Cartesian product . When this occurs, you may end up with a lot of rows being displayed. For example, assume you had one table containing 50 rows and a second table containing 100 rows. If you select columns from those two tables without a join, you would get 5,000 rows returned. This is because each row from table 1 would be joined to each row in table 2, which would yield a total of 50 multiplied by 100 rows, or 5,000 rows.

The following example shows a subset of the rows from a Cartesian product between the product_types and products tables:

  SELECT pt.product_type_id, p.product_id FROM   product_types pt, products p;  PRODUCT_TYPE_ID PRODUCT_ID --------------- ----------  1 1  2 1  3 1  4 1  5 1  1 2  2 2  3 2  4 2  5 2  1 3 ...  5 11  1 12  2 12  3 12  4 12  5 12 60 rows selected. 

A total of 60 rows are selected because the product_types and products tables contain 5 and 12 rows, respectively (5 * 12 = 60).