IN 子句中不存在列，但 SQL 运行 [英] Column does not exist in the IN clause, but SQL runs

问题描述

我有一个使用 IN 子句的查询.这是一个简化版本:

I have a query that uses the IN clause. Here's a simplified version:

SELECT *
  FROM table A
  JOIN table B
    ON A.ID = B.ID
 WHERE B.AnotherColumn IN (SELECT Column FROM tableC WHERE ID = 1)

tableC 没有 Column 列，但查询执行得很好，没有错误消息.谁能解释一下原因?

tableC doesn't have a Column column, but the query executes just fine with no error message. Can anyone explain why?

推荐答案

如果 outer 查询中的表具有该名称的列，这将起作用.这是因为外部查询中的列名可供子查询使用，您可能有意在子查询 SELECT 列表中选择外部查询列.

This will work if a table in the outer query has a column of that name. This is because column names from the outer query are available to the subquery, and you could be deliberately meaning to select an outer query column in your subquery SELECT list.

例如:

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 所观察到的，避免这种不太明显的陷阱"的最安全方法是养成在子查询中限定列名的习惯:

As Damien observes, the safest way to protect yourself from this none-too-obvious "gotcha" is to get into the habit of qualifying your column names in the subquery:

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

这篇关于IN 子句中不存在列，但 SQL 运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！