Microsoft® SQL Server® 2012 Bible

217

9

Chapter 9: Merging Data with Joins, Subqueries, and CTEs

FIGURE 9-3

The inner join includes only those rows from each side of the join that are contained within

the intersection of the two data sources.

Data Set A Data Set B

Common

Intersection

Inner Join

Creating Inner Joins

Using T- SQL code, joins are specifi ed within the FROM portion of the SELECT statement.

The keyword JOIN identifi es the second table, and the ON clause defi nes the common

ground between the two tables. The default type of join is an inner join, so the keyword

INNER is optional. For clarity, however, you should always include it:

SELECT *

FROM Table1

[INNER] JOIN Table2

ON Table1.column = Table2.column;

Because joins pull together data from two data sets, it makes sense that SQL needs to know

how to match up rows from those sets. SQL Server merges the rows by matching a value

common to both tables. Typically, a primary key value from one table is matched with a

foreign key value from the secondary table; however, tables can be related by columns that

are not defi ned as keys. As long as the data types are compatible, a join can be performed.

Whenever a row from the fi rst table matches a row from the second table, the two rows are

merged into a new row containing data from both tables.

The following code sample joins the SalesLT.RetailSales (secondary) and SalesLT

.Customer (primary) tables from the AdventureWorksLT2012 sample database. The ON

clause specifi es the common data:

USE AdventureWorksLT2012;

SELECT CST.CompanyName, SOH.TotalDue

FROM SalesLT.Customer CST

INNER JOIN SalesLT.SalesOrderHeader SOH

ON CST.CustomerID = SOH. CustomerID

The query begins with the Customer table. For every Customer row, SQL Server attempts

to identify matching SalesOrderHeader rows by comparing the CustomerID columns

c09.indd 217c09.indd 217 7/30/2012 4:25:05 PM7/30/2012 4:25:05 PM

http://www.it-ebooks.info