Microsoft® SQL Server™ 2008 All-In-One Desk Reference For Dummies®

III.5.3. Retrieving Data from Multiple Tables

Multi-table queries are queries that retrieve data from multiple tables in a single SELECT statement. The results from one table are joined with the results from another table. The INNER JOIN operator is used to identify the tables to be joined, and the ON operator identifies the column that the tables are joined on.

By using the INNER JOIN statement, you can include columns from multiple tables in the column list. In order for the output to have meaning, the joined tables must have an existing relationship — the INNER JOIN operator joins the tables on this relationship.

For example, Figure 5-4 shows the Person.Person and HumanResources. Employee tables from the AdventureWorks2008 database (shown as Person(Person) and Employee(HumanResources) in the Query Designer). These tables are related by the primary key of BusinessEntityID in the Person table and the foreign key of BusinessEntityID in the Employee table.

NOTE

The primary key is used within a table to ensure that each row is unique. The foreign key in one table references the primary key in another table, creating the relationship between the two tables.

Figure III.5-4. The Person and Employee tables from the Adventure Works2008 database.

The primary key and foreign key relationship between the tables is what allows you to join the two tables in a SELECT statement. For example, ...