Exercise 3: JOINs 🔗

🎯 Objective

Master different types of SQL JOINs to combine data from multiple tables.

📋 Problem Statement

You have three tables: products, orders, and suppliers.

CREATE TABLE suppliers (
  supplier_id INT PRIMARY KEY,
  supplier_name VARCHAR(100),
  country VARCHAR(50),
  rating DECIMAL(3, 2)
);

INSERT INTO suppliers VALUES
(101, 'TechCorp', 'USA', 4.5),
(102, 'ElectroSupply', 'China', 4.2),
(103, 'FurniturePro', 'Germany', 4.8),
(104, 'LightingWorld', 'Italy', 4.0),
(105, 'GlobalParts', 'Japan', 4.6);

Note: Products table has supplier_id foreign key. Some suppliers may not have products yet (supplier 105).

📥 Tasks

Task 1: Product-Supplier Details (INNER JOIN)

List all products with their supplier names and countries. Only show products that have suppliers.

Task 2: All Products with Supplier Info (LEFT JOIN)

Show all products, including those without supplier information (if any). Display product_name, price, and supplier_name.

Task 3: All Suppliers with Product Count (LEFT JOIN + Aggregation)

List all suppliers with the count of products they supply. Include suppliers with zero products.

Task 4: Orders with Full Details (Multiple JOINs)

Show order details including: order_id, product_name, quantity, order_date, and supplier_name.

Task 5: High-Rated Suppliers' Products

Find all products from suppliers with rating >= 4.5. Show product_name, price, supplier_name, and rating.

Task 6: Products Without Orders (LEFT JOIN + WHERE NULL)

Find products that have never been ordered.

💡 Hints

Hint 1: JOIN Types

- **INNER JOIN**: Returns only matching rows from both tables - **LEFT JOIN**: Returns all rows from left table + matching rows from right - **RIGHT JOIN**: Returns all rows from right table + matching rows from left - **FULL OUTER JOIN**: Returns all rows from both tables (not supported in MySQL)

Hint 2: JOIN Syntax

```sql SELECT columns FROM table1 JOIN table2 ON table1.key = table2.key ```

Hint 3: Finding Unmatched Rows

Use LEFT JOIN + WHERE right_table.key IS NULL

✅ Solutions

Click to reveal solutions

Solution 1: Product-Supplier Details (INNER JOIN)

SELECT 
  p.product_name,
  p.price,
  s.supplier_name,
  s.country
FROM products p
INNER JOIN suppliers s ON p.supplier_id = s.supplier_id
ORDER BY p.product_name;

Result: 8 rows (all products have suppliers)

Explanation: INNER JOIN returns only rows where supplier_id matches in both tables.

Alternative Syntax (implicit join):

SELECT p.product_name, p.price, s.supplier_name, s.country
FROM products p, suppliers s
WHERE p.supplier_id = s.supplier_id;

Note: Explicit JOIN syntax is preferred for readability.

---

Solution 2: All Products with Supplier Info (LEFT JOIN)

SELECT 
  p.product_name,
  p.price,
  s.supplier_name
FROM products p
LEFT JOIN suppliers s ON p.supplier_id = s.supplier_id
ORDER BY p.product_name;

Result: Same as INNER JOIN in this case (all products have suppliers).

Explanation: LEFT JOIN keeps all products even if supplier_id doesn't match. NULL would appear for supplier_name if no match.

When to use LEFT JOIN:

• When you want to keep all rows from the "main" table
• When checking for missing relationships
• When some foreign keys might be NULL

---

Solution 3: All Suppliers with Product Count (LEFT JOIN + Aggregation)

SELECT 
  s.supplier_name,
  s.country,
  COUNT(p.product_id) AS product_count
FROM suppliers s
LEFT JOIN products p ON s.supplier_id = p.supplier_id
GROUP BY s.supplier_id, s.supplier_name, s.country
ORDER BY product_count DESC, s.supplier_name;

Result:

supplier_name    | country | product_count
-----------------|---------|---------------
ElectroSupply    | China   | 3
TechCorp         | USA     | 2
FurniturePro     | Germany | 2
LightingWorld    | Italy   | 1
GlobalParts      | Japan   | 0

Key Point: COUNT(p.product_id) counts non-NULL product_ids. For GlobalParts (no products), it returns 0.

Common Mistake: Using COUNT(*) would return 1 for GlobalParts (counting the supplier row itself).

---

Solution 4: Orders with Full Details (Multiple JOINs)

SELECT 
  o.order_id,
  p.product_name,
  o.quantity,
  o.order_date,
  s.supplier_name
FROM orders o
JOIN products p ON o.product_id = p.product_id
JOIN suppliers s ON p.supplier_id = s.supplier_id
ORDER BY o.order_date;

Result: 10 rows with complete order information

Explanation: Chain multiple JOINs to connect orders → products → suppliers.

Join Order Matters (for readability):

• Start with the "main" table (orders)
• Join related tables in logical order
• Database optimizer will determine actual execution order

---

Solution 5: High-Rated Suppliers' Products

SELECT 
  p.product_name,
  p.price,
  s.supplier_name,
  s.rating
FROM products p
JOIN suppliers s ON p.supplier_id = s.supplier_id
WHERE s.rating >= 4.5
ORDER BY s.rating DESC, p.product_name;

