INNER JOIN vs LEFT JOIN: Examples, NULLs, and Common SQL Mistakes

Use INNER JOIN when you need only matching rows. Use LEFT JOIN when every row from the left table must remain, even without a match on the right. That choice matters for customer reports, missing-data checks, and interview questions.

Suppose you need to show customers and their orders. A customer with two orders appears twice: a join returns matching combinations, not automatically one row per customer. A customer with no orders disappears from an INNER JOIN but remains in a LEFT JOIN with NULL order columns.

Your reporting question	Join to choose
Which customers have orders?	INNER JOIN
Show every customer, including those without orders	LEFT JOIN
Find customers without orders	LEFT JOIN followed by a NULL check

The examples below make this difference visible without relying on a diagram.

Try it yourself in SQLite

Open SQL Lab, paste this setup, and run it once. Then run each query separately. Table and column names stay in English so the same SQL works in every language. The lab interface and linked lessons are currently in English.

CREATE TABLE blog_customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE blog_orders (
  id INTEGER PRIMARY KEY, customer_id INTEGER, status TEXT, amount INTEGER
);
INSERT INTO blog_customers VALUES (1, 'Asha'), (2, 'Luis'), (3, 'Mei');
INSERT INTO blog_orders VALUES
  (101, 1, 'paid', 120), (102, 1, 'pending', 80), (103, 2, 'paid', 60);

1. INNER JOIN returns matches

Asha has two orders, Luis has one, and Mei has none. The inner join returns three rows. Mei is absent because no order satisfies the join condition.

SELECT c.name, o.id AS order_id, o.status
FROM blog_customers AS c
INNER JOIN blog_orders AS o ON o.customer_id = c.id
ORDER BY c.id, o.id;

Expected result

name	order_id	status
Asha	101	paid
Asha	102	pending
Luis	103	paid

2. LEFT JOIN preserves customers

Now Mei appears too. The left join adds a row with NULL right-side values when a customer has no matching order. Asha still appears twice; preserving customers does not remove one-to-many matches.

SELECT c.name, o.id AS order_id, o.status
FROM blog_customers AS c
LEFT JOIN blog_orders AS o ON o.customer_id = c.id
ORDER BY c.id, o.id;

Expected result

name	order_id	status
Asha	101	paid
Asha	102	pending
Luis	103	paid
Mei	NULL	NULL

NULL means no matching value; it is not zero or an empty string.

3. Filter in ON to preserve unmatched customers

To list every customer with only their paid orders, put the paid-order condition in ON. A customer with only pending orders would also remain, with NULL order columns. The condition controls which orders can match.

SELECT c.name, o.id AS order_id
FROM blog_customers AS c
LEFT JOIN blog_orders AS o
  ON o.customer_id = c.id AND o.status = 'paid'
ORDER BY c.id, o.id;

Expected result

name	order_id
Asha	101
Luis	103
Mei	NULL

4. WHERE can remove the rows you meant to keep

Moving the same condition to WHERE changes the question. The WHERE filter runs against the joined rows. Mei’s NULL status does not satisfy status = 'paid', so Mei disappears. This is a common reason a LEFT JOIN behaves like an INNER JOIN for the rows being reported.

SELECT c.name, o.id AS order_id
FROM blog_customers AS c
LEFT JOIN blog_orders AS o ON o.customer_id = c.id
WHERE o.status = 'paid'
ORDER BY c.id, o.id;

Expected result

name	order_id
Asha	101
Luis	103

5. Count actual orders, not the placeholder row

COUNT(o.id) ignores the NULL order ID for Mei and correctly returns zero. COUNT(*) would count the preserved customer row and report one. Because the order ID is a non-null primary key, it is a reliable column to count here.

SELECT c.name, COUNT(o.id) AS order_count
FROM blog_customers AS c
LEFT JOIN blog_orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;

Expected result

name	order_count
Asha	2
Luis	1
Mei	0

Practice and check your answer

Find customers who have no paid orders, even if they have pending orders. Start with the paid-order LEFT JOIN above and add WHERE o.id IS NULL before ORDER BY. The answer in this dataset is Mei.

Use a non-null right-side key for the NULL check. Checking a nullable field such as a delivery note can mistake a real matched order for an absent order. Avoid “fixing” duplicate rows with DISTINCT until you understand the relationship: it can hide a wrong join condition.

For a report with one row per customer, aggregate after the join, as in the count example. For order-level reporting, keep the separate order rows. Decide the intended row granularity before writing the SELECT list.

Documentation and next steps

Practice INNER JOIN and LEFT/RIGHT JOIN. These examples run in SQLite and use syntax also supported by PostgreSQL and MySQL. Other join types and optimizer behavior depend on the database.

• SQLite reference
• Database reference