Cartesian Product on Two Tables

The Cartesian product is the operation that underlies every query involving more than one table. When you list multiple tables in the FROM clause separated by commas, the database engine first combines them by taking every possible pairing of rows from the first table with every row from the second table. This combined result is a single temporary table that the rest of the query then works on.

Consider two tables: DANCE and MUSIC. The query SELECT * FROM DANCE, MUSIC; asks MySQL to produce all combinations of tuples from these two tables. The output is a table with degree 6 (the sum of the degrees of the two original tables) and cardinality 20 (the product of their row counts) — Table 1.15. Every row from DANCE is paired with every row from MUSIC, regardless of whether the data is related.

To make the result meaningful, you add a WHERE clause. For instance, to see only those rows where the Name attribute matches in both tables, you write:

SELECT * FROM DANCE D, MUSIC M WHERE D.Name = M.Name;

This query uses table aliases — D for DANCE and M for MUSIC. Aliases work just like column aliases: they give a short, convenient name to a table for the duration of the current query. Once you assign an alias in the FROM clause, you must use that alias everywhere in the query; the original table name cannot be used again in that same query. …