Why does a LEFT JOIN behave like an INNER JOIN?

Definition:

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.

Key points:

• 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.

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.

Interview point:

• Explain the difference between filtering matching partners and filtering final result rows.