SQL - INNER JOIN
The SQL INNER JOIN keyword is used to combine column values of two tables and returns all rows from both of the tables when there is a match between the columns.
Syntax
The syntax for using INNER JOIN keyword is given below:
SELECT table1.column1, table1.column2, table2.column1, table2.column2, ... FROM table1 INNER JOIN table2 ON table1.matching_column = table2.matching_column;
Example:
Consider database tables called Employee and Contact_Info with the following records:
Table 1: Employee table
EmpID | Name | City | Age | Salary |
---|---|---|---|---|
1 | John | London | 25 | 3000 |
2 | Marry | New York | 24 | 2750 |
3 | Jo | Paris | 27 | 2800 |
4 | Kim | Amsterdam | 30 | 3100 |
5 | Ramesh | New Delhi | 28 | 3000 |
6 | Huang | Beijing | 28 | 2800 |
Table 2: Contact_Info table
Phone_Number | EmpID | Address |
---|---|---|
+1-80XXXXX000 | 2 | XXX, Brooklyn, New York, USA |
+33-14XXXXX01 | 3 | XXX, Grenelle, Paris, France |
+31-20XXXXX19 | 4 | XXX, Geuzenveld, Amsterdam, Netherlands |
+86-10XXXXX458 | 6 | XXX, Yizhuangzhen, Beijing, China |
+65-67XXXXX4 | 7 | XXX, Yishun, Singapore |
+81-35XXXXX72 | 8 | XXX, Koto City, Tokyo, Japan |
-
To inner join Employee and Contact_Info tables based on matching column EmpID, the SQL code is given below. This will fetch Name and Age columns from Employee table and Address column from Contact_Info table.
SELECT Employee.Name, Employee.Age, Contact_Info.Address FROM Employee INNER JOIN Contact_Info ON Employee.EmpID = Contact_Info.EmpID;
This will produce the result as shown below:
Name Age Address Marry 24 XXX, Brooklyn, New York, USA Jo 27 XXX, Grenelle, Paris, France Kim 30 XXX, Geuzenveld, Amsterdam, Netherlands Huang 28 XXX, Yizhuangzhen, Beijing, China -
To fetch all fields of a table, table.* keyword is used, for example - to fetch all fields of the Employee table, Employee.* is used in the below SQL code:
SELECT Employee.*, Contact_Info.Address FROM Employee INNER JOIN Contact_Info ON Employee.EmpID = Contact_Info.EmpID;
This result of the following code will be:
EmpID Name City Age Salary Address 2 Marry New York 24 2750 XXX, Brooklyn, New York, USA 3 Jo Paris 27 2800 XXX, Grenelle, Paris, France 4 Kim Amsterdam 30 3100 XXX, Geuzenveld, Amsterdam, Netherlands 6 Huang China 28 2800 XXX, Yizhuangzhen, Beijing, China