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

III.9.3. Understanding Outer Joins

The primary method used to join two tables is an INNER JOIN, often shortened to JOIN. However, OUTER JOINS also exist, such as RIGHT OUTER JOIN, LEFT OUTER JOIN, and FULL OUTER JOIN.

• RIGHT OUTER JOIN (RIGHT JOIN): The RIGHT OUTER JOIN displays all the rows from the table on the right (the second table mentioned in the query) and only those rows that have matches in the table on the left.

• LEFT OUTER JOIN (LEFT JOIN): The LEFT OUTER JOIN displays all the rows from the table on the left (the first table mentioned in the query) and only those rows that have matches in the table on the right.

• FULL OUTER JOIN (OUTER JOIN): The FULL OUTER JOIN displays all the rows from both tables.

All the JOIN operations join two or more tables. When more than two tables are joined, the Query Optimizer joins two tables at a time and then uses the result from that join to join to another table. This process continues until all the tables are joined.

The example queries in this section run against the AdventureWorks database. Use the steps in Chapter 5 of this mini-book to check whether AdventureWorks is installed. If not, install it.

III.9.3.1. Using an INNER JOIN

Although the INNER JOIN is discussed in the "Creating a New Query" section of Chapter 3 in this mini-book, it's listed here for easy comparison with the outer joins. The INNER JOIN displays only rows that ...