DAY 6: SQL - JOIN Operations

📝 Setup Instructions:

1. Open SQLite Browser with your database
2. You should have 3 tables: Orders, Customers, Products
3. Orders table: OrderID, CustomerID, ProductID, Quantity, OrderDate
4. Customers table: CustomerID, CustomerName, Region, Email
5. Products table: ProductID, ProductName, Category, Price

💡 JOINs = SQL's VLOOKUP: Remember Day 5 (Excel VLOOKUP)? SQL JOINs do the same thing - combine related data from multiple tables! But JOINs are more powerful and flexible.

Exercise 1: Your First INNER JOIN - Add Customer Names

Step-by-Step Instructions:

1. Type this query to join Orders with Customers:

   SELECT
       Orders.OrderID,
       Orders.CustomerID,
       Customers.CustomerName,
       Customers.Region,
       Orders.Quantity
   FROM Orders
   INNER JOIN Customers
       ON Orders.CustomerID = Customers.CustomerID
   LIMIT 20;

2. Click Execute (▶️)
3. You'll see order data WITH customer details!

🔍 Understanding the Syntax:

• FROM Orders = Start with Orders table (left table)
• INNER JOIN Customers = Connect to Customers table (right table)
• ON Orders.CustomerID = Customers.CustomerID = Matching condition (like VLOOKUP lookup value)
• Orders.OrderID = Specify which table each column comes from

💡 Tips:

• INNER JOIN returns only rows where match exists in BOTH tables
• Always specify table name for columns: Orders.CustomerID
• The ON clause is like the VLOOKUP match - it connects related records

✅ Expected Result: Orders with customer names and regions added

Exercise 2: JOIN to Add Product Information

Step-by-Step Instructions:

1. Now join Orders with Products to get product details:

   SELECT
       Orders.OrderID,
       Orders.ProductID,
       Products.ProductName,
       Products.Category,
       Products.Price,
       Orders.Quantity,
       (Products.Price * Orders.Quantity) AS TotalPrice
   FROM Orders
   INNER JOIN Products
       ON Orders.ProductID = Products.ProductID
   ORDER BY TotalPrice DESC
   LIMIT 20;

2. Notice we calculated TotalPrice directly in the query!
3. This is the SQL equivalent of Day 5's VLOOKUP + multiplication

💡 JOIN vs VLOOKUP Comparison:

• Excel Day 5: =VLOOKUP(C2, Products!A:D, 4, FALSE)
• SQL Day 6: INNER JOIN Products ON Orders.ProductID = Products.ProductID
• Same concept, different syntax!

✅ Expected Result: Orders with product names, categories, prices, and calculated totals

Exercise 3: LEFT JOIN - Include ALL Orders (Even Without Matches)

Step-by-Step Instructions:

1. LEFT JOIN keeps ALL rows from the left table, even if no match exists:

   SELECT
       Orders.OrderID,
       Orders.CustomerID,
       Customers.CustomerName,
       Customers.Region
   FROM Orders
   LEFT JOIN Customers
       ON Orders.CustomerID = Customers.CustomerID;

2. If an OrderID has a CustomerID not in the Customers table, it still shows (with NULL for customer fields)
3. Try finding orders with missing customers:

   SELECT
       Orders.OrderID,
       Orders.CustomerID,
       Customers.CustomerName
   FROM Orders
   LEFT JOIN Customers
       ON Orders.CustomerID = Customers.CustomerID
   WHERE Customers.CustomerName IS NULL;

   This finds "orphaned" orders - orders without matching customers!

🎯 JOIN Types Comparison:

• INNER JOIN: Only rows with matches in BOTH tables (most common)
• LEFT JOIN: ALL rows from left table + matching rows from right (right can be NULL)
• RIGHT JOIN: ALL rows from right table + matching rows from left (left can be NULL)

✅ Expected Result: All orders, including those without matching customers (if any)

Exercise 4: Three-Table JOIN - Complete Order View

Step-by-Step Instructions:

1. Join all three tables for a comprehensive view:

   SELECT
       Orders.OrderID,
       Customers.CustomerName,
       Customers.Region,
       Products.ProductName,
       Products.Category,
       Products.Price,
       Orders.Quantity,
       (Products.Price * Orders.Quantity) AS TotalPrice
   FROM Orders
   INNER JOIN Customers
       ON Orders.CustomerID = Customers.CustomerID
   INNER JOIN Products
       ON Orders.ProductID = Products.ProductID
   ORDER BY TotalPrice DESC
   LIMIT 30;

2. This single query combines data from 3 tables!
3. You can add WHERE clauses too:

   WHERE Customers.Region = 'West' AND Products.Category = 'Technology'

💡 Multiple JOINs:

• Stack JOINs one after another
• Each JOIN connects to the previous result
• You can join as many tables as needed
• This is database normalization in action!

✅ Expected Result: Complete order information with customer and product details in one query

Exercise 5: JOIN + GROUP BY - Aggregate Across Tables

Step-by-Step Instructions:

1. Calculate total sales per customer using JOIN + GROUP BY:

   SELECT
       Customers.CustomerName,
       Customers.Region,
       COUNT(Orders.OrderID) AS TotalOrders,
       SUM(Products.Price * Orders.Quantity) AS TotalSales
   FROM Orders
   INNER JOIN Customers
       ON Orders.CustomerID = Customers.CustomerID
   INNER JOIN Products
       ON Orders.ProductID = Products.ProductID
   GROUP BY Customers.CustomerName, Customers.Region
   ORDER BY TotalSales DESC
   LIMIT 10;

2. This finds your top 10 customers by total sales!
3. Try these variations:
   • Total sales by Region: GROUP BY Customers.Region
   • Total sales by Category: GROUP BY Products.Category
   • Top products: GROUP BY Products.ProductName

🎯 Combining Concepts:

This exercise combines:

• Day 2: SELECT, aggregate functions (SUM, COUNT)
• Day 4: WHERE clause (can add filters)
• Day 6: JOINs (connecting tables)
• Plus: GROUP BY (we'll cover more in Day 8)

✅ Expected Result: Customer ranking by total sales value

🎯 Practice Goal Checkpoint:

By completing these 5 exercises, you should now be able to:

• Use INNER JOIN to combine data from multiple tables
• Understand the ON clause (matching condition)
• Use LEFT JOIN to include all rows from one table
• Join three or more tables in a single query
• Combine JOINs with WHERE, GROUP BY, and aggregates
• See the parallel between SQL JOINs and Excel VLOOKUPs

Key Insight: JOINs are how databases connect related data. Instead of storing everything in one giant table (like Excel), we normalize data into separate tables and JOIN them when needed. This is more efficient and reduces data duplication!

Excel vs SQL Data Combination:

• Excel (Day 5): VLOOKUP pulls one value at a time
• SQL (Day 6): JOIN combines entire tables at once
• Same goal, but SQL is more powerful for large datasets!

Take a 5-minute break, then move on to Independent Practice!