Database Reference
In-Depth Information
Figure 5-22.
Using LEFT OUTER JOIN
How It Works
Let's start with the
SELECT
list.
select PP.ProductID, PP.Name, PPR.ReviewerName, PPR.Comments, PPR.Rating
Since you're selecting columns from two tables, you need to identify which table a column comes
from, which you do by prefixing the table name and a dot (.) to the column name. This is known as
disambiguation,
or removing ambiguity so the database manager knows which column to use. Though
this has to be done only for columns that appear in both tables, the best practice is to qualify all columns
with their table names.
The following
FROM
clause specifies both the tables you're joining and the kind of join you're using:
from Production.Product PP left outer join Production.ProductReview PPR
It specifies a left outer join of the Production.Product and Production.ProductReview tables.
It also specifies the criteria for joining the primary key
ProductID
of the
Product
table with the
foreign key
ProductId
of the
ProductReview
table.
on PP.ProductID = PPR.ProductID
The left outer join on ProductID produces a table composed of five columns: ProductID, Name,
ReviewerName, Comments, and Rating. All the data is retrieved from rows in Production.Product, which
is the left table, and matching and unmatching data from Production.ProductReview where their
ProductID columns have the matching or even unmatching values.
