No Slide Title

Transcript No Slide Title

Chapter 4 How to retrieve data from two or more tables

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. Murach’s SQL Server 2008, C4 © 2008, Mike Murach & Associates, Inc.

Slide 2

Objectives (continued)

  Describe the use of the implicit syntax for coding joins. Describe the use of unions including the use of the new EXCEPT and INTERSECT operators. Murach’s SQL Server 2008, C4 © 2008, 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]...

Notes

 The INNER keyword is optional and is seldom used.

 This syntax for coding an inner join can be referred to as the

explicit syntax

. It is also called the

SQL-92 syntax

because it was introduced by the SQL-92 standards.

Slide 4

How to code an inner join

 A

join

is used to combine columns from two or more tables into a result set based on the

join conditions

you specify.

 For an

inner join

, only those rows that satisfy the join condition are included in the result set.

 A join condition names a column in each of the two tables involved in the join and indicates how the two columns should be compared.

 In most cases, you’ll join two tables based on the relationship between the primary key in one table and a foreign key in the other table.

 You can also join tables based on relationships not defined in the database. These are called

ad hoc relationships

 If the columns in a join condition have the same name, you have to qualify them with the table names. To code a

qualified column name

, type the table name, a period, then the column name.

Slide 5

A SELECT statement that joins the Vendors and Invoices tables SELECT InvoiceNumber, VendorName FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID

The result set (114 rows)

Slide 6

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]...

Slide 7

When and how to use correlation names



Correlation names

are temporary table names assigned in the FROM clause. You can use them when long table names make qualified column names long or confusing.

  A correlation name can also be called a

table alias

If you assign a correlation name to a table, you must use that name to refer to the table within your query. You can’t use the original table name.

 Although the AS keyword is optional, it’s a good idea to use it because it makes the FROM clause easier to read.

 You can use a correlation name for any table in a join without using correlation names for all of the other tables.

 Use correlation names whenever they simplify or clarify the query.

Avoid using correlation names when they make a query more confusing or difficult to read.

Slide 8

An inner join with 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 (11 rows)

Slide 9

An inner join with 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 (6 rows)

Slide 10

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 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

Slide 11

The result set for a join using qualified table names (37 rows)

Slide 12

A stored procedure that adds a linked server

USE master; EXEC sp_addlinkedserver @server='DBServer', @srvproduct='', @provider='SQLNCLI', @datasrc='localhost\SqlExpress'

Slide 13

How to use compound join conditions

 A join condition can include two or more conditions connected by AND or OR operators.

 In most cases, your code will be easier to read if you code the join condition in the ON expression and search conditions in the WHERE clause.

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

Slide 14

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 result set

Slide 15

How to use a self-join

  A

self-join

is a join that joins a table with itself.

When you code a self-join, you must use correlation names for the tables, and you must qualify each column name with the correlation name.

 Self-joins frequently include the DISTINCT keyword to eliminate duplicate rows.

Slide 16

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)

Slide 17

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

Slide 18

The first interim table (11 rows)

Slide 19

The second interim table

(11 rows)

Slide 20

The final result set (11 rows)

Slide 21

Inner joins that join more than two tables

 You can think of a multi-table join as a series of two-table joins proceeding from left to right.

 The first two tables are joined to produce an

interim result set

interim table

. Then, the interim table is joined with the next table, and so on.

Slide 22

The implicit syntax for an inner join

SELECT select_list FROM table_1, table_2 [, table_3]... WHERE table_1.column_name operator table_2.column_name [AND table_2.column_name operator table_3.column_name]...

How to use the implicit inner join syntax

 Instead of coding a join condition in the FROM clause, you can code it in the WHERE clause along with any search conditions. Then, you list the tables you want to join in the FROM clause.  This syntax for coding joins is referred to as the

implicit syntax

, or the

theta syntax

Slide 23

A SELECT statement that joins the Vendors and Invoices tables SELECT InvoiceNumber, VendorName FROM Vendors, Invoices WHERE Vendors.VendorID = Invoices.VendorID

The result set

Slide 24

A statement that joins four tables SELECT VendorName, InvoiceNumber, InvoiceDate, InvoiceLineItemAmount AS LineItemAmount, AccountDescription FROM Vendors, Invoices, InvoiceLineItems, GLAccounts WHERE Vendors.VendorID = Invoices.VendorID

AND Invoices.InvoiceID = InvoiceLineItems.InvoiceID

AND InvoiceLineItems.AccountNo = GLAccounts.AccountNo

AND InvoiceTotal - PaymentTotal - CreditTotal > 0 ORDER BY VendorName, LineItemAmount DESC The result set

Slide 25

The explicit syntax for an outer join SELECT select_list FROM table_1 {LEFT|RIGHT|FULL} [OUTER] JOIN table_2 ON join_condition_1 [{LEFT|RIGHT|FULL} [OUTER] JOIN table_3 ON join_condition_2]...

How to code an outer join

 An

outer join

retrieves all rows that satisfy the join condition, plus unmatched rows in one or both tables.

 In most cases, you use the equal operator to retrieve rows with matching columns. However, you can also use any of the other comparison operators.

 When a row with unmatched columns is retrieved, any columns from the other table that are included in the result set are given null values.

Slide 26

What outer joins do Joins of this type

Left outer join Right outer join Full outer join

Keep unmatched rows from

Slide 27

A SELECT statement that uses a left outer join

SELECT VendorName, InvoiceNumber, InvoiceTotal FROM Vendors LEFT JOIN Invoices ON Vendors.VendorID = Invoices.VendorID ORDER BY VendorName (202 rows)

Slide 28

The Departments table

The Employees table

Slide 29

A left outer join SELECT DeptName, Departments.DeptNo, LastName FROM Departments LEFT JOIN Employees ON Departments.DeptNo = Employees.DeptNo

Slide 30

A right outer join SELECT DeptName, Employees.DeptNo, LastName FROM Departments RIGHT JOIN Employees ON Departments.DeptNo = Employees.DeptNo

Slide 31

A full outer join SELECT DeptName, Departments.DeptNo, Employees.DeptNo, LastName FROM Departments FULL JOIN Employees ON Departments.DeptNo = Employees.DeptNo

Slide 32

The Departments table

The Employees table The Projects table

Slide 33

A SELECT statement that joins the three tables using left outer joins SELECT DeptName, LastName, ProjectNo FROM Departments LEFT JOIN Employees ON Departments.DeptNo = Employees.DeptNo

LEFT JOIN Projects ON Employees.EmployeeID = Projects.EmployeeID

ORDER BY DeptName, LastName, ProjectNo The result set

Slide 34

A SELECT statement that joins the three tables using full outer joins

SELECT DeptName, LastName, ProjectNo FROM Departments FULL JOIN Employees ON Departments.DeptNo = Employees.DeptNo

FULL JOIN Projects ON Employees.EmployeeID = Projects.EmployeeID

ORDER BY DeptName

The result set

Slide 35

The implicit syntax for an outer join (deprecated) SELECT select_list FROM table_1, table_2 [, table 3]... WHERE table_1.column_name {*=|=*} table_2.column_name [table_2.column_name {*=|=*} table_3.column_name]... The implicit outer join operators Operator *= =* Description

Slide 36

How to use the implicit outer join syntax

 The implicit, or theta, syntax for outer joins is an old syntax that was used prior to the SQL-92 standards, and it has been deprecated with SQL Server 2005.  The implicit outer join syntax is only allowed on SQL Server 2005 when the compatibility level for the database is set to SQL Server 2000 or earlier.  You can’t perform a full outer join using the implicit syntax. Murach’s SQL Server 2008, C4 © 2008, Mike Murach & Associates, Inc.

Slide 37

A SELECT statement that joins two tables using a left outer join SELECT VendorName, InvoiceNumber, InvoiceTotal FROM Vendors, Invoices WHERE Vendors.VendorID *= Invoices.VendorID

ORDER BY VendorName (202 rows)

Slide 38

How to combine inner and outer joins

 You can combine inner and outer joins within a single SELECT statement using the explicit join syntax. You can’t combine them using the implicit syntax.

A SELECT statement that combines an outer and an inner join SELECT DeptName, LastName, ProjectNo FROM Departments JOIN Employees ON Departments.DeptNo = Employees.DeptNo

LEFT JOIN Projects ON Employees.EmployeeID = Projects.EmployeeID

ORDER BY DeptName

Slide 39

The Departments table

The Employees table The Projects table

Slide 40

The interim table for the combined inner and outer joins The result set

Slide 41

How to code a cross join using the explicit syntax The explicit syntax for a cross join SELECT select_list FROM table_1 CROSS JOIN table_2 A cross join that uses the explicit syntax SELECT Departments.DeptNo, DeptName, EmployeeID, LastName FROM Departments CROSS JOIN Employees ORDER BY Departments.DeptNo

How to code a cross join using the implicit syntax The implicit syntax for a cross join SELECT select_list FROM table_1, table_2 A cross join that uses the implicit syntax SELECT Departments.DeptNo, DeptName, EmployeeID, LastName FROM Departments, Employees ORDER BY Departments.DeptNo

Slide 42

The result set for both cross joins

(45 rows) How to use cross joins

 A

cross join

joins each row from the first table with each row from the second table. The result set is known as a

Cartesian product

 To code a cross join using the explicit syntax, use the CROSS JOIN keywords in the FROM clause.

 To code a cross join using the implicit syntax, list the tables in the FROM clause and omit the join condition from the WHERE clause.

Slide 43

The syntax for a union operation SELECT_statement_1 UNION [ALL] SELECT_statement_2 [UNION [ALL] SELECT_statement_3]...

[ORDER BY order_by_list] How to combine data from different tables

 A

union

combines the result sets of two or more SELECT statements into one result set.

 Each result set must return the same number of columns, and corresponding columns must have the same data type.

 If you want to keep duplicate rows, code the ALL keyword.

 The column names in the final result set are taken from the first SELECT clause. If an ORDER BY clause is coded, it must refer to those column names.

Slide 44

A union that combines data from two tables SELECT 'Active' AS Source, InvoiceNumber, InvoiceDate, InvoiceTotal FROM ActiveInvoices WHERE InvoiceDate >= '06/01/2008' UNION SELECT 'Paid' AS Source, InvoiceNumber, InvoiceDate, InvoiceTotal FROM PaidInvoices WHERE InvoiceDate >= '06/01/2008' ORDER BY InvoiceTotal DESC The result set 72 rows)

Slide 45

A union that combines data from the Invoices table

SELECT 'Active' AS Source, InvoiceNumber, InvoiceDate, InvoiceTotal FROM Invoices WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0 UNION SELECT 'Paid' AS Source, InvoiceNumber, InvoiceDate, InvoiceTotal FROM Invoices WHERE InvoiceTotal - PaymentTotal - CreditTotal <= 0 ORDER BY InvoiceTotal DESC

The result set

(114 rows)

Slide 46

A union that combines payment data from the same joined tables SELECT InvoiceNumber, VendorName, '33% Payment' AS PaymentType, InvoiceTotal AS Total, (InvoiceTotal * 0.333) AS Payment FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID

WHERE InvoiceTotal > 10000 UNION SELECT InvoiceNumber, VendorName, '50% Payment' AS PaymentType, InvoiceTotal AS Total, (InvoiceTotal * 0.5) AS Payment FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID

WHERE InvoiceTotal BETWEEN 500 AND 10000 UNION SELECT InvoiceNumber, VendorName, 'Full amount' AS PaymentType, InvoiceTotal AS Total, InvoiceTotal AS Payment FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID

WHERE InvoiceTotal < 500 ORDER BY PaymentType, VendorName, InvoiceNumber

Slide 47

The result set for the union that combines payment data from joined tables

(114 rows)

Slide 48

The syntax for the EXCEPT and INTERSECT operations SELECT_statement_1 {EXCEPT|INTERSECT} SELECT_statement_2 [ORDER BY order_by_list]

Slide 49

The Customers table The Employees table

(24 rows) (9 rows)

Slide 50

A query that excludes rows from the first query if they also occur in the second query SELECT CustomerFirst, CustomerLast FROM Customers EXCEPT SELECT FirstName, LastName FROM Employees ORDER BY CustomerLast The result set (23 rows)

Slide 51

A query that only includes rows that occur in both queries

SELECT CustomerFirst, CustomerLast FROM Customers INTERSECT SELECT FirstName, LastName FROM Employees

The result set

(1 row)

Slide 52