SQL Tutorial SQL Advanced SQL Database SQL References

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.
SQL JOINs

Example:

Consider a database containing tables called Employee and Contact_Info with the following records:

Table 1: Employee table

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

Table 2: Contact_Info table

Phone_NumberEmpIDAddressGender
+1-80XXXXX0002XXX, Brooklyn, New York, USAF
+33-14XXXXX013XXX, Grenelle, Paris, FranceM
+31-20XXXXX194XXX, Geuzenveld, Amsterdam, NetherlandsF
+86-10XXXXX4586XXX, Yizhuangzhen, Beijing, ChinaM
+65-67XXXXX47XXX, Yishun, SingaporeM
+81-35XXXXX728XXX, Koto City, Tokyo, JapanM

  • 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:

    NameAgeAddress
    Marry24XXX, Brooklyn, New York, USA
    Jo27XXX, Grenelle, Paris, France
    Kim30XXX, Geuzenveld, Amsterdam, Netherlands
    Huang28XXX, Yizhuangzhen, Beijing, China

5