SQL - JOIN
The SQL JOIN clause is used to combine rows of two or more tables based on common column between them. There are four types of JOINs in SQL:
- INNER JOIN: Returns records based on matching rows in both tables.
- LEFT JOIN: Returns records which contains all rows from left table and matching rows from right tables.
- RIGHT JOIN: Returns records which contains all rows from right table and matching rows from left tables.
- FULL JOIN: Returns records which contains all rows from both tables.
Example:
Consider a 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 |
-
The INNER JOIN clause is used with Employee and Contact_Info tables based on common column EmpID. It returns Name, Age and Address columns based on match in both tables. The SQL code is given below:
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