Set operations and joins

Set operations combine the results of two tables, which must have the same number of attributes, with matching domains:

• UNION: rows that are in either table, with repeated rows shown once.
• INTERSECT: rows that are in both tables. For example, students who take both DANCE and MUSIC.
• MINUS: rows in the first table but not the second.

The Cartesian product of two tables pairs every row of the first with every row of the second. Its degree is the sum of the two degrees, and its cardinality is the product of the two cardinalities. For example, if one table has 3 columns and 3 rows, and another has 2 columns and 6 rows, their product has 5 columns and 18 rows. Most of these combined rows are meaningless, so we usually want only some of them.

A JOIN keeps only the rows that match a condition, usually where the primary key of one table equals the foreign key of the other:

SELECT ... FROM UNIFORM U JOIN COST C ON U.UCode = C.UCode;

(U and C are short aliases for the table names.) The same result can be written with WHERE U.UCode = C.UCode, after listing both tables in FROM.

A NATURAL JOIN joins on the attribute the two tables have in common, and shows that attribute only once.

To join N tables on equality conditions, you need N − 1 join conditions.