Previous
Next

SQL - INNER JOIN Query

The INNER JOIN query is used to retrieve the matching records from two or more tables based on the specified condition.

Syntax:

SELECT table1.column_name(s), table2.column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

For the demo purpose, we will use the following Employee and Department tables in all examples.

Employee Table
EmpId FirstName LastName Email Salary DeptId
1 'John' 'King' '[email protected]' 33000 1
2 'James' 'Bond'
3 'Neena' 'Kochhar' '[email protected]' 17000 2
4 'Lex' 'De Haan' '[email protected]' 15000 1
5 'Amit' 'Patel' 18000 3
6 'Abdul' 'Kalam' '[email protected]' 25000 2
Department Table
DeptId Name
1 'Finance'
2 'HR'

Consider the following inner join query.

SQL Script: Inner Join Query
SELECT Employee.EmpId, Employee.FirstName, Employee.LastName, Department.Name 
FROM Employee 
INNER JOIN Department
ON Employee.DeptId = Department.DeptId;

The above inner join query joins the Employee table and Department table and retrieves records from both the tables where Employee.DeptId = Department.DeptId. It only fetches records from both the tables where DeptId in the Employee table matches with the DeptId of the Department table. If the DeptId is NULL or not matching, then it won't retrieve those records. The following is the result of the above query.

Employee Table
EmpId FirstName LastName Name
1 'John' 'King' 'Finance'
3 'Neena' 'Kochhar' 'HR'
4 'Lex' 'De Haan' 'Finance'
6 'Abdul' 'Kalam' 'HR'

Notice that it only displayed the records whose DeptId matches, not whose DeptId is null or not matching.

It does not matter which table you take first in the query. The following query will display the same result as above.

SQL Script: Inner Join Query
SELECT Employee.EmpId, Employee.FirstName, Employee.LastName, Department.Name 
FROM  Department
INNER JOIN Employee
ON Department.DeptId = Employee.DeptId;

It is not mandatory to use the INNER JOIN phrase. You can use the WHERE clause to achieve the same result, as shown below.

SQL Script: Inner Join using WHERE Clause
SELECT emp.EmpId, emp.FirstName, emp.LastName, dept.Name 
FROM  Department dept, Employee emp
WHERE dept.DeptId = emp.DeptId;

You can take alias as short cuts of table names, as shown below.

SQL Script: Inner Join Query
SELECT emp.EmpId, emp.FirstName, emp.LastName, dept.Name 
FROM  Department dept
INNER JOIN Employee emp
ON dept.DeptId = emp.DeptId;

Repeat the INNER JOIN.. ON statement to include one more table in the query. For example, the following inner join query joins the three tables.

SQL Script: Inner Join Query
SELECT Employee.EmpId, Employee.FirstName, Employee.LastName, Consultant.FirstName, Department.Name 
FROM Employee 
INNER JOIN Department
ON Employee.DeptId = Department.DeptId
INNER JOIN Consultant
ON Consultant.DeptId = Department.DeptId;
Previous
Next