What’s the difference between inner join, left join, and outer join?

1. INNER JOIN

• Returns only the matching rows between two tables.
• If there’s no match, that row is excluded. ✅ Useful when you only care about the overlap between tables.

Example:

SELECT A.id, A.name, B.order_id
FROM Customers A
INNER JOIN Orders B
ON A.id = B.customer_id;

➡️ Returns only customers who have placed orders.

2. LEFT JOIN (or LEFT OUTER JOIN)

• Returns all rows from the left table, and the **matching rows from the right table**.
• If there’s no match in the right table, you still get the left row, but with NULL for missing right-side values. ✅ Useful when you want everything from the left table, regardless of matches.

Example:

SELECT A.id, A.name, B.order_id
FROM Customers A
LEFT JOIN Orders B
ON A.id = B.customer_id;

➡️ Returns all customers, including those who never placed orders (their order_id will be NULL).

3. FULL OUTER JOIN (or just OUTER JOIN)

• Returns all rows from both tables.
• If there’s a match → show it.
• If a row exists in only one table → show it with NULL for the other side. ✅ Useful when you want a **complete dataset**
• from both sides.

Example:

SELECT A.id, A.name, B.order_id
FROM Customers A
FULL OUTER JOIN Orders B
ON A.id = B.customer_id;

➡️ Returns:

• Customers with orders,
• Customers without orders,
• Orders without matching customers.

📊 Visual Summary (Venn Diagram style):

• INNER JOIN → Only the intersection (overlap).
• LEFT JOIN → All of Left + matching part of Right.
• FULL OUTER JOIN → Union of both, with NULLs where no match exists.