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;