MySQL Joins

Joins are used for fetching data from two or more tables and written using SELECT statements.

We are going to use `employee` and `user` tables mentioned below for examples in this article. SQL commands for creating the tables and inserting data are available here.

id	first_name	last_name	job_title	salary
1	Robin	Jackman	Software Engineer	5500
2	Taylor	Edward	Software Architect	7200
3	Vivian	Dickens	Database Administrator	6000
4	Harry	Clifford	Database Administrator	6800
5	Eliza	Clifford	Software Engineer	4750
6	Nancy	Newman	Software Engineer	5100
7	Melinda	Clifford	Project Manager	8500
8	Harley	Gilbert	Software Architect	8000

id	employee_id	user_type	username	password
1		SUPER ADMIN	admin	admin
2	1	NORMAL	robin	robin
3	2	ADMIN	taylor	taylor
4	3	ADMIN	vivian	vivian
5	4	NORMAL	harry	harry
6	7	ADMIN	melinda	melinda
7	8	NORMAL	harley	harley

All the SQL commands mentioned in this article can be run in MySQL command-line, in a GUI tool or with mysqli_query() in a PHP script.

Result sets shown in the article were taken by running the commands in command-line.

INNER JOIN

Inner joins let us select rows that have same value in both tables for specified columns thereby returns matching rows. We specify the first table after FROM as in normal SELECT statements and the second table is specified after INNER JOIN.

Using ON clause we can specify the columns that should have matching values.

SELECT e.first_name, e.last_name, u.user_type, u.username FROM `employee` AS e INNER JOIN `user` AS u ON e.id = u.employee_id;

+------------+-----------+-----------+----------+
| first_name | last_name | user_type | username |
+------------+-----------+-----------+----------+
| Robin      | Jackman   | NORMAL    | robin    |
| Taylor     | Edward    | ADMIN     | taylor   |
| Vivian     | Dickens   | ADMIN     | vivian   |
| Harry      | Clifford  | NORMAL    | harry    |
| Melinda    | Clifford  | ADMIN     | melinda  |
| Harley     | Gilbert   | NORMAL    | harley   |
+------------+-----------+-----------+----------+

In above result set, you can see that only the employees who had a matching row in `user` table have been returned.

Table Order in Inner Joins

Since inner joins only return matching rows, it doesn’t matter which table you specify as the first table. Following two queries return same result.

SELECT e.first_name, e.last_name, u.user_type, u.username FROM `employee` AS e INNER JOIN `user` AS u ON e.id = u.employee_id;

SELECT e.first_name, e.last_name, u.user_type, u.username FROM `user` AS u INNER JOIN `employee` AS e ON e.id = u.employee_id;

JOIN and CROSS JOIN

JOIN (without INNER part) and CROSS JOIN work as same as INNER JOIN. Following two queries return same result set as corresponding INNER JOIN query.

SELECT e.first_name, e.last_name, u.user_type, u.username FROM `employee` AS e JOIN `user` AS u ON e.id = u.employee_id;

SELECT e.first_name, e.last_name, u.user_type, u.username FROM `employee` AS e CROSS JOIN `user` AS u ON e.id = u.employee_id;

Using WHERE Clause

Similar to normal SELECT statements, you can specify conditions on rows to be retrieved with a WHERE clause (applicable to other join types as well).

SELECT e.first_name, e.last_name, u.user_type, u.username FROM `employee` AS e INNER JOIN `user` AS u ON e.id = u.employee_id WHERE e.salary > 6000 AND u.user_type = 'ADMIN';

+------------+-----------+-----------+----------+
| first_name | last_name | user_type | username |
+------------+-----------+-----------+----------+
| Taylor     | Edward    | ADMIN     | taylor   |
| Melinda    | Clifford  | ADMIN     | melinda  |
+------------+-----------+-----------+----------+

Inner Joins Without INNER JOIN Keyword

It’s possible to write inner joins omitting INNER JOIN and ON keywords like below.

SELECT e.first_name, e.last_name, u.user_type, u.username FROM `employee` AS e, `user` AS u WHERE e.id = u.employee_id AND e.salary > 6000 AND u.user_type = 'ADMIN';

+------------+-----------+-----------+----------+
| first_name | last_name | user_type | username |
+------------+-----------+-----------+----------+
| Taylor     | Edward    | ADMIN     | taylor   |
| Melinda    | Clifford  | ADMIN     | melinda  |
+------------+-----------+-----------+----------+

Table names are specified after FROM keyword separated by commas.
Table relationships are mentioned in WHERE clause.

