» MySQL Tips » How to Use SQL JOIN to Retrieve Data from Multiple Tables
How to Use SQL JOIN to Retrieve Data from Multiple Tables
Summary: In this tutorial, you will learn how to use SQL JOIN to select data from multiple database tables. You will learn different kinds of JOIN including inner join, outer join, left and right joins.
In the previous tutorial, you've learned how to retrieve data from one table by using SELECT statement. But normally you often select data from multiple tables to have a complete data. In order to do so, you need to use SQL JOIN clause with SELECT statement. The typical form of SQL JOIN clause in MySQL is simple as follows:
SELECT column_list FROM table_1
[INNER | LEFT | RIGHT] table_2 ON conditions_2
[INNER | LEFT | RIGHT] table_3 ON conditions_3 ...
WHERE conditions
After SELECT keyword is a list of column name in which you want to retrieve the data. These columns have to be in the tables you select such as table_1, table_2... If the columns' name in those tables are the same, you have to explicit declare the as the form table_name.column_name or use table and column alias, otherwise MySQL will returns you an error message to say that the column name you selected is ambiguous. Next you list the main table and then a list of table you want to join. You can use INNER JOIN, LEFT JOIN or RIGHT JOIN. You can join a table with more than two tables or even with itself which is known as self join. In the JOIN clause you have to declare the join conditions. If all the conditions on each join clause matches, MySQL will return the corresponding data.
Using INNER JOIN
INNER JOIN is used to retrieve the data from all tables listed based on condition listed after keyword ON. If the condition is not meet, nothing is returned. For example, see the following tables in our classicmodels sample database. We have employees table and offices table. Two tables are linked together by the column officeCode. To find out who is in which country and state we can use INNER JOIN to join these tables. Here is the SQL code:
SELECT employees.firstname,
employees.lastname,
offices.country,
offices.state
FROM employees
INNER JOIN offices
ON offices.officeCode = employees.officeCode
And we will get the data like this:
+-----------+-----------+-----------+------------+
| firstname | lastname | country | state |
+-----------+-----------+-----------+------------+
| Diane | Murphy | USA | CA |
| Mary | Patterson | USA | CA |
| Jeff | Firrelli | USA | CA |
| William | Patterson | Australia | NULL |
| Gerard | Bondur | France | NULL |
| Anthony | Bow | USA | CA |
| Leslie | Jennings | USA | CA |
| Leslie | Thompson | USA | CA |
| Julie | Firrelli | USA | MA |
| Steve | Patterson | USA | MA |
| Foon Yue | Tseng | USA | NY |
| George | Vanauf | USA | NY |
| Loui | Bondur | France | NULL |
| Gerard | Hernandez | France | NULL |
| Pamela | Castillo | France | NULL |
| Larry | Bott | UK | NULL |
| Barry | Jones | UK | NULL |
| Andy | Fixter | Australia | NULL |
| Peter | Marsh | Australia | NULL |
| Tom | King | Australia | NULL |
| Mami | Nishi | Japan | Chiyoda-Ku |
| Yoshimi | Kato | Japan | Chiyoda-Ku |
| Martin | Gerard | France | NULL |
+-----------+-----------+-----------+------------+
23 rows in set (0.02 sec)
If you want to find only employees in USA, just execute this query:
SELECT e.firstname,
e.lastname,
o.state
FROM employees e
INNER JOIN offices o
ON o.officeCode = e.officeCode AND country = 'USA'
+-----------+-----------+-------+
| firstname | lastname | state |
+-----------+-----------+-------+
| Diane | Murphy | CA |
| Mary | Patterson | CA |
| Jeff | Firrelli | CA |
| Anthony | Bow | CA |
| Leslie | Jennings | CA |
| Leslie | Thompson | CA |
| Julie | Firrelli | MA |
| Steve | Patterson | MA |
| Foon Yue | Tseng | NY |
| George | Vanauf | NY |
+-----------+-----------+-------+
10 rows in set (0.00 sec)
Using LEFT and RIGHT JOIN
LEFT JOIN can be used when you want to retrieve the data from the main table (table1) even if there is no match in other tables (table_2, table_3....). While RIGHT JOIN is used to retrieve the data the from all other tables (table_2, table_3...) even if there is no match in the main table.
Let take a look at an example of using SQL left and SQL right join. In our classicalmodels sample database, when the company wants to establish a new office, the SQL query to insert a new office to the database as follows:
INSERT INTO classicmodels.offices
(officeCode,
city,
phone,
addressLine1,
addressLine2,
state,
country,
postalCode,
territory
)
VALUES ('8',
'Boston',
'+1 215 837 0825',
'1550 dummy street',
'dummy address',
'MA',
'USA',
'02107',
'NA'
)
At this time, the company has not hired any new employee for the new office yet so the new office does not have any employee. If we want to know employees who work in which office and all the offices of the company, we can use RIGHT JOIN as follows:
SELECT firstname,
lastname,
addressLine1
FROM employees e
RIGHT JOIN offices o ON o.officeCode = e.officeCode
+-----------+-----------+--------------------------+
| firstname | lastname | addressLine1 |
+-----------+-----------+--------------------------+
| Mary | Patterson | 100 Market Street |
| Diane | Murphy | 100 Market Street |
| Jeff | Firrelli | 100 Market Street |
| Anthony | Bow | 100 Market Street |
| Leslie | Jennings | 100 Market Street |
| Leslie | Thompson | 100 Market Street |
| Julie | Firrelli | 1550 Court Place |
| Steve | Patterson | 1550 Court Place |
| Foon Yue | Tseng | 523 East 53rd Street |
| George | Vanauf | 523 East 53rd Street |
| Gerard | Bondur | 43 Rue Jouffroy D'abbans |
| Loui | Bondur | 43 Rue Jouffroy D'abbans |
| Gerard | Hernandez | 43 Rue Jouffroy D'abbans |
| Pamela | Castillo | 43 Rue Jouffroy D'abbans |
| Martin | Gerard | 43 Rue Jouffroy D'abbans |
| Mami | Nishi | 4-1 Kioicho |
| Yoshimi | Kato | 4-1 Kioicho |
| William | Patterson | 5-11 Wentworth Avenue |
| Andy | Fixter | 5-11 Wentworth Avenue |
| Peter | Marsh | 5-11 Wentworth Avenue |
| Tom | King | 5-11 Wentworth Avenue |
| Larry | Bott | 25 Old Broad Street |
| Barry | Jones | 25 Old Broad Street |
| NULL | NULL | 1550 dummy street |
+-----------+-----------+--------------------------+
24 rows in set (0.00 sec)
As you can see, the RIGHT JOIN get the all the data from second table (offices) and data from the first table employees even the condition does not match.
Joining a Table to Itself or Self joins
You can also using JOIN clause to join table to itself. In this case you need to use table alias. Consider the following situation to our sample database, we want to know who reports to whom in our organization. Here is the SQL self join query to print the organization structure:
SELECT concat(e.firstname,',',e.lastname) AS employee,
concat(m.firstname,',',m.lastname) AS manager
FROM employees AS m
INNER JOIN employees AS e ON m.employeeNumber = e.reportsTo
ORDER BY employee
+------------------+-------------------+
| employee | manager |
+------------------+-------------------+
| Andy,Fixter | William,Patterson |
| Barry,Jones | Gerard,Bondur |
| Foon Yue,Tseng | Anthony,Bow |
| George,Vanauf | Anthony,Bow |
| Gerard,Hernandez | Gerard,Bondur |
| Jeff,Firrelli | Diane,Murphy |
| Julie,Firrelli | Anthony,Bow |
| Larry,Bott | Gerard,Bondur |
| Leslie,Jennings | Anthony,Bow |
| Leslie,Thompson | Anthony,Bow |
| Loui,Bondur | Gerard,Bondur |
| Martin,Gerard | Gerard,Bondur |
| Mary,Patterson | Diane,Murphy |
| Pamela,Castillo | Gerard,Bondur |
| Peter,Marsh | William,Patterson |
| Steve,Patterson | Anthony,Bow |
| Tom,King | William,Patterson |
| Yoshimi,Kato | Mami,Nishi |
+------------------+-------------------+
For more information on SQL self-join check it out SQL self-join tutorial.