INNER JOIN vs LEFT JOIN — which rows survive

INNER JOIN: Only Matching Rows Survive

INNER JOIN returns only rows that have a matching row in both tables. If an order has a customer_id that doesn't exist in the customers table, that order is dropped from the result.

-- Only returns orders that have a valid customer
SELECT o.id, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

LEFT JOIN: Keep All Left Rows

LEFT JOIN returns every row from the left table (orders), plus matched columns from the right table where a match exists. Where there's no match, right-side columns are NULL.

-- Returns ALL orders; customers columns are NULL if no match
SELECT
  o.id,
  c.name,
  c.email
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id;

Using LEFT JOIN to Find Orphaned Rows

The pattern of filtering on NULL from the right side of a LEFT JOIN is a clean way to find rows with no match — customers who never ordered, products never sold:

-- Customers who have never placed an order
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

JOIN without INNER/LEFT/RIGHT defaults to INNER JOIN in all major databases.