The left join that became an inner join

You meet it when#

You wrote left join because you wanted every row from the left side, then added a filter and lost them anyway.

The version that looks right#

select o.order_id, t.tag
from orders o
left join order_tags t on t.order_id = o.order_id
where t.tag = 'gift';

Orders with no tags produce t.tag = NULL. NULL = 'gift' is unknown, not true, so the WHERE discards them. The left join is now an inner join and every untagged order has vanished.

The pattern#

Filter the right table in the ON clause, where it constrains the match rather than the result:

select o.order_id, t.tag
from orders o
left join order_tags t
       on t.order_id = o.order_id
      and t.tag = 'gift';

Every order survives. Orders with the tag get it; orders without get NULL.

The rule, stated plainly#

For an inner join it makes no difference which clause you use, and that is precisely why the habit does not form. It only bites once you switch a join to left and forget to move the predicate.