sql server 2008 management studio 没有检查我的查询的语法
一如既往，我的惊讶会有一个合理的解释，但在那之前......
As always, there will be a reasonable explanation for my surprise, but till then....
我有这个问题
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)
但奇怪的是:HotelSupplier中没有hs_id字段，它叫hs_key！
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
单独(用鼠标选择查询的那部分并按 F5)，我得到一个错误，但是当我在 in 子句中使用它时，它没有！
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?
推荐答案
它从外部查询中获取 hs_id 的值.
It is taking the value of hs_id from the outer query.
在其 select 列表中没有从选定表中投影任何列的查询是完全有效的.
It is perfectly valid to have a query that doesn't project any columns from the selected table in its select list.
例如
select 10 from HotelSupplier where id = 142
将返回一个结果集，其中包含与 where 子句匹配的行数以及所有行的值 10.
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.
只要 HotelSupplier 至少有一行 id = 142(并且所以子查询至少返回一行)
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)
这当然等价于
delete from Photo where Photo.hs_id = Photo.hs_id
顺便说一句，这是我个人看到的 Microsoft Connect 上错误报告的最常见的错误".Erland Sommarskog 将其包含在他的 wishlist 中，用于 SET STRICT_CHECKS ON
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
相关文章