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 | notes |
---|---|---|---|---|---|
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 | +------------+-----------+--------+------------+
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.