Join on Two Tables

Understanding JOINs: Combining Data from Two Tables

A database is useful because it stores related data across multiple tables. But to answer real questions, you often need to bring that data back together. That is exactly what a JOIN operation does — it combines rows from two (or more) tables based on a condition you specify.

The key idea is that a JOIN is not a Cartesian product. A Cartesian product pairs every row of one table with every row of the other, producing all possible combinations — most of which are meaningless. A JOIN, by contrast, pairs only those rows that satisfy a given condition, so you get only the meaningful, related combinations.

The Condition: Linking Tables Through Common Attributes

When you JOIN two tables, you almost always use a condition that compares a common attribute — typically the primary key of one table and the foreign key of the other. This is how the relationship between the tables is expressed in the query.

Consider two tables in a database called SchoolUniform:

UNIFORM (UCode, UName, UColor) — UCode is the primary key.

COST (UCode, Size, Price) — UCode and Size together form a composite key. UCode here is a foreign key referencing UNIFORM.

Notice that the COST table does not have a row for UCode 3 (Tie). That matters when you see the output of a JOIN — only matching rows appear.

Three Ways to Write the Same JOIN

The textbook shows that you can write a query to list UCode, UName, UColor, Size, and Price from both tables in three different ways. All three produce the same result.

a) Using a condition in the WHERE clause

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

Here, you list both tables in the FROM clause, separated by a comma, and then specify the join condition in the WHERE clause. Because UCode exists in both tables, you must qualify it with the table name (or an alias) to remove ambiguity. The aliases U and C are used for that purpose.

The output is:

Four rows are returned. Each row from UNIFORM is paired with every matching row from COST (based on UCode). The UCode column appears twice — once from each table — which is redundant.

b) Using the explicit JOIN clause

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

This is the modern, preferred syntax. The JOIN keyword is used explicitly in the FROM clause, and the condition is given in an ON clause. No condition is needed in the WHERE clause. The output is identical to the previous query.

c) Using NATURAL JOIN

mysql> SELECT * FROM UNIFORM NATURAL JOIN COST;

NATURAL JOIN is a special variant. It works like a JOIN on equality, but it automatically identifies the common attribute(s) between the two tables and removes the redundant column from the result. The output has UCode appearing only once:

This is cleaner and more readable. However, NATURAL JOIN works only when the two tables share exactly one common attribute with the same name. If they share more than one, it joins on all of them automatically.

Important Points to Remember About JOINs

The textbook summarises a few general rules:

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