Cartesian Product on Two Tables

The Cartesian product is the operation that sits underneath every multi-table query in SQL. When you list more than one table in the FROM clause, separated by commas, the database engine first combines those tables by forming the Cartesian product — every possible pairing of rows from the first table with every row from the second. Only after that does it apply any WHERE condition you have written.

Consider two tables: DANCE and MUSIC. If you run:

SELECT * FROM DANCE, MUSIC;

the result is a single table containing all combinations of tuples from both tables. The degree (number of columns) of this result is the sum of the degrees of the two original tables. The cardinality (number of rows) is the product of their cardinalities. As established in Section 9.10.4, both DANCE and MUSIC have degree 3 (columns SNo, Name, Class); DANCE has a cardinality of 4 rows and MUSIC has a cardinality of 5 rows. So DANCE X MUSIC has degree 3 + 3 = 6 and cardinality 4 × 5 = 20, exactly as shown in Table 9.23.

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

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

This query uses table aliases — D for DANCE and M for MUSIC. Table aliases work just like column aliases (see Section 9.6.2): they give a shortened 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. The alias is valid only for that one query.

The output of the above query is a table with degree 6 and cardinality 2 — only the two rows where the name matched: