在 SQL 中使用危险的 IN 子句 [英] Using the dangerous IN clause in SQL

问题描述

为什么 SQL Server 会这样.我在 SQL 2005 上运行它.

Why does SQL server behave this way. I am running this on SQL 2005.

IN 子句不验证子查询中的列名，而是验证它针对外部查询中的表名.这是获取

The IN clause does not validate the column names in the sub query but validates it against the table name in the outer query. Here is an example of getting

Create table #table1(col1 int, col2 char(10), col3 char(15));

Create table #table2(col10 int, col11 char(10), col2 char(15));


insert into #table1(col1, col2, col3)
select 1, 'one', 'three'

insert into #table1(col1, col2, col3)
select 2, 'two', 'three'

insert into #table1(col1, col2, col3)
select 3, 'three', 'four'


insert into #table2(col10, col11, col2)
select 1, 'one', 'three'

insert into #table2(col10, col11, col2)
select 2, 'two', 'three'

insert into #table2(col10, col11, col2)
select 3, 'three', 'four'


select * from #table1
where col1 IN
(select col1 from #table2)

好像我只是选择select col1 from #table2"并运行它会吐出错误

Where as if I just select the "select col1 from #table2" and run it spits an error

Msg 207, Level 16, State 1, Line 1
Invalid column name 'col1'.

推荐答案

为什么?因为能够在子查询中引用来自外部查询的列经常有用.没有可用于关闭此行为的设置，但如果您养成使用别名的习惯，则应该避免大多数问题:

Why? Because it's frequently useful to be able to reference columns from the outer query in subqueries. There's no setting you can use to turn off this behaviour, but if you get into the habit of using aliases, you should avoid most problems with it:

select * from #table1 t1
where t1.col1 IN
(select t2.col1 from #table2 t2)

会产生错误.

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