MySQL Joins

MySQL JOIN Operations: Combining Data across Tables

A MySQL JOIN combines columns from two or more tables based on a related key column shared between them.

---

Visualizing MySQL JOIN Types

flowchart LR
    A["Table A (Left)"] --- B["INNER JOIN (Matching Rows Only)"]
    A --- C["LEFT JOIN (All Left + Matching Right)"]
    D["Table B (Right)"] --- B
    D --- E["RIGHT JOIN (All Right + Matching Left)"]

---

Summary of JOIN Types

1. INNER JOIN: Returns rows with matching values in both tables.
2. LEFT JOIN: Returns all rows from the left table and matching rows from the right table.
3. RIGHT JOIN: Returns all rows from the right table and matching rows from the left table.
4. CROSS JOIN: Returns the Cartesian product of all rows from both tables.

---

Practical SQL Code Example

-- Query customer names alongside their orders
SELECT 
    c.first_name, 
    c.last_name, 
    o.order_id, 
    o.total_amount
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

Expected Query Output

first_name	last_name	order_id	total_amount
Jane	Doe	1001	149.99
Alice	Johnson	1002	89.50

---

Best Practices & Gotchas

• Index Foreign Keys: Ensure join columns (e.g. customer_id) are indexed to speed up table join lookups.
• Use Table Aliases: Use concise table aliases (c for customers, o for orders) for cleaner query readability.

---

Self-Check Challenge

Which JOIN type returns all records from the left table even if there are no matching records in the right table?