Result:

product_name          | price   | supplier_name  | rating
----------------------|---------|----------------|-------
Office Chair          | 199.99  | FurniturePro   | 4.80
Standing Desk         | 499.99  | FurniturePro   | 4.80
Laptop Pro 15         | 1299.99 | TechCorp       | 4.50
Monitor 27"           | 349.99  | TechCorp       | 4.50

Explanation: JOIN first, then filter with WHERE. This is more efficient than filtering in JOIN condition for INNER JOINs.

---

Solution 6: Products Without Orders (LEFT JOIN + WHERE NULL)

SELECT 
  p.product_name,
  p.price,
  p.stock_quantity
FROM products p
LEFT JOIN orders o ON p.product_id = o.product_id
WHERE o.order_id IS NULL;

Result:

product_name    | price  | stock_quantity
----------------|--------|---------------
Office Chair    | 199.99 | 40
Standing Desk   | 499.99 | 15
Monitor 27"     | 349.99 | 30
Desk Lamp       | 45.99  | 60

Explanation:

1. LEFT JOIN keeps all products
2. Products without orders have NULL in orders columns
3. WHERE o.order_id IS NULL filters to unmatched products

Alternative (using NOT EXISTS):

SELECT p.product_name, p.price, p.stock_quantity
FROM products p
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.product_id = p.product_id
);

🚀 Follow-up Challenges

1. Self-Join: If products had a related_product_id, find products and their related products
2. CROSS JOIN: Generate all possible product-supplier combinations (Cartesian product)
3. Multiple Conditions: JOIN with multiple conditions (e.g., matching on two columns)
4. Anti-Join: Find suppliers who have never had their products ordered (combine techniques)

🔑 Key Takeaways

JOIN Types Comparison

JOIN Type	Returns
INNER JOIN	Only matching rows from both tables
LEFT JOIN	All rows from left + matching from right (NULL if no match)
RIGHT JOIN	All rows from right + matching from left (NULL if no match)
FULL OUTER JOIN	All rows from both tables (NULL where no match)
CROSS JOIN	Cartesian product (all combinations)

Best Practices

• Use explicit JOIN syntax (not implicit with WHERE)
• Always specify join conditions (avoid accidental CROSS JOINs)
• Use table aliases for readability (p, o, s instead of full names)
• Join on indexed columns for performance (usually primary/foreign keys)
• Consider join order for complex queries (though optimizer handles this)
• Use LEFT JOIN to find missing relationships (with WHERE NULL)

Performance Tips

• Index foreign keys - dramatically speeds up JOINs
• Filter early - use WHERE before JOIN when possible
• Limit columns - SELECT only needed columns, not *
• Avoid joining large tables without WHERE conditions
• Use EXPLAIN to analyze query execution plan

📚 Common Patterns

Pattern 1: Find Orphaned Records

-- Products without suppliers
SELECT p.*
FROM products p
LEFT JOIN suppliers s ON p.supplier_id = s.supplier_id
WHERE s.supplier_id IS NULL;

Pattern 2: Count Relationships

-- Suppliers with product counts
SELECT s.supplier_name, COUNT(p.product_id) AS product_count
FROM suppliers s
LEFT JOIN products p ON s.supplier_id = p.supplier_id
GROUP BY s.supplier_id, s.supplier_name;

Pattern 3: Multi-Level Joins

-- Orders → Products → Suppliers → Countries
SELECT o.order_id, p.product_name, s.supplier_name, s.country
FROM orders o
JOIN products p ON o.product_id = p.product_id
JOIN suppliers s ON p.supplier_id = s.supplier_id
WHERE s.country = 'USA';

Pattern 4: Conditional Joins

-- Join only high-value orders
SELECT p.product_name, o.quantity
FROM products p
JOIN orders o ON p.product_id = o.product_id AND o.quantity > 2;

⚠️ Common Pitfalls

⚠️ Cạm bẫy

Pitfall 1: Forgetting JOIN Condition

-- WRONG: Creates Cartesian product
SELECT * FROM products, suppliers;

-- CORRECT: Specify join condition
SELECT * FROM products p JOIN suppliers s ON p.supplier_id = s.supplier_id;

⚠️ Cạm bẫy

Pitfall 2: Using WHERE Instead of JOIN Condition

-- Works but less clear
SELECT * FROM products p, suppliers s WHERE p.supplier_id = s.supplier_id;

-- Better: Explicit JOIN
SELECT * FROM products p JOIN suppliers s ON p.supplier_id = s.supplier_id;

⚠️ Cạm bẫy

Pitfall 3: Confusing LEFT JOIN with WHERE

-- WRONG: WHERE converts LEFT JOIN to INNER JOIN
SELECT * FROM products p
LEFT JOIN suppliers s ON p.supplier_id = s.supplier_id
WHERE s.country = 'USA';  -- Filters out products without suppliers!

-- CORRECT: Use AND in JOIN condition
SELECT * FROM products p
LEFT JOIN suppliers s ON p.supplier_id = s.supplier_id AND s.country = 'USA';

---

📚 Bài liên quan

Related Topics

• ← Exercise 2: Aggregations
• → Exercise 4: Subqueries
• SQL JOINs Guide