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

1. Use explicit syntax for inner joins to retrieve data from one or more tables.

2. Code unions to combine data from multiple tables.

Knowledge Requirements

1. Understand when to qualify column names.

2. Learn to use table aliases effectively.

3. Differentiate between various join types:

   • Inner Join

   • Left Outer Join

   • Right Outer Join

   • Full Outer Join

   • Cross Join

4. Discuss implicit syntax for inner joins.

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

1. Left Outer Join: Keeps unmatched rows from the first table.

2. Right Outer Join: Keeps unmatched rows from the second table.

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