Learning Advanced Query
In the previous tutorial, you've learn how to retrieve data from one table by using SELECT statement. But in the real database programming task, you usually does not select data from just one table because of the normalization process (a big table is divided into a subset of smaller tables). So to get the complete data, you need to use SQL JOIN clause in SELECT statement. The normal form of SQL JOIN clause in MySQL is simple 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, otherwise MySQL will returns you an error message to say that the column name you selected is ambiguous. Next you will 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. In the JOIN clause you have to declare the join conditions. If all the conditions on each join clause match, 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 employees.firstname,employees.lastname,state
FROM employees
INNER JOIN offices ON offices.officeCode = employees.officeCode AND country = 'USA'
Here is the data back
+-----------+-----------+-------+
| 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. As an example, in our classicalmodels sample database, when the company want to establish a new office, the SQL script to insert a new office to the databse 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 hasn't any hire new employee yet so the new office does not have any employee. If we want to know employee who belongs to which office and all the offices of the company, we can use RIGHT JOIN as follows:
SELECT firstname,lastname,addressLine1
FROM employees
RIGHT JOIN offices ON offices.officeCode = employees.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 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. Consider the following situation to our sample databse, we want to know who has to report to whom in organization structure. Here is the SQL script:
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 |
+------------------+-------------------+
