As always, there will be a reasonable explanation for my surprise, but till then....
I have this query
delete from Photo where hs_id in (select hs_id from HotelSupplier where id = 142)
select hs_id from HotelSupplier where id = 142
It is taking the value of
hs_id from the outer query.
Unqualified column references are resolved from the closest scope outwards so this just gets treated as a correlated sub query.
The result of this query will be to delete all rows from
hs_id is not null as long as HotelSupplier has at least one row where id = 142 (and so the subquery returns at least one row)
It might be a bit clearer if you consider what the effect of this is
delete from Photo where Photo.hs_id in (select Photo.hs_id)
This is of course equivalent to
delete from Photo where Photo.hs_id = Photo.hs_id
By the way this is far and away the most common "bug" that I personally have seen erroneously reported on Microsoft Connect. Erland Sommarskog includes it in his wishlist for
SET STRICT_CHECKS ON