SQL - JOIN


Advertisements

Previous Page Next Page

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 the 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 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_NumberEmpIDAddress
+1-80XXXXX0002XXX, Brooklyn, New York, USA
+33-14XXXXX013XXX, Grenelle, Paris, France
+31-20XXXXX194XXX, Geuzenveld, Amsterdam, Netherlands
+86-10XXXXX4586XXX, Yizhuangzhen, Beijing, China
+65-67XXXXX47XXX, Yishun, Singapore
+81-35XXXXX728XXX, 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:

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


Previous Page Next Page