SQL INNER JOIN vs LEFT JOIN

A join combines related rows from two tables. In this example, customers may have orders, and each order belongs to a customer. The difference between an INNER JOIN and a LEFT JOIN is what happens to a customer without a matching order.

Start with the sample data

The customers table has three customers. Ben has no order. The orders table has three orders, each linked to a customer by customer_id.

The matching key is customers.customer_id = orders.customer_id. A customer can match more than one order, so Asha will appear twice in joined results.

INNER JOIN: return matching rows

An INNER JOIN returns a row only when the join condition matches in both tables. Ben is absent because there is no order with customer_id = 2.

SELECT
    c.customer_name,
    o.order_id,
    o.amount
FROM customers AS c
INNER JOIN orders AS o
    ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

LEFT JOIN: keep every row from the left table

A LEFT JOIN returns every customer. Matching order columns are filled when an order exists; otherwise, the columns from orders are NULL. Here Ben remains in the result with no order details.

SELECT
    c.customer_name,
    o.order_id,
    o.amount
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

Filter carefully after a LEFT JOIN

A condition on the right-hand table in the WHERE clause removes rows where that value is NULL. This query therefore excludes Ben and behaves like an inner match for this condition:

SELECT c.customer_name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
WHERE o.amount >= 100;

If you want every customer but only want matching orders of at least 100, put that condition in the ON clause instead:

SELECT c.customer_name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
    AND o.amount >= 100
ORDER BY c.customer_id;

Count matches without losing customers

To count orders per customer and keep customers with no orders, use a LEFT JOIN and count a non-nullable column from the right table, such as its primary key. COUNT(o.order_id) returns zero for Ben; COUNT(*) would count Ben's preserved joined row as one.

SELECT
    c.customer_name,
    COUNT(o.order_id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY c.customer_id;

Choose the join that matches your question

• Use INNER JOIN when you only want records that have a match on both sides.
• Use LEFT JOIN when every row from the left table should remain, including unmatched rows.
• Check whether a join key can match multiple rows; one-to-many matches intentionally repeat the left-side values.
• When filtering a LEFT JOIN, decide whether the condition belongs in ON (which matches fewer right-side rows) or WHERE (which can remove left-side rows).

Want to practice formatting these queries? Try the SQL formatter. It changes layout, not query meaning, and does not execute or validate queries against your database.