Join on Two Tables

The Purpose of a JOIN

A database is rarely useful if you can only look at one table at a time. Real questions — like "What is the price of a white shirt?" — require data from two different tables. The JOIN operation is how you combine rows from two tables based on a related column between them.

This is fundamentally different from a Cartesian product. A Cartesian product pairs every row of the first table with every row of the second, producing all possible combinations — most of which are meaningless. A JOIN, by contrast, pairs only those rows that satisfy a specified condition, so you get only the meaningful, related data.

The Tables Used as an Example

To understand JOINs, the textbook works with two tables in a database called SchoolUniform: UNIFORM (Table 1.17 — UCode, UName, UColor) and COST (Table 1.18 — UCode, Size, Price). In the UNIFORM table, UCode is the primary key. In the COST table, UCode and Size together form a composite key. The column UCode is the common attribute that links the two tables — it should be defined as a foreign key in the COST table. This is the column you use to fetch related data from both tables.

Three Ways to Write the Same JOIN

The textbook shows a query that lists UCode, UName, UColor, Size, and Price for related tuples. It presents three different SQL approaches that all produce the same result.

Method (a): Using a Condition in the WHERE Clause

mysql> SELECT * FROM UNIFORM U, COST C WHERE U.UCode = C.UCode;

This is the older, implicit style of joining. You list both tables in the FROM clause and put the join condition in the WHERE clause. Because the column UCode exists in both tables, you must use a table alias (here, U for UNIFORM and C for COST) to remove ambiguity — the qualifier tells SQL which table's UCode you mean. The output — shown in Table 1.19 — has UCode appearing twice, once from each table, with identical values in each row. That is redundant.

Method (b): Explicit Use of the JOIN Clause

mysql> SELECT * FROM UNIFORM U JOIN COST C ON U.Ucode = C.Ucode;

This is the modern, explicit style. The JOIN keyword is used in the FROM clause, and the condition is given with the ON clause. No condition is needed in the WHERE clause. The output is exactly the same as in method (a), shown in Table 1.19.

Method (c): Explicit Use of NATURAL JOIN

mysql> SELECT * FROM UNIFORM NATURAL JOIN COST;

NATURAL JOIN is a special variant of JOIN. It works exactly like a JOIN with an ON clause, but it automatically identifies the common column(s) between the two tables and joins on them. Its key advantage is that it removes the redundant column from the output — the common attribute appears only once:

The result is the same as before, except that UCode now appears only once. This is cleaner and more readable.

Key Points to Remember When Using JOINs

The textbook lists several important rules for applying JOIN operations on two or more tables:

• If you are joining two tables on an equality condition on a common attribute, you can use either JOIN with an ON clause or NATURAL JOIN in the FROM clause.
• If you need to join three tables on equality conditions, you will need two JOIN or NATURAL JOIN clauses.
• In general, to combine N tables on equality conditions, you need N-1 joins. …