SQL Server - INNER JOIN Query

The INNER JOIN query is used to retrieve the matching records from two or more tables based on the specified condition. SQL Server follows the SQL stadards for inner join queries.

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 Server: 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 Server: 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 Server: 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 Server: 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 Server: 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;