Since table relationships are mixed with other conditions in WHERE clause, this way of writing inner joins is less readable compared to writing with INNER JOIN and ON keywords.

LEFT JOIN

Left joins let you select all the rows from first table (left table) for specified relationship and fetch only the matching ones from second table (right table).

SELECT e.first_name, e.last_name, u.user_type, u.username FROM `employee` AS e LEFT JOIN `user` AS u ON e.id = u.employee_id;

+------------+-----------+-----------+----------+
| first_name | last_name | user_type | username |
+------------+-----------+-----------+----------+
| Robin      | Jackman   | NORMAL    | robin    |
| Taylor     | Edward    | ADMIN     | taylor   |
| Vivian     | Dickens   | ADMIN     | vivian   |
| Harry      | Clifford  | NORMAL    | harry    |
| Eliza      | Clifford  | NULL      | NULL     |
| Nancy      | Newman    | NULL      | NULL     |
| Melinda    | Clifford  | ADMIN     | melinda  |
| Harley     | Gilbert   | NORMAL    | harley   |
+------------+-----------+-----------+----------+

You can understand the generating of above result set in following two steps.

First MySQL looks at the table relationship and identifies `id` field of `employee` table as the left part and `employee_id` field of `user` table as the right part. It then selects all the rows from `employee` table that have values for `id` column.
In the second step, based on e.id = u.employee_id relationship, it fetches matching rows from `user` table and creates final rows set (These rows contain values from both `employee` and `user` tables). If it can’t find a matching row, NULL is set to the cells of `user` table.

Using IS NOT NULL operator, you can limit the result set so that it only includes final rows where right table cells have values.

SELECT e.first_name, e.last_name, u.user_type, u.username FROM `employee` AS e LEFT JOIN `user` AS u ON e.id = u.employee_id WHERE u.employee_id IS NOT NULL;

+------------+-----------+-----------+----------+
| first_name | last_name | user_type | username |
+------------+-----------+-----------+----------+
| Robin      | Jackman   | NORMAL    | robin    |
| Taylor     | Edward    | ADMIN     | taylor   |
| Vivian     | Dickens   | ADMIN     | vivian   |
| Harry      | Clifford  | NORMAL    | harry    |
| Melinda    | Clifford  | ADMIN     | melinda  |
| Harley     | Gilbert   | NORMAL    | harley   |
+------------+-----------+-----------+----------+

Similarly using IS NULL operator you can fetch employees who don’t have user accounts.

SELECT e.first_name, e.last_name, u.user_type, u.username FROM `employee` AS e LEFT JOIN `user` AS u ON e.id = u.employee_id WHERE u.employee_id IS NULL;

+------------+-----------+-----------+----------+
| first_name | last_name | user_type | username |
+------------+-----------+-----------+----------+
| Eliza      | Clifford  | NULL      | NULL     |
| Nancy      | Newman    | NULL      | NULL     |
+------------+-----------+-----------+----------+

RIGHT JOIN

Right joins work opposite to left joins. That is, priority is given to right table and fetches all the rows from right table for given relationship.

Following query returns all the rows from `user` table and fills NULL for `employee` table’s cells if no matching row found.

SELECT e.first_name, e.last_name, u.user_type, u.username FROM `employee` AS e RIGHT JOIN `user` AS u ON u.employee_id = e.id;

+------------+-----------+-------------+----------+
| first_name | last_name | user_type   | username |
+------------+-----------+-------------+----------+
| NULL       | NULL      | SUPER ADMIN | admin    |
| Robin      | Jackman   | NORMAL      | robin    |
| Taylor     | Edward    | ADMIN       | taylor   |
| Vivian     | Dickens   | ADMIN       | vivian   |
| Harry      | Clifford  | NORMAL      | harry    |
| Melinda    | Clifford  | ADMIN       | melinda  |
| Harley     | Gilbert   | NORMAL      | harley   |
+------------+-----------+-------------+----------+

Cartesian Product

Cartesian product means for every item in first set, you get all the items of second set. That is, if first set has 4 items and second set has 3 items, you would get 12 items (4*3) in the result.

In MySQL, a Cartesian product would be produced if you don’t specify the table relationship in an inner join. Run following query and see the result. You would get 56 rows (8*7).

SELECT e.first_name, e.last_name, u.user_type, u.username FROM `employee` AS e INNER JOIN `user` AS u;

Although the result set produced by a query like above seems meaningless, a Cartesian product can be used when there is no relationship between tables and you still want to get a combined result set.

