Combining Tables — Cartesian Product, Equi-Join and UNION

A well-designed relational database keeps data in separate related tables to avoid duplication — for example, our Employee table and our Dept table. But a report often needs information from more than one table at once, such as each employee's name together with the city their department is in. Bringing data from two tables into a single result is done with a join, and it relies on the shared column that links them (Employee.Dept matches Dept.DeptName).

Cartesian product (cross join). If we simply select from two tables without saying how they are related, MySQL pairs every row of the first table with every row of the second. This is the cartesian product (or cross join):

SELECT * FROM Employee, Dept;

With 6 employees and 3 departments this produces 6 x 3 = 18 rows — most of them meaningless (for example, pairing Aarti of Sales with the Accounts department). The cartesian product is rarely wanted on its own; it is really the raw starting point that a join then filters. In general, the cartesian product of a table with m rows and a table with n rows has m x n rows.

Equi-join. An equi-join keeps only those paired rows in which the linking columns are equal — that is, it adds a condition matching the foreign key to the primary key. This gives the meaningful combination we actually want:

SELECT Employee.Name, Employee.Dept, Dept.City
FROM Employee, Dept
WHERE Employee.Dept = Dept.DeptName;

Now each employee appears once, matched to the correct city — for example Aarti Behera / Sales / Cuttack, and Manoj Nayak / IT / Bhubaneswar. Because the join condition uses the equality (=) operator, it is called an equi-join. The same query may also be written with the explicit JOIN ... ON form, which is clearer:

SELECT E.Name, E.Dept, D.City
FROM Employee AS E
JOIN Dept AS D ON E.Dept = D.DeptName;

(Here E and D are short table aliases that save typing.) When the same column name exists in both tables, we write TableName.Column (or Alias.Column) to say which one we mean. …