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

Basics Creating a Document Head and Body Elements Formatting Text Creating Lists Embedding Images URLs Explained Linking to Documents Creating Tables Forms Form Basics Input and Textarea Select Lists Frames Framesets The Frame Element Nested Framesets Targetting Frames Inline Frames		Tips and Tricks Meta Tags Transition Effects HTML Generators Create a Document Create a List Create a Table

Basics Implementing CSS CSS Syntax Pseudo Classes/Elements CSS Classes CSS Properties Font Properties Color and Background Text Properties Border Properties Margins and Padding Size and Position		Tips and Tricks Menu Buttons Special Effects

Basics Running a Script Variables Expressions and Operators Objects.Properties.Methods The Date Object Strings Regular Expressions Defining RegExp Patterns Branches and Conditions Loops Arrays Array Basics Array Methods Sorting Arrays User-Defined Functions Cookies Windows Frames		Tips and Tricks Image Replacement Using Includes Form Validation Debugging

Basics Creating a Script Running a Script Variables Expressions and Operators Strings Strings Basics Strings and Substrings Replacing Substrings Regular Expressions Branches and Conditions Loops Arrays Array Basics Array Functions Sorting Arrays User-Defined Functions Include and Require Uploading Files File Functions Session Variables		Tips and Tricks Page Templates Form Reply Scripts Form Validation JavaScript to PHP