For an example take following `leave_type` table that has no relationship to `employee` table (no `employee_id` column in `leave_type` table). SQL commands for creating the table and inserting data are available here.

id	name
1	Casual
2	Medical

Now think that each employee is eligible for both leave types (Casual and Medical) and you want to display a result set which lists leave types each employee is eligible. Following query will bring the desired result set.

SELECT e.first_name, e.last_name, l.name FROM `employee` AS e INNER JOIN `leave_type` AS l;

+------------+-----------+---------+
| first_name | last_name | name    |
+------------+-----------+---------+
| Robin      | Jackman   | Casual  |
| Robin      | Jackman   | Medical |
| Taylor     | Edward    | Casual  |
| Taylor     | Edward    | Medical |
| Vivian     | Dickens   | Casual  |
| Vivian     | Dickens   | Medical |
| Harry      | Clifford  | Casual  |
| Harry      | Clifford  | Medical |
| Eliza      | Clifford  | Casual  |
| Eliza      | Clifford  | Medical |
| Nancy      | Newman    | Casual  |
| Nancy      | Newman    | Medical |
| Melinda    | Clifford  | Casual  |
| Melinda    | Clifford  | Medical |
| Harley     | Gilbert   | Casual  |
| Harley     | Gilbert   | Medical |
+------------+-----------+---------+

Resolving Name Ambiguity

For all the queries mentioned so far in this article, we have specified aliases (like `employee` AS e) for table names and used them with column names (like e.first_name) to mention which column belong to which table.

If all columns mentioned in a join query have different names, you don’t have to use aliases as shown in following example.

SELECT first_name, last_name, user_type, username FROM `employee` INNER JOIN `user`;

However following query will trigger a MySQL error since it can’t decide to which table `id` field mentioned in ON clause belongs since both `employee` and `user` tables have an `id` field.

SELECT first_name, last_name, user_type, username FROM `employee` INNER JOIN `user` ON id = employee_id;

To avoid errors due to name ambiguity, it’s recommended to use table aliases always in join queries. You can also rename the column names in result sets as mentioned in article on SELECT statement.

Joining Multiple Tables

It’s possible to join more than two tables with a join query. Consider following `meeting_user` table that stores the user accounts of meeting software of the company.

Currently it has four accounts for existing four users and a general one for administration. SQL commands for creating the table and inserting data are available here.

id	user_id	username	password
1		m_admin	m_admin
2	3	m_taylor	m_taylor
3	4	m_vivian	m_vivian
4	6	m_melinda	m_melinda
5	7	m_harley	m_harley

Following query joins `employee`, `user` and `meeting_user` tables. You can see that we have used aliases for username columns to distinguish them in the result set.

SELECT e.first_name, e.last_name, u.user_type, u.username AS u_username, m.username AS m_username FROM `employee` AS e LEFT JOIN `user` AS u ON e.id = u.employee_id LEFT JOIN `meeting_user` AS m ON u.id = m.user_id;

+------------+-----------+-----------+------------+------------+
| first_name | last_name | user_type | u_username | m_username |
+------------+-----------+-----------+------------+------------+
| Robin      | Jackman   | NORMAL    | robin      | NULL       |
| Taylor     | Edward    | ADMIN     | taylor     | m_taylor   |
| Vivian     | Dickens   | ADMIN     | vivian     | m_vivian   |
| Harry      | Clifford  | NORMAL    | harry      | NULL       |
| Eliza      | Clifford  | NULL      | NULL       | NULL       |
| Nancy      | Newman    | NULL      | NULL       | NULL       |
| Melinda    | Clifford  | ADMIN     | melinda    | m_melinda  |
| Harley     | Gilbert   | NORMAL    | harley     | m_harley   |
+------------+-----------+-----------+------------+------------+

When generating above result set, first joining happens between `employee` and `user` tables and then the result set becomes the left table for second joining.

It’s not mandatory to select columns from all the tables mentioned in a join query. For an example, following query can be used to fetch meeting software username of each employee.

However since there is no relationship between `employee` and `meeting_user` tables (there is no `employee_id` column in `meeting_user` table), you still need to use `user` table for the joining but you don’t have to select any column from it.

SELECT e.first_name, e.last_name, m.username AS m_username FROM `employee` AS e LEFT JOIN `user` AS u ON e.id = u.employee_id LEFT JOIN `meeting_user` AS m ON u.id = m.user_id;

