Customer Orders: LEFT vs INNER JOIN

Context

Joins are one of the most common SQL interview topics because they directly affect which rows appear in a result set. A wrong join choice can silently exclude important records or include unexpected NULL values.

Core Question

Explain the difference between INNER JOIN and LEFT JOIN in SQL. In your answer, cover:

1. What rows each join returns
2. How unmatched rows are handled
3. When you would choose one over the other
4. A simple example of each using tables such as customers and orders
5. A common mistake people make when filtering after a LEFT JOIN

Scope Guidance

The interviewer is looking for a practical explanation, not just definitions. You should be able to describe the result set behavior clearly, explain how NULLs appear in a LEFT JOIN, and connect each join type to realistic business questions such as "customers with orders" versus "all customers, including those with no orders."

INNER JOIN

INNER JOIN returns only rows where the join condition matches in both tables. If a row from either side has no match, it is excluded from 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

LEFT JOIN returns all rows from the left table and matching rows from the right table. If there is no match on the right side, the right-table columns 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 and NULLs

The main behavioral difference is how unmatched rows are treated. INNER JOIN drops them, while LEFT JOIN preserves left-table rows and fills right-table columns with NULL.

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

Choosing the Correct Join

Use INNER JOIN when you only care about records with valid relationships in both tables. Use LEFT JOIN when the absence of related data is meaningful and should still appear in the output.

Filtering After a LEFT JOIN

A common mistake is putting a filter on the right table in the WHERE clause after a LEFT JOIN, which can effectively turn it into an INNER JOIN. If you want to preserve unmatched left rows, place the filter in the ON clause when appropriate.

SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
 AND o.order_status = 'completed';