17 - MySQL Using Join

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

Google

Singapore

$15000

ID

location

Quantity

1

Delhi

1278

2

NY

2200

3

Spain

2190

4

Israel

2890

Now we need to fetch data of all company details along with the Quantity of servers they have. The query for the same is:
Select A.ID, Company, HQ_Addres, location, Quantity,Amount from company A INNER JOIN quantity B on A.ID = B.ID;

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

Google

Singapore

Israel

2890

$15000

Note: Always avoid Ambiguous column name in Join queries. For example in the above query we use A.ID in the select columns list because ID is present in both the tables so we need to mention that for which table the ID refers to. We can also write company.ID i.e using the original table name instead of alias name.

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.

Like us on Facebook