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

Question

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

I wonder if this is normal behaviour?

Answer 1

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

Answer 2

It's a strong argument for keeping column names consistent between tables. As @Martin says, the SQL syntax allows column names to be resolved from the outer query, when there's no match in the inner query. This is a boon when writing correlated subqueries, but can trip you up sometimes (as here)