216
Part II: Building Databases and Working with Data
The intersection simply represents that some common column can connect a row from the
fi rst data set to data in the second data set. The common values are typically a primary key
and a foreign key, such as these examples from the AdventureWorksLT2012 sample data-
base. All tables listed are from the SalesLT schema:■ (^) CustomerID between the Customer and CustomerAddress tables
■ SalesOrderID between the SalesOrderHeader and SalesOrderDetail tables
■ (^) ProductID between the Product and SalesOrderDetail tables
SQL includes many types of joins that determine how the rows are selected from the dif-
ferent sides of the intersection. Table 9-1 lists the join types. (Each is explained in more
detail later in this section.)
TABLE 9-1 Join Types
Join Type Query Des igner Symbol Defi nition
Inner join Includes only matching rows
Left outer
join
Includes all rows from the left table regardless of whether
a match exists, and matching rows from the right table
Right outer
join
Includes all the rows from the right table regardless of
whether a match exists, and matching rows from the left
table
Full outer
join
Includes all the rows from both tables regardless of
whether a match exists
Q (theta)
join
Matches rows using a nonequal condition — the symbol
shows the actual theta condition (<, >, <=, >=, <>)
Cross join No join
connection
Produces a Cartesian product — a match between each
row in data source one with each row from data source
two without any conditions or restrictions
This chapter uses the AdventureWorksLT2012 sample database, which is available for download from http://
msftdbprodsamples.codeplex.com/releases/view/55330
Inner Joins
The inner join is by far the most common join. It’s also referred to as a common join and
was originally called a natural join by E. F. Codd. The inner join returns only those rows
that represent a match between the two data sets. An inner join is well named because it
extracts only data from the inner portion of the intersection of the two overlapping data
sets, as shown in Figure 9-3.
c09.indd 216c09.indd 216 7/30/2012 4:25:04 PM7/30/2012 4:25:04 PM
http://www.it-ebooks.info