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.
| EmpId | FirstName | LastName | 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 |
| DeptId | Name |
|---|---|
| 1 | 'Finance' |
| 2 | 'HR' |
Consider the following 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.
| 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.
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.
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.
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.
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;