Context

Joins are one of the most common SQL topics in interviews because they directly affect which rows appear in a result set. A small change in join type can silently remove important records.

Core question

Explain the difference between INNER JOIN and LEFT JOIN using a real-world example such as customers and orders.

In your answer, cover:

1. What each join returns
2. How unmatched rows are handled
3. A business case where INNER JOIN is the correct choice
4. A business case where LEFT JOIN is the correct choice
5. What the result would look like when some customers have no orders

Scope guidance

The interviewer expects a practical explanation, not just definitions. Use simple PostgreSQL examples, describe the output in words, and mention common mistakes such as accidentally filtering out unmatched rows after a LEFT JOIN.

INNER JOIN returns only matching rows

An INNER JOIN keeps rows only when the join condition matches in both tables. If a customer has no related order, that customer does not appear in the result.

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

LEFT JOIN preserves all rows from the left table

A LEFT JOIN returns every row from the left table, even when there is no match in the right table. For non-matching rows, columns from the right table are returned as NULL.

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

Unmatched rows are often the reason to use LEFT JOIN

Use LEFT JOIN when missing related data is meaningful, such as customers who have never placed an order. This makes it possible to identify gaps, inactivity, or missing relationships.

SELECT c.customer_id, c.customer_name
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Filtering after a LEFT JOIN can change the result

A common mistake is adding a WHERE filter on a right-table column after a LEFT JOIN. That can remove NULL rows and effectively turn the query into an INNER JOIN.

SELECT c.customer_id, c.customer_name, o.order_date
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
WHERE o.order_date >= DATE '2024-01-01';

Join choice depends on the business question

If the question is about completed relationships, INNER JOIN is usually appropriate. If the question is about coverage, missing activity, or full population reporting, LEFT JOIN is usually better.