Create a Cartesian product

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.