我有一个使用IN子句的查询.这是一个简化版本：
SELECT *
FROM table A
JOIN table B
ON A.ID = B.ID
WHERE B.AnotherColumn IN (SELECT Column FROM tableC WHERE ID = 1)
解决方法
如果外部查询中的表具有该名称的列,则此方法将起作用.这是因为外部查询中的列名可用于子查询,您可能故意在子查询SELECT列表中选择外部查询列.
例如：
CREATE TABLE #test_main (colA integer) CREATE TABLE #test_sub (colB integer) -- Works,because colA is available to the sub-query from the outer query. However,-- it's probably not what you intended to do: SELECT * FROM #test_main WHERE colA IN (SELECT colA FROM #test_sub) -- Doesn't work,because colC is nowhere in either query SELECT * FROM #test_main WHERE colA IN (SELECT colC FROM #test_sub)
正如Damien所观察到的,保护自己免受这种不太明显的“陷阱”的最安全的方法是养成在子查询中限定列名的习惯：
-- Doesn't work,because colA is not in table #test_sub,so at least you get -- notified that what you were trying to do doesn't make sense. SELECT * FROM #test_main WHERE colA IN (SELECT #test_sub.colA FROM #test_sub)
