Home > MySQL > Join Queriesprinter version

MySQL Join Queries

This tutorial assumes that you have access to the MySQL command prompt and uses the people table created in the Create Tutorial.

A join query is one that outputs rows based on a join condition in a WHERE clause used on two or more tables.

To demonstrate joins an additional table called payroll has been added to the database and the contents of people and payroll are shown below using a SELECT query.

Examples of Select Statements

Below is a join query that returns job, age and salary using data from the people and payroll table.

Example of a Join Query

mysql> SELECT job, age, salary FROM people, payroll WHERE people.id = payroll.id;
+----------+---+------+
|job       |age|salary|
+----------+---+------+
|Programmer|40 |20010 |
|Manager   |36 |25030 |
|Operator  |51 |12400 |
+----------+---+------+
3 rows in set (0.10 sec)

mysql>

For a join query to work the following features must be observed.

If the same attribute occurs in more than one table then the attribute name must be preceded a table name and a period e.g. people.lastname.

Otherwise the query will return an error stating that the attribute is ambiguous.
The FROM clause must contain more than one table name separated by commas.
The WHERE clause must match single rows in multiple tables otherwise the query will return a Cartesian Product.

A Cartesian Product

A Cartesian Product is returned when a Cartesian join is used which returns all possible combinations of the attributes in each table.

In the WHERE clause in the query above if people.lastname and payroll.lastname had been used instead of people.id and payroll.id it would have returned all combinations of these attributes - which is not what is required.

Example of a Cartesian Join

mysql> SELECT job, age, salary FROM people, payroll WHERE people.lastname = payroll.lastname;
+----------+---+------+
|job       |age|salary|
+----------+---+------+
|Programmer|40 |20010 |
|Manager   |36 |25030 |
|Manager   |51 |25030 |
|Operator  |36 |12400 |
|Operator  |51 |12400 |
+----------+---+------+
5 rows in set (0.11 sec)

mysql>

The result above implies that there are two managers and two operators aged, 36 and 51 which isn't the case.

With join queries you must be careful to avoid the Cartesian Product if you want sensible results.

Previous - Querying Next - Functions