+------------+-----------+------------+
| first_name | last_name | m_username |
+------------+-----------+------------+
| Robin      | Jackman   | NULL       |
| Taylor     | Edward    | m_taylor   |
| Vivian     | Dickens   | m_vivian   |
| Harry      | Clifford  | NULL       |
| Eliza      | Clifford  | NULL       |
| Nancy      | Newman    | NULL       |
| Melinda    | Clifford  | m_melinda  |
| Harley     | Gilbert   | m_harley   |
+------------+-----------+------------+

You will usually see multiple table joins in many-to-many relationships.

One to Many Relationships

Data tables mentioned so far had one-to-one relationships. That is for each row in left table there was only one corresponding row (or no row) in the right table.

It’s also possible to have more than one corresponding row in right table for each row in left table. Such relationships are called one-to-many relationships.

Consider following table that stores telephone numbers of the employees. Note that there are duplicates in `employee_id` column forming one-to-many relationships (One employee can have one or more telephone numbers). SQL commands for creating the table and inserting data are available here.

id	employee_id	type	no
1	1	mobile	245-249697
2	2	mobile	270-235969
3	2	land	325-888885
4	3	mobile	270-684972
5	4	mobile	245-782365
6	4	land	325-888886
7	5	mobile	245-537891
8	6	mobile	270-359457
9	7	mobile	245-436589
10	7	land	325-888887
11	8	mobile	245-279164
12	8	land	325-888888

Following query returns employee names and their mobile numbers. There isn’t a difference in syntax compared to LEFT JOIN queries considered so far.

SELECT e.first_name, e.last_name, t.type, t.no FROM `employee` AS e LEFT JOIN `telephone` AS t ON e.id = t.employee_id;

+------------+-----------+--------+------------+
| first_name | last_name | type   | no         |
+------------+-----------+--------+------------+
| Robin      | Jackman   | mobile | 245-249697 |
| Taylor     | Edward    | mobile | 270-235969 |
| Taylor     | Edward    | land   | 325-888885 |
| Vivian     | Dickens   | mobile | 270-684972 |
| Harry      | Clifford  | mobile | 245-782365 |
| Harry      | Clifford  | land   | 325-888886 |
| Eliza      | Clifford  | mobile | 245-537891 |
| Nancy      | Newman    | mobile | 270-359457 |
| Melinda    | Clifford  | mobile | 245-436589 |
| Melinda    | Clifford  | land   | 325-888887 |
| Harley     | Gilbert   | mobile | 245-279164 |
| Harley     | Gilbert   | land   | 325-888888 |
+------------+-----------+--------+------------+

Many to Many Relationships

In many-to-many relationships, rows in both tables can have more than one corresponding row in other table.

Many-to-many relationships need to have an intermediate table for storing relationships. Many-to-many relationships will be explained in our next article.

Using ORDER BY and LIMIT

Similar to normal SELECT statements, you can use ORDER BY and LIMIT in join statements.

SELECT e.first_name, e.last_name, t.type, t.no FROM `employee` AS e LEFT JOIN `telephone` AS t ON e.id = t.employee_id ORDER BY t.type DESC, e.last_name ASC, e.first_name ASC LIMIT 4;

+------------+-----------+--------+------------+
| first_name | last_name | type   | no         |
+------------+-----------+--------+------------+
| Eliza      | Clifford  | mobile | 245-537891 |
| Harry      | Clifford  | mobile | 245-782365 |
| Melinda    | Clifford  | mobile | 245-436589 |
| Vivian     | Dickens   | mobile | 270-684972 |
+------------+-----------+--------+------------+

Specifying Multiple Relationships in ON Clause

Some data tables can have composite primary keys. That is, more than one column forms the primary key. In such cases, you can specify multiple relationships in ON clause using AND operator as shown in following syntax.

SELECT t1.*, t2.* FROM table1 AS t1 LEFT JOIN table2 AS t2 ON t1.field1 = t2.field1 AND t1.field2 = t2.field2;

Swapping Column Names in ON Clause

You can swap the column names mentioned in ON clause as below (Relationship is now u.employee_id = e.id instead of e.id = u.employee_id).

SELECT e.first_name, e.last_name, u.user_type, u.username FROM `user` AS u INNER JOIN `employee` AS e ON u.employee_id = e.id;

However it’s more readable if you specify left part of the relationship from left table and right part from right table.

Where to Head from Here...

You Are at	:	MySQL > MySQL Joins
Previous Article	:	MySQL Delete Statement
Next Article	:	MySQL Many to Many Relationships

We Value Your Feedback...

We love to hear what you think about this article. Please provide your opinion, suggestions and improvements using following form. Note that submitted feedback is not displayed but we will get back to you if it needs a reply.

Categories

Articles in MySQL Category