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

SQL Workshop  Joins Doug Shook

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 2

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

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

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

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

More than two tables The first interim table (11 rows) The second interim table (11 rows) 7

More than two tables The final result set (11 rows)  Be careful! 8

Outer Joins  Will return all rows from one or both of the tables – Regardless of condition  Three types – Left – Right – Full 9

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

Outer Joins The Departments table The Employees table The Projects table 11

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 12

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 13

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 14

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 15

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

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 17

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

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

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

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

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 22

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

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