sql server programming: Hi All,

The following case is true both in SQL SERVER2000 and SQLSERVER2005.

Have a table TA. Columns are say COLA1,COLA2,COLA3......
TB is a different table.Columns are say COLB1,COLB2.....

Write a query:
select COLA1 from TB
-- This generates an error " Invalid Column Name 'COLA1'.

But surprisingly the below query doesn't throw error.

select * from TA where <whatever column of TA> in (select COLA1 from
TB)

-- Nothing is returned as result set.

I have tried it with various tables in sql server and could not find
any explanation.

If you have any explanation please share with us.

Thanks
Mukut

[quoted text, click to view]


In a subquery you can reference columns in the outer part of the query.
Aliases are optional, so your query is equivalent to:

SELECT *
FROM TA WHERE <whatever column of TA>
IN (SELECT TA.cola1 FROM TB);

TA.cola1 is a valid column name. This is standard SQL and without this
feature correlated subqueries would not work. If you are always careful
to specify the alias then you'll get an error if you reference the
wrong table:

SELECT *
FROM TA WHERE <whatever column of TA>
IN (SELECT TB.cola1 FROM TB);

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--