Overview
The JOIN syntax in SQL Server is used to build a single result set from more than 1 table. At the most basic level, two tables are joined based on a common set of columns between the tables. Joins can be coded in a few different manners; but in this example, we will use INNER JOIN logic to return matching rows from both tables. The tables included in the JOIN logic are specified in the FROM clause. An ON statement is used to indicate the matching columns. Let’s dig in.
Explanation
Let’s use the same query from an earlier tutorial with a few different modifications to the FROM clause. In the example below, we are selecting the columns from five different tables to show that data is returned from each table in the JOIN logic. In the example below, we are joining between these tables:
- HumanResources.Employee
- HumanResources.EmployeeAddress
- Person.Address
- Person.StateProvince
In each of these circumstances, the unique identifiers between the tables are used in the ON clause to join between the tables to return matching rows.
As you can see in the example below, aliasing is valuable because it makes the code compact and saves on a great deal of typing for each column specified in the SELECT, FROM, JOIN, WHERE, and ORDER BY logic.
USE AdventureWorks;
GO
SELECT E.EmployeeID,
E.LoginID AS 'Domain\LoginName',
E.HireDate,
EA.AddressID,
A.AddressID,
A.AddressLine1,
A.AddressLine2,
A.City,
S.[Name] AS 'State',
A.PostalCode
FROM HumanResources.Employee E
INNER JOIN HumanResources.EmployeeAddress EA
ON E.EmployeeID = EA.EmployeeID
INNER JOIN Person.Address A
ON EA.AddressID = A.AddressID
INNER JOIN Person.StateProvince S
ON A.StateProvinceID = S.StateProvinceID
WHERE E.VacationHours = 8
ORDER BY E.HireDate;
GO
Below is the sample result set:
Additional Information
Jeremy Kadlec is a Founder, Editor and Author at MSSQLTips.com with more than 300 contributions and 25+ years of SQL Server experience. Jeremy leads a team of more than 300 authors helping millions of SQL Server professionals around the globe every second of the day for the last 20 years. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP based on his community contributions. Jeremy brings 25+ years of SQL Server DBA and Developer knowledge to the community and holds a bachelor’s degree from SSU and master’s degree from UMBC.
