SQLServerCh04

Chapter 4

How to retrieve data
from two or more tables

Murach's SQL Server 2012, C4

© 2012, Mike Murach & Associates, Inc.

Slide 1

Objectives
Applied
 Use the explicit syntax to code an inner join that returns data from
a single table or multiple tables.
 Code a union that combines data from a single table or multiple
tables.
Knowledge
 Explain when column names need to be qualified.
 Describe the proper use of correlation names.
 Describe the differences between an inner join, a left outer join, a
right outer join, a full outer join, and a cross join.
 Explain why you don’t need to use right outer joins.
 Describe the use of the implicit syntax for coding inner joins.

Murach's SQL Server 2012, C4

© 2012, Mike Murach & Associates, Inc.

Slide 2

Objectives (cont.)
 Describe the use of unions including the use of the EXCEPT and
INTERSECT operators.

Murach's SQL Server 2012, C4

© 2012, Mike Murach & Associates, Inc.

Slide 3

The explicit syntax for an inner join
SELECT select_list
FROM table_1
[INNER] JOIN table_2
ON join_condition_1
[[INNER] JOIN table_3
ON join_condition_2]...

Murach's SQL Server 2012, C4

© 2012, Mike Murach & Associates, Inc.

Slide 4

An inner join of the Vendors and Invoices tables
SELECT InvoiceNumber, VendorName
FROM Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID;

The result set

(114 rows)

Murach's SQL Server 2012, C4

© 2012, Mike Murach & Associates, Inc.

Slide 5

The syntax for an inner join
that uses correlation names
SELECT select_list
FROM table_1 [AS] n1
[INNER] JOIN table_2 [AS] n2
ON n1.column_name operator n2.column_name
[[INNER] JOIN table_3 [AS] n3
ON n2.column_name operator n3.column_name]...

Murach's SQL Server 2012, C4

© 2012, Mike Murach & Associates, Inc.

Slide 6

Correlation names that make the query
more difficult to read
SELECT InvoiceNumber, VendorName, InvoiceDueDate,
InvoiceTotal - PaymentTotal - CreditTotal AS BalanceDue
FROM Vendors AS v JOIN Invoices AS i
ON v.VendorID = i.VendorID
WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0
ORDER BY InvoiceDueDate DESC;

The result set

(11 rows)

Murach's SQL Server 2012, C4

© 2012, Mike Murach & Associates, Inc.

Slide 7

A correlation name that simplifies the query
SELECT InvoiceNumber, InvoiceLineItemAmount,
InvoiceLineItemDescription
FROM Invoices JOIN InvoiceLineItems AS LineItems
ON Invoices.InvoiceID = LineItems.InvoiceID
WHERE AccountNo = 540
ORDER BY InvoiceDate;

The result set

(6 rows)

Murach's SQL Server 2012, C4

© 2012, Mike Murach & Associates, Inc.

Slide 8

The syntax of a fully-qualified object name
linked_server.database.schema.object

A join with fully-qualified table names
SELECT VendorName, CustLastName, CustFirstName,
VendorState AS State, VendorCity AS City
FROM DBServer.AP.dbo.Vendors AS Vendors
JOIN DBServer.ProductOrders.dbo.Customers AS Customers
ON Vendors.VendorZipCode = Customers.CustZip
ORDER BY State, City;

The result set

(37 rows)

Murach's SQL Server 2012, C4

© 2012, Mike Murach & Associates, Inc.

Slide 9

The same join with partially-qualified table names
SELECT VendorName, CustLastName, CustFirstName,
VendorState AS State, VendorCity AS City
FROM Vendors
JOIN ProductOrders..Customers AS Customers
ON Vendors.VendorZipCode = Customers.CustZip
ORDER BY State, City;

Murach's SQL Server 2012, C4

© 2012, Mike Murach & Associates, Inc.

Slide 10

A stored procedure that adds a linked server
USE master;
EXEC sp_addlinkedserver
@server='DBServer',
@srvproduct='',
@provider='SQLNCLI',
@datasrc='localhost\SqlExpress';

Murach's SQL Server 2012, C4

© 2012, Mike Murach & Associates, Inc.

Slide 11

An inner join with two conditions
SELECT InvoiceNumber, InvoiceDate,
InvoiceTotal, InvoiceLineItemAmount
FROM Invoices JOIN InvoiceLineItems AS LineItems
ON (Invoices.InvoiceID = LineItems.InvoiceID) AND
(Invoices.InvoiceTotal >
LineItems.InvoiceLineItemAmount)
ORDER BY InvoiceNumber;

The result set

Murach's SQL Server 2012, C4

© 2012, Mike Murach & Associates, Inc.

Slide 12

The same join with the second condition
coded in a WHERE clause
SELECT InvoiceNumber, InvoiceDate,
InvoiceTotal, InvoiceLineItemAmount
FROM Invoices JOIN InvoiceLineItems AS LineItems
ON Invoices.InvoiceID = LineItems.InvoiceID
WHERE Invoices.InvoiceTotal >
LineItems.InvoiceLineItemAmount
ORDER BY InvoiceNumber;

The same result set

Murach's SQL Server 2012, C4

© 2012, Mike Murach & Associates, Inc.

Slide 13

A self-join that returns vendors from cities
in common with other vendors
SELECT DISTINCT Vendors1.VendorName, Vendors1.VendorCity,
Vendors1.VendorState
FROM Vendors AS Vendors1 JOIN Vendors AS Vendors2
ON (Vendors1.VendorCity = Vendors2.VendorCity) AND
(Vendors1.VendorState = Vendors2.VendorState) AND
(Vendors1.VendorID Vendors2.VendorID)
ORDER BY Vendors1.VendorState, Vendors1.VendorCity;

The result set

(84 rows)

Murach's SQL Server 2012, C4

© 2012, Mike Murach & Associates, Inc.

Slide 14

A SELECT statement that joins four tables
SELECT VendorName, InvoiceNumber, InvoiceDate,
InvoiceLineItemAmount AS LineItemAmount,
AccountDescription
FROM Vendors
JOIN Invoices ON Vendors.VendorID = Invoices.VendorID
JOIN InvoiceLineItems
ON Invoices.InvoiceID = InvoiceLineItems.InvoiceID
JOIN GLAccounts
ON InvoiceLineItems.AccountNo = GLAccounts.AccountNo
WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0
ORDER BY VendorName, LineItemAmount DESC;

Murach's SQL Server 2012, C4

© 2012, Mike Murach & Associates, Inc.

Slide 15

The first interim table

(11 rows)

Murach's SQL Server 2012, C4

© 2012, Mike Murach & Associates, Inc.

Slide 16

The second interim table

(11 rows)

Murach's SQL Server 2012, C4

© 2012, Mike Murach & Associates, Inc.

Slide 17

The final result set

(11 rows)

Murach's SQL Server 2012, C4

© 2012, Mike Murach & Associates, Inc.

Slide 18