Join tables in a SELECT statement example
By: Jeremy Kadlec
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.
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:
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: