INNER JOIN vs LEFT JOIN in SQL — the real difference, with examples

An INNER JOIN returns only rows where the join condition matches on both sides. A LEFT JOIN returns all rows from the left table, with NULLs for non-matching right-side columns.

Setup

CREATE TABLE customers (id INT, name TEXT);
CREATE TABLE orders    (id INT, customer_id INT, total DECIMAL);

INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol');
INSERT INTO orders    VALUES (101, 1, 500), (102, 1, 200), (103, 2, 999);

INNER JOIN — matched only

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

name	total
Alice	500
Alice	200
Bob	999

Carol disappears — she has no order.

LEFT JOIN — keep every customer

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

name	total
Alice	500
Alice	200
Bob	999
Carol	NULL

Carol is preserved with NULL on the order side.

When to pick which

• Reporting customers + their orders? LEFT JOIN — you want every customer.
• Reporting orders + their customers? INNER JOIN — orders without a customer are data corruption.
• Finding "customers with no orders"? LEFT JOIN + WHERE o.id IS NULL.

Common interview trap

SELECT c.name FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.total > 100;   -- ❌ this silently becomes an INNER JOIN!

Filtering on the right-side column in WHERE strips NULL rows. Put the filter in the ON clause to preserve LEFT JOIN behavior:

LEFT JOIN orders o ON o.customer_id = c.id AND o.total > 100