Chapter 04 slides
Chapter Overview
Focuses on retrieving data from multiple tables using SQL in SQL Server 2022.
Covers inner joins, outer joins, unions, and set operations.
Objectives
Applied Knowledge
Use explicit syntax for inner joins to retrieve data from one or more tables.
Code unions to combine data from multiple tables.
Knowledge Requirements
Understand when to qualify column names.
Learn to use table aliases effectively.
Differentiate between various join types:
Inner Join
Left Outer Join
Right Outer Join
Full Outer Join
Cross Join
Discuss implicit syntax for inner joins.
Utilize set operators: UNION, EXCEPT, INTERSECT.
Inner Joins
Syntax
Explicit Syntax:
SELECT select_list FROM table_1 [INNER] JOIN table_2 ON join_condition_1
Example of Inner Join between Vendors and Invoices:
SELECT InvoiceNumber, VendorName FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID;
Using Table Aliases
Syntax with Aliases:
SELECT select_list FROM table_1 [AS] n1 INNER JOIN table_2 [AS] n2 ON n1.column_name = n2.column_name
Without Aliases Example:
SELECT InvoiceNumber, VendorName, InvoiceDueDate, InvoiceTotal - PaymentTotal - CreditTotal AS BalanceDue FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0 ORDER BY InvoiceDueDate DESC;
Fully Qualifying Object Names
Fully Qualified Syntax Example:
SELECT VendorName, CustLastName, CustFirstName, VendorState AS State, VendorCity AS City FROM AP.dbo.Vendors v JOIN ProductOrders.dbo.Customers c ON v.VendorZipCode = c.CustZip ORDER BY State, City;
Partially Qualified Example:
SELECT VendorName, CustLastName, CustFirstName, VendorState AS State, VendorCity AS City FROM Vendors v JOIN ProductOrders..Customers c ON v.VendorZipCode = c.CustZip ORDER BY State, City;
Joining with Conditions
Inner Join with Two Conditions
Example of Inner Join:
SELECT InvoiceNumber, InvoiceDate, InvoiceTotal, InvoiceLineItemAmount FROM Invoices i JOIN InvoiceLineItems li ON i.InvoiceID = li.InvoiceID AND i.InvoiceTotal > li.InvoiceLineItemAmount
Self-Joins
Self-Join Example
Returns vendors from the same city:
SELECT DISTINCT v1.VendorName, v1.VendorCity, v1.VendorState FROM Vendors v1 JOIN Vendors v2 ON v1.VendorCity = v2.VendorCity AND v1.VendorState = v2.VendorState AND v1.VendorID <> v2.VendorID ORDER BY v1.VendorState, v1.VendorCity;
Outer Joins
Types of Outer Joins
Left Outer Join: Keeps unmatched rows from the first table.
Right Outer Join: Keeps unmatched rows from the second table.
Full Outer Join: Keeps unmatched rows from both tables.
Syntax for Outer Join
Explicit Syntax:
SELECT select_list FROM table_1 {LEFT|RIGHT|FULL} OUTER JOIN table_2 ON join_condition
Using Set Operations
UNION Syntax
Basic Syntax:
SELECT_statement_1 UNION SELECT_statement_2 [ORDER BY order_by]
Example of Union Operation
Combining data from two tables:
SELECT 'Active' AS Source, InvoiceNumber, InvoiceDate, InvoiceTotal FROM ActiveInvoices WHERE InvoiceDate >= '01/01/2023' UNION SELECT 'Paid' AS Source, InvoiceNumber, InvoiceDate, InvoiceTotal FROM PaidInvoices WHERE InvoiceDate >= '01/01/2023' ORDER BY InvoiceTotal DESC;
Set Operators: EXCEPT and INTERSECT
Syntax for EXCEPT and INTERSECT
Basic Structure:
SELECT_statement_1 {EXCEPT|INTERSECT} SELECT_statement_2 [ORDER BY order_by]
EXCEPT Example
Excluding rows:
SELECT CustomerFirst, CustomerLast FROM Customers EXCEPT SELECT FirstName, LastName FROM Employees ORDER BY CustomerLast;
INTERSECT Example
Including only rows present in both sets:
SELECT CustomerFirst, CustomerLast FROM Customers INTERSECT SELECT FirstName, LastName FROM Employees;