Lecture Notes: Joins in MySQL

Introduction

• Joins: A clause used to combine rows from two or more tables based on a related column.
• Example Setup:
  • Two tables:
    • Transactions
    • Customers
  • Venn Diagram Analogy:
    • Transactions on the left, Customers on the right.
    • Common data represented by the middle of the Venn diagram.

Demonstration Setup

• Transactions Table:
  • Example row with amount: $1, customer ID: null (e.g., cash transaction).
  • Some rows may not have a customer ID.
• Customers Table:
  • Example customer: Poppy Puff.
  • Some customers may not have made any transactions.

Types of Joins

Inner Join

• Purpose: To select rows that have matching values in both tables.
• Syntax:
  • SELECT * FROM transactions INNER JOIN customers ON transactions.customer_ID = customers.customer_ID
• Result: Only displays rows with matching customer_IDs.
  • Excludes rows without matching customer IDs, e.g., $1 transaction or Poppy Puff.

Left Join

• Purpose: To select all rows from the left table and matching rows from the right table.
• Syntax:
  • SELECT * FROM transactions LEFT JOIN customers ON transactions.customer_ID = customers.customer_ID
• Result: Includes all transactions:
  • Displays all transactions even if they don't have a matching customer_ID in the right table.

Right Join

• Purpose: To select all rows from the right table and matching rows from the left table.
• Syntax:
  • SELECT * FROM customers RIGHT JOIN transactions ON transactions.customer_ID = customers.customer_ID
• Result: Includes all customers:
  • Displays all customers even if they haven't initiated any transaction.

Practical Application

• Selective Column Display:
  • Ability to select specific columns such as transaction ID, amount, first name, and last name.
  • Useful for identifying who initiated specific transactions.

Conclusion

• Joins Overview:
  • Joins are used to combine rows from tables based on related columns like a foreign key (e.g., customer_ID).
  • Provides a foundational understanding of how to link tables in MySQL.