Michel Michel - 6 months ago 18
SQL Question

sql server 2008 management studio not checking the syntax of my query

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)

which executes just fine (later i found out that the entire photo table was empty)

but the strange thing: there is no field
in HotelSupplier, it is called

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
clause, it doesn't!

I wonder if this is normal behaviour?


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