SQL INNER JOIN 🕸️¶

Mentor's Note: Data is rarely stored in a single table. You have "Customers" in one place and "Orders" in another. INNER JOIN is the handshake that introduces them. It brings together only the data that matches in both places. 💡

🌟 The Scenario: The Dinner Date 🍽️¶

Imagine you are arranging a dinner. - Table A (People): A list of all your friends. - Table B (RSVPs): A list of people who said "Yes". - INNER JOIN: The people who actually show up to eat (Friends AND RSVP'd). If a friend didn't RSVP, they aren't on the list. If an RSVP has no name, it's ignored. 🚫

🎨 Visual Logic: The Intersection¶

flowchart LR
    A((Table A<br/>ID:1,2)) -.->|match| I((Intersection<br/>ID:1))
    B((Table B<br/>ID:1,3)) -.->|match| I

    A2[Only A: ID 2]
    B3[Only B: ID 3]

    style A fill:#4caf50,stroke:#333,stroke-width:3px
    style B fill:#2196f3,stroke:#333,stroke-width:3px
    style I fill:#ffbc3b,stroke:#333,stroke-width:4px

Only data present in BOTH tables (the highlighted intersection) is included in the result.

💻 1. The Basic Syntax¶

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Example: Who ordered what?¶

-- Scenario: Show Customer Name and their Order Date
SELECT customers.name, orders.order_date
FROM customers
INNER JOIN orders
ON customers.id = orders.customer_id;

💻 2. Joining Multiple Tables (The Chain) 🔗¶

You can join as many tables as you like!

-- Scenario: Customer -> Order -> Product
SELECT C.name, O.order_date, P.product_name
FROM customers C
JOIN orders O ON C.id = O.customer_id
JOIN order_details D ON O.id = D.order_id
JOIN products P ON D.product_id = P.id;

⚠️ Important Rules¶

Ambiguity: If both tables have a column named id, you must use table.id or alias.id.
Data Loss: Any row that does not find a match is hidden. (Use OUTER JOIN if you want to keep them).