Error Message is not Displaying in this Query? why?

Hello all

please see this query

select * from Table1 where Table1ID in
(select Table1ID from Table3)

Step1 )select Table1ID from Table3

output->Giving error as Table1ID is not valid column in Table3 .

Step2)select * from Table1 where Table1ID in
(select Table1ID from Table3)

Output-> Giving all records of Table1 as i am expecting error from this query .

Please check with your demo database and reply.

Thanking you.

Ramana.

There are no errors. By TSQL syntaxis, all Table1 columns are visible in subquery.

If you'd alias all your tables, then you will be never confused by such behavior. Look at:

select

*

from Table1 t1

where t1.Table1ID in (

select

t3.Table1ID

from Table3 t3

)

|||

Thanks for your reply .i did that one and i got error .but what was the problem in first query it has to give error .so please tell me what is problem with that one .

Thanks

|||As I said, becuse of all Table1 columns are visible within subquery. As you didn't specify alias for "Table1ID" subquery column, the column have been sought in both Table1 and Table3.|||

Thanks ,

But i want to know that is it necessary to add alias whenever i am using 'in ' in the query .

|||

Sorry, but SQL Server doesn't see an error in your query. If you had the same column in both tables and didn't properly alias them then you'd get an error, because it would need to know which table you were referencing, but since each column referenced in the select only exists in one place, the query parser is happy.

It's really a good idea to ALWAYS alias your columns, and then there's never a doubt regarding the source of the data.

|||

The answer here is that subqueries are technically correlated, whether you add a where clause to join them or not. So in this query:

say that

select table1Id from table1

returns:

table1Id
1
2

select *
from Table1
where Table1ID in (select Table1ID from Table3)

For the row where table1Id = 1, The results of (select Table1ID from Table3)

1 table3.column1 table3.column2
1 table3.column1 table3.column2

For the row where table1Id = 2, The results of (select Table1ID from Table3)

2 table3.column1 table3.column2
2 table3.column1 table3.column2

This allows you to add a where clause of WHERE table3.table1Id = table1.table1Id.

So the advice given on using aliases with subqueries is definitely correct. Or, just use

select *
from Table1
where exists (select *
from table3
where table3.table1Id = table1.table1Id)

where you would be less likely to make that mistake (which I have made a few times myself and asked the same question too :)