It is common and very easy to fetch the data from the single table as we already learned in select command chapter. Now the situation arises where we need to fetch the data from two or more tables based on certain conditions in a single MySQL query.
Both tables must have some common fields according to which we map the fields of one table to another to match them and fetch the corresponding data.
Similarly sometime there is need to update or delete the records from one table based on the conditions on another table.
MySQL provide JOINS to combine the two tables and thus we can use select, delete and update on multiple tables from the single MySQL query. The basic syntax of MySQL JOIN is:
SELECT column1, column2, … , columnN FROM Table1 INNER JOIN Table2 ON join_condition1 INNER JOIN Table3 ON join_condition2 ... WHERE where_conditions;
MySQL provides various JOIN operations depend upon the conditions on the table i.e whether we need data from the right table or left table. The Joins that MySQL provides are:
- INNER JOIN
- RIGHT JOIN
- LEFT JOIN
- FULL OUTER JOIN
Below image specify how to use these joins to fetch data from left or right table depending upon the conditions.
Here A and B are alias names for the Table A and Table B that is written next to table names in the query. Key refers to the common name that is common in both the tables.
NOTE: If you want to fetch the data from one table that for which there is no data in the second table then you need to use NULL for that column in second table as mentioned in the above image.
Example of Joins:
We have a data of the company names along with their head quarter address and price of some server in one table and Quantity of server and location in another table with ID as common column in both tables.
ID | Company | HQ_Address | Amount |
1 | HCL | NY | $1000 |
2 | Apple | Chicago | $2000 |
3 | IBM | INDIA | $3000 |
4 | Singapore | $15000 |
ID | location | Quantity |
1 | Delhi | 1278 |
2 | NY | 2200 |
3 | Spain | 2190 |
4 | Israel | 2890 |
The output of above INNER JOIN query is:
ID | Company | HQ_Address | location | Quantity | Amount |
1 | HCL | NY | Delhi | 1278 | $1000 |
2 | Apple | Chicago | NY | 2200 | $2000 |
3 | IBM | INDIA | Spain | 2190 | $3000 |
4 | Singapore | Israel | 2890 | $15000 |
Left Join: Used when you want to select all the data from left hand side table and the matched data in right table but not the un-matched data of right table.
Right Join: Used when you want to select all the data from righthand side table and the matched data in left table but not the un-matched data of left table.
Outer Join: When you want to fetch all the matched and un matched data from both left and right table.