SQL Workshop Joins Doug Shook Inner Joins Joins are used to - - PowerPoint PPT Presentation

SQL Workshop

Joins

Doug Shook

2

Inner Joins

 Joins are used to combine data from multiple tables

into one result

 Requires the name of the column from the second

table, along with a condition that defines the relationship

3

Inner Joins

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

An inner join of the Vendors and Invoices tables

SELECT InvoiceNumber, VendorName FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID;

4

Correlation Names

 AS can be used to set up an alias like we saw before

– Be careful to keep the name meaningful

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;

5

Compound conditions

 Which of the following do you prefer? Why?

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

6

More than two tables

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;

7

More than two tables

The first interim table

(11 rows)

The second interim table

(11 rows)

8

More than two tables

 Be careful!

The final result set

(11 rows)

9

Outer Joins

 Will return all rows from one or both of the tables

– Regardless of condition

 Three types

– Left – Right – Full

10

Outer Joins

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

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)

11

Outer Joins

The Departments table The Employees table The Projects table

12

Outer Joins

A left outer join

SELECT DeptName, Departments.DeptNo, LastName FROM Departments LEFT JOIN Employees ON Departments.DeptNo = Employees.DeptNo;

The result set A right outer join

SELECT DeptName, Employees.DeptNo, LastName FROM Departments RIGHT JOIN Employees ON Departments.DeptNo = Employees.DeptNo;

The result set

13

Outer Joins

A full outer join

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

The result set

14

Outer Joins

Join 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

15

Outer Joins

Join 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

16

Join Combinations

 Inner and outer joins can be used in the same query

– Cannot use implicit syntax

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

17

Unions

 Combines the results from two or more SELECT

statements

 Only works well for similar tables

– Must have same number of columns – Columns must have compatible types – Column names in the result are taken from the first select statement

18

Unions

A union that combines data from two different tables

SELECT 'Active' AS Source, InvoiceNumber, InvoiceDate, InvoiceTotal FROM ActiveInvoices WHERE InvoiceDate >= '02/01/2012' UNION SELECT 'Paid' AS Source, InvoiceNumber, InvoiceDate, InvoiceTotal FROM PaidInvoices WHERE InvoiceDate >= '02/01/2012' ORDER BY InvoiceTotal DESC; (72 rows)

19

Unions

A union that combines data from the same 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)

20

Exercises

 Write a SELECT statement that returns all columns

from the Vendors table joined with the Invoices table.

 Write a SELECT statement that returns four

columns: VendorName From the Vendors table InvoiceNumber From the Invoices table InvoiceDate From the Invoices table Balance InvoiceTotal minus the sum of PaymentTotal and CreditTotal The result set should have one row for each invoice with a non-zero balance. Sort by VendorName.

21

Exercises

 Write a SELECT statement that returns three

columns: VendorName From the Vendors table DefaultAccountNo From the Vendors table AccountDescription From the GLAccounts table Sort the result set by AccountDescription, then VendorName.

22

Exercises

 Write a SELECT statement that returns five columns

from three tables, all using column aliases: Vendor VendorName column Date InvoiceDate column Number InvoiceNumber column Sequence InvoiceSequence column LineItem InvoiceLineItemAmount column Sort the final result set by Vendor, Date, Number, and Sequence

23

Exercises

 Write a SELECT statement that returns two columns

from the GLAccounts table: AccountNo and

• AccountDescription. The result set should have one

row for each account number that has never been

• used. Sort the final result set by AccountNo.

Hint: Use an outer join to the InvoiceLineItems table.

24

Exercises

 Use the UNION operator to generate a result set

consisting of two columns from the Vendors table: VendorName and VendorState. If the vendor is in California, the VendorState should be "CA"; otherwise, the VendorState should be "Outside CA." Sort the final result set by VendorName.