Why does a LEFT JOIN behave like an INNER JOIN?

Understand the concept

A LEFT JOIN preserves left-side rows by filling unmatched right-side columns with NULL. A later WHERE condition can remove those rows if it requires a right-side value to match. This makes the final result look like an inner join even though the join itself was outer.

Important ideas

• A WHERE condition on the right table can reject the NULL unmatched rows.

• Put the condition in ON when it should limit matches but preserve left rows.

• Example: keep every employee, but join only their approved leave records.

• Move a right-side filter into ON only when the requirement is to limit matches while preserving all left rows.

Work through the example

SELECT e.id, l.id AS approved_leave_id
FROM employees e
LEFT JOIN leaves l
  ON l.employee_id = e.id AND l.status = 'approved';

• Employees with no approved leave still appear with a NULL leave ID. Putting l.status = 'approved' in WHERE would remove those unmatched rows.