Introduction
In SQL Server, the INNER JOIN is used to combine rows from two or more tables based on a related column between them. It retrieves only the matching rows that satisfy the join condition, discarding non-matching rows from both tables. This document provides a brief explanation and coding examples of how to use the INNER JOIN in SQL Server.
Syntax
The basic syntax of the INNER JOIN in SQL Server is as follows:
sqlSELECT columns
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
Example 1: Simple Inner Join
Let's consider two tables: Employees and Departments, which have a common column DepartmentID.
Table: Employees
| EmployeeID | EmployeeName | DepartmentID |
|---|---|---|
| 1 | John | 101 |
| 2 | Jane | 102 |
| 3 | Alex | 101 |
Table: Departments
| DepartmentID | DepartmentName |
|---|---|
| 101 | IT |
| 102 | HR |
Suppose we want to retrieve the names of employees along with their department names. We can use an INNER JOIN to achieve this:
sqlSELECT Employees.EmployeeName, Departments.DepartmentName
FROM Employees
INNER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;
Output:
| EmployeeName | DepartmentName |
|---|---|
| John | IT |
| Jane | HR |
| Alex | IT |
Example 2: Inner Join with Aliases
You can use aliases to simplify table names when dealing with long table names or for better readability.
sqlSELECT emp.EmployeeName, dep.DepartmentName
FROM Employees AS emp
INNER JOIN Departments AS dep
ON emp.DepartmentID = dep.DepartmentID;
Example 3: Inner Join with Multiple Conditions
You can use additional conditions in the ON clause to refine the join.
Table: Orders
| OrderID | EmployeeID | OrderDate |
|---|---|---|
| 1 | 1 | 2023-05-10 |
| 2 | 3 | 2023-05-12 |
| 3 | 2 | 2023-05-15 |
sqlSELECT Employees.EmployeeName, Orders.OrderDate
FROM Employees
INNER JOIN Orders
ON Employees.EmployeeID = Orders.EmployeeID AND Orders.OrderDate > '2023-05-10';
Output:
| EmployeeName | OrderDate |
|---|---|
| Jane | 2023-05-12 |
| Alex | 2023-05-15 |
Conclusion
The INNER JOIN in SQL Server is a powerful feature to combine data from multiple tables based on related columns. It allows you to extract specific information from different tables and consolidate it into a single result set. By mastering the usage of INNER JOIN, you can efficiently work with complex data relationships in your SQL queries.
0 Comments