Transact-SQL Primer: Inner Join

Learn How to Combine Data from Multiple Tables in a SQL Query

Jun 19, 2008

A basic SQL concept that should be mastered is that of "joins." Joins allow for the display of data from different tables to be combined with specific relationships.

A "join" in SQL is a means by which related data from more than 1 table can be displayed together. There are several different types of joins, but this article will focus solely on the "inner join." In mathematical terms, an inner join between 2 subsets of data (2 tables) results in the intersection of those subsets. In other words, data in both tables that have the same values in one or more specified fields.

An example will serve best to clarify how an inner join operates. To follow along with the example and play with joins, set up a SQL server and development environment as outlined in "Install SQL Server 2005 Express." In the AdventureWorks sample database, the table HumanResources.Employee lists employee information, but seems to be missing the employee's name. A query to list employees who hold the title "Production Technician - WC10" would result in an unsatisfactory list.

SELECT ContactID, LoginID, Gender, BirthDate

FROM HumanResources.Employee

WHERE Title = 'Production Technician - WC10'

ORDER BY ContactID

1071 adventure-works\ruth0 F 1946-07-06

1072 adventure-works\sidney0 M 1946-10-01

1073 adventure-works\jeffrey0 M 1946-08-12

1074 adventure-works\doris0 F 1946-05-06

On a typical report, the first field, ContactID, would not be sufficient for most needs. If the employee name is required, where could it be found and incorporated into this query? This is where an inner join is needed. There is another table in this database, Person.Contact, that contains contact information, including first and last names. The "primary key" field, a unique identifier for each contact, is called "ContactID." This looks hopeful. As a matter of fact, this field holds the same contact ids as the field of the same name in HumanResources.Employee. By joining the 2 tables on the ContactID field, the data in both tables can be used in the query.

SELECT c.LastName, c.FirstName, e.LoginID, e.Gender, e.BirthDate

FROM HumanResources.Employee e

JOIN Person.Contact c

ON c.ContactID = e.ContactID

WHERE e.Title = 'Production Technician - WC10'

ORDER BY c.LastName, c.FirstName

First notice the 1-letter prefixes in front of each field being selected, "c." and "e." These are called "aliases" and are a way to refer to a table using a shorter name than the table name itself. Each alias is defined when it's related table is used. Look at the 3 lines that start with FROM, JOIN and ON. Records are being selected from the HumanResources.Employee (e) table as in the first query. This time, however, it is being "joined" to the Person.Contact (c) table and the joining relationship between the 2 tables is the field "ContactID." A portion of the results follow.

Baker Mary adventure-works\mary1 F 1976-10-20

Ellerbrock Ruth adventure-works\ruth0 F 1946-07-06

Ersan Ebru adventure-works\ebru0 M 1976-10-23

Ford Jeffrey adventure-works\jeffrey0 M 1946-08-12

Practice using some other inner joins. There are similar relationships between tables Production.Product and Production.ProductModel, as well as Production.Product and Sales.SalesOrderDetail.

The copyright of the article Transact-SQL Primer: Inner Join in Database Programming is owned by Dan Hartshorn. Permission to republish Transact-SQL Primer: Inner Join in print or online must be granted by the author in writing.