Join tables in a SELECT statement example

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:

NERJOIN