Using INNER JOIN
INNER JOIN is used to retrieve the data from all tables listed based on a condition of equality listed after keyword ON. If the condition is not meet, rows are not returned. For example, see the following tables in our classicmodels sample database. We have employees table and offices table. Two tables are linked together through the keyed column officeCode. OfficeCode is the primary key of the offices table and is a foreign key in the employees table. To find out what employees work in which countries and states we can use INNER JOIN to join the offices and employees table. We return columns from both tables (note the fully qualified column names like "employees.firstname"), but only the rows where the officeCode in each of the tables match. 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 |
| FoonYue | 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)
We could make the above query return more specific results. What if we want to find only employees in USA? The following query adds an AND clause to narrow down the country:
SELECT e.firstname,
e.lastname,
state
FROM employees e INNER JOIN offices o
ON o.officeCode = e.officeCode
WHERE country = 'USA' ;
Here is the resulting data
+-----------+-----------+-------+
| 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 |
| FoonYue | Tseng | NY |
| George | Vanauf | NY |
+-----------+-----------+-------+
10 rows in set (0.00 sec)