1.4 Combine Tables with Joins

• Returns rows where the join condition matches in both tables.
• Any unmatched rows are discarded.

Think of it as an intersection (common area) between two data circles.

Logic:

SELECT c.customer_id, c.name, o.amount
FROM customers c
INNER JOIN orders o 
  ON c.customer_id = o.customer_id;

Conceptually: “Show me customers who actually placed an order.”

Result: Customers without orders won’t appear.

• Returns all rows from the left table, even if there’s no match in the right.
• Missing values from the right side become NULL.

SELECT c.customer_id, c.name, o.amount
FROM customers c
LEFT JOIN orders o 
  ON c.customer_id = o.customer_id;

Result: Every customer is listed. Those without orders will have NULL in amount.

Analogy: Left JOIN = “All customers, and their orders if they have them.”

• The mirror image of LEFT JOIN.
• Keeps all rows from the right table.

SELECT c.customer_id, c.name, o.amount
FROM customers c
RIGHT JOIN orders o 
  ON c.customer_id = o.customer_id;

Result: Every order appears, even if there’s no matching customer.

Note: Some databases (like SQLite) don’t support RIGHT JOIN — use LEFT JOIN with reversed order instead.

• Returns all rows from both tables.
• Missing matches from either side are filled with NULL.

SELECT c.customer_id, c.name, o.amount
FROM customers c
FULL OUTER JOIN orders o 
  ON c.customer_id = o.customer_id;

Result: Complete data universe — customers with or without orders, and even stray orders.

Analogy: “Include everyone, whether they match or not.”

• No condition, no mercy — every row in one table is joined with every row in another.

SELECT *
FROM products p
CROSS JOIN stores s;

Result: If there are 10 products and 5 stores → you get 50 rows. Useful for generating combinations, but dangerous for large tables.

SELECT c.customer_id, c.name, o.order_id, o.amount
FROM customers c
INNER JOIN orders o 
  ON c.customer_id = o.customer_id;

Explanation: Shows only customers who placed at least one order. If customer 7 never ordered — they won’t appear.

SELECT c.customer_id, c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o 
  ON c.customer_id = o.customer_id;

Explanation: All customers appear. If they never ordered, order columns will be NULL.

Business Value: Perfect for customer engagement reports — “Which customers haven’t purchased yet?”

SELECT c.customer_id, c.name, o.amount
FROM customers c
LEFT JOIN orders o 
  ON c.customer_id = o.customer_id
WHERE o.amount > 0;

⚠️ Problem: This filter (WHERE o.amount > 0) removes NULL rows — effectively converting your LEFT JOIN into an INNER JOIN!

✅ Fix: Move filter into the JOIN condition:

SELECT c.customer_id, c.name, o.amount
FROM customers c
LEFT JOIN orders o 
  ON c.customer_id = o.customer_id AND o.amount > 0;

Now non-ordering customers remain visible. 🎉

• “Explain the difference between INNER and LEFT JOIN.”
• “Why would a LEFT JOIN return fewer rows than the left table?”
• “What happens if the join condition is wrong?”
• “Can you simulate a RIGHT JOIN using a LEFT JOIN?”

Interviewers want to see data reasoning:

“LEFT JOIN keeps all rows from the left side — unless a WHERE clause removes NULLs, which effectively turns it into an INNER JOIN.”

Bonus points if you can explain with an example or diagram.

Find all customers who have not placed any orders.

SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o 
  ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

This is the classic “anti-join” pattern — and a favorite interview test!

• ❌ Forgetting the ON condition → accidental CROSS JOIN explosion.
• ❌ Filtering on WHERE instead of ON in LEFT JOINs.
• ❌ Mismatched data types on join keys → no matches.
• ❌ Duplicate keys → multiplied rows.

💡 Debug Tip: Start with a LIMIT 10 and inspect NULL patterns — they reveal whether you’re joining correctly.