"select where in (select)" behaves strangely

I have noticed that selects that have a "where in (select...)" behave in a way I would not expect them to.

For example:

if object_id('x') is not null drop table x

if object_id('y') is not null drop table y

create table x(x1 int); create table y(y1 int)

insert x values (1); insert x values (2)

insert y values (1); insert y values (2)

select * from x where x1 in (select x1 from y)

/*

x1

--

1

2

*/

I would expect a syntax error here, since table y does not contain a column called x1. When it does contain a column called x1, the query behaves as I would expect it to:

if object_id('x') is not null drop table x

if object_id('y') is not null drop table y

create table x(x1 int); create table y(y1 int, x1 int)

insert x values (1); insert x values (2)

insert y values (1, 3); insert y values (2, 3)

select * from x where x1 in (select x1 from y)

--(0 row(s) affected)

Is this behavior by design?

Thanks,

Ron Rice

Yes, this is basically by design. There was a similar discussion here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1260288&SiteID=1

(search was corelated rowland)