Microsoft® SQL Server® 2012 Bible

219

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

9

To see how joins can alter the number of rows returned, look at the following query. First,

create some data in the tempdb by executing the following SQL:

USE tempdb;

CREATE TABLE [dbo].[Customer](

[CustomerID] [int] NOT NULL,

[LastName] [varchar](50) NOT NULL);

INSERT INTO Customer(CustomerID, LastName)

VALUES(101, 'Smith'),

(102, 'Adams'),

(103, 'Reagan'),

(104, 'Franklin'),

(105, 'Dowdry')

CREATE TABLE [dbo].[SalesOrder](

[OrderNumber] [varchar](50) NOT NULL,

[CustomerID] [int] NOT NULL);

INSERT INTO SalesOrder (OrderNumber, CustomerID )

VALUES( '1',101),

( '2',101),

( '3',102),

( '4',102),

( '5',103),

( '6',105),

( '7',105)

The initial row count of Customer is 5, yet when the customers are matched with their

orders, the row count changes to 7. The following code compares the two queries and their

respective results side-by-side:

USE tempdb;

SELECT CustomerID , LastName SELECT cst.CustomerID , OrderNumber

from Customer; FROM SalesOrder so

INNER JOIN Customer cst

ON so.CustomerID = cst.CustomerID;

Results from both queries:

CustomerID LastName CustomerID OrderNumber

----------- -------- ----------- -----------

101 Smith 101 1

101 2

102 Adams 102 3

102 4

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

http://www.it-ebooks.info