Course contentsShow
← Data Science
Lesson 928 of 2,14524. SQL JoinsFree lesson

LEFT JOIN vs INNER JOIN: When to Use Each

Comparing result sets and deciding which join type fits your analytical question about data completeness.

LEFT JOIN vs INNER JOIN: When to Use Each

You'll learn: How to choose between LEFT JOIN and INNER JOIN based on whether you need to preserve unmatched records.

The Core Difference

The key question is: What happens to rows without a match?

  • INNER JOIN: Returns only rows where a match exists in both tables
  • LEFT JOIN: Returns all rows from the left table, plus matching rows from the right (or NULL if no match)

Think of it like a party invitation list:

  • INNER JOIN = Only people who RSVP'd "yes" get counted
  • LEFT JOIN = Everyone invited gets counted, whether they responded or not

When to Use INNER JOIN

Use INNER JOIN when you only care about complete, matching records:

  • Finding customers who have placed orders
  • Listing employees with assigned departments
  • Products that have been purchased
-- Only customers who made purchases
SELECT customers.name, orders.order_date
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;

When to Use LEFT JOIN

Use LEFT JOIN when you need to preserve all records from your primary table, even without matches:

  • Finding customers including those who never ordered
  • Listing all products including those never sold
  • Reporting all employees even those without department assignments
-- All customers, showing order info when available
SELECT customers.name, orders.order_date
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

The Analytical Question

Ask yourself: "Do I need to see records with missing data?" If yes → LEFT JOIN. If no → INNER JOIN.

Key Takeaway: Use INNER JOIN when both sides must match; use LEFT JOIN when you need all records from the left table regardless of matches, preserving data completeness for analysis.