SQL - INNER JOIN


Advertisements

Previous Page Next Page

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

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

    NameAgeAddress
    Marry24XXX, Brooklyn, New York, USA
    Jo27XXX, Grenelle, Paris, France
    Kim30XXX, Geuzenveld, Amsterdam, Netherlands
    Huang28XXX, 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:

    EmpIDNameCityAgeSalaryAddress
    2MarryNew York242750XXX, Brooklyn, New York, USA
    3JoParis272800XXX, Grenelle, Paris, France
    4KimAmsterdam303100XXX, Geuzenveld, Amsterdam, Netherlands
    6HuangChina282800XXX, Yizhuangzhen, Beijing, China

Previous Page Next Page