Database Reference
In-Depth Information
The inner join on
ProductID
produces a table composed of five columns: ProductID, Name,
ReviewerName, Comments, and Rating. The data is retrieved from rows in Production.Product and
Production.ProductReview where their ProductID columns have the same value. Any rows in Orders that
don't match rows in Employees are ignored, and vice versa. (This isn't the case here, but you'll see an
example soon.) An inner join always produces only rows that satisfy the join specification.
Tip
Columns used for joining don't have to appear in the
SELECT
list. If you want, you can omit that column.
Outer Joins
Outer joins return
all
rows from (at least) one of the joined tables even if rows in one table don't match
rows in the other. Three types of outer joins exist: left outer join, right outer join, and full outer join. The
terms
left
and
right
refer to the operands on the left and right of the
JOIN
operator. (Refer to the basic
syntax for the inner join, and you'll see why we called the operands
left-table
and
right-table
.) In a
left outer join, all rows from the left table will be retrieved whether they have matching rows in the right
table. Conversely, in a right outer join, all rows from the right table will be retrieved whether they have
matching rows in the left table. In a full outer join, all rows from both tables are returned.
Tip
Left and right outer joins are logically equivalent. It's always possible to convert a left join into a right join
by changing the operator and flipping the operands or a right join into a left with a similar change. So, only one of
these operators is actually needed. Which one you choose is basically a matter of personal preference, but a useful
rule of thumb is to use either left or right but not both in the same query. The query optimizer won't care, but
humans find it much easier to follow a complex query if the joins always go in the same direction.
When is this useful? Quite frequently. In fact, whenever a parent-child relationship exists between
tables, despite that referential integrity is maintained, some parent rows may not have related rows in
the child table, since child rows may be allowed to have null foreign key values and therefore not match
any row in the parent table.
Try It: Using LEFT OUTER JOIN
To list all ProductID and ProductName entries, even those that haven't been reviewed yet and have no
associated ReviewerName, Comments, and Rating, open a New Query window in SQL Server
Management Studio (remember to make AdventureWorks your query context). Enter the following
query, and click Execute. You should see the results shown in Figure 5-22.
select PP.ProductID, PP.Name, PPR.ReviewerName, PPR.Comments, PPR.Rating
from Production.Product PP left outer join Production.ProductReview PPR
on PP.ProductID = PPR.ProductID
