SQL Server misses that column doesn't exist

I have this query

delete
from Photo
where hs_id in (
    select hs_id
    from HotelSupplier
    where id = 142
)

Which executes just fine (later I found out that the entire photo table was empty).

But the strange thing: there is no column hs_id in HotelSupplier, it is called hs_key!

So when I execute the last part

select hs_id
from HotelSupplier
where id = 142

Separately (select that part of the query with the mouse and hit F5), I get an error, but when I use it in the in clause, it doesn't!

Is this normal behaviour?

Solution

It is taking the value of hs_id from the outer query.

It is perfectly valid to have a query that doesn't project any columns from the selected table in its select list.

For example

select 10 from HotelSupplier where id = 142

would return a result set with as many rows as matched the where clause and the value 10 for all rows.

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 Photo where 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