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).