When you perform a multiple-table query that does not
explicitly state a join condition among the tables, you create a Cartesian
product. A Cartesian product consists of every possible combination
of rows from the tables. This result is usually large and unwieldy.
The
following query selects from two tables and produces a Cartesian product. Figure 1: Query
SELECT * FROM customer, state;
Although only 52 rows exist in the state table
and 28 rows in the customer table, the effect of the query
is to multiply the rows of one table by the rows of the other and
retrieve an impractical 1,456 rows, as the result shows. Figure 2: Query result
customer_num 101
fname Ludwig
lname Pauli
company All Sports Supplies
address1 213 Erstwild Court
address2
city Sunnyvale
state CA
zipcode 94086
phone 408-789-8075
code AK
sname Alaska
customer_num 101
fname Ludwig
lname Pauli
company All Sports Supplies
address1 213 Erstwild Court
address2
city Sunnyvale
state CA
zipcode 94086
phone 408-789-8075
code HI
sname Hawaii
customer_num 101
fname Ludwig
lname Pauli
company All Sports Supplies
address1 213 Erstwild Court
address2
city Sunnyvale
state CA
zipcode 94086
phone 408-789-8075
code CA
sname California
⋮
In addition, some of the data that is displayed
in the concatenated rows is contradictory. For example, although the city and state from
the customer table indicate an address in California, the code and sname from
the state table might be for a different state.