CODEWITHSUNDEEP
sqlsql-server-2008
user1668795
user1668795

Reputation: 57

issue using IN clause with sub query

I have the following query 

SELECT COUNT(*) 
FROM Table1 
WHERE Column1 IN
(SELECT  Column1 FROM Table2)

Actually there is no column named Column1 in the Table2. So if we execute only the sub query 

 SELECT  Column1 FROM Table2

it will throw the error - Invalid column name 'Column1'.

But if I Execute the full query , I am not getting any error. It is returing the total row count in Table1.

So I would like to know the reason why this query it not giving any error in this case And the way the IN clause works in this scenario.

Upvotes: 0

Views: 112

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

Within a subquery, you're allowed to access columns from the outer query. And that's what's happening here:

SELECT COUNT(*) 
FROM Table1 
WHERE Column1 IN
(SELECT  Column1 FROM Table2)

Could also be written as:

SELECT COUNT(*) 
FROM Table1 
WHERE Column1 IN
(SELECT Table1.Column1 FROM Table2)

Provided that there's at least one row in Table2, the subquery will always return the Column1 value from Table1, and the IN() will be successful.

This is one reason I usually recommend using aliases:

SELECT COUNT(*) 
FROM Table1 t1
WHERE t1.Column1 IN
(SELECT t2.Column1 FROM Table2 t2)

will produce an error if there's no Column1 in Table2 - or, if you do reference t1 inside the subquery, it's more obvious that it's deliberate.

Upvotes: 2

Darren
Darren

Reputation: 70718

The count returned are the results from Table1 not Table2. If you look at the execution plan you can see that it uses a LEFT SEMI JOIN for the query, therefore if it can't join on the columns I assume the following query will be executed:

SELECT COUNT(*) 
FROM Table1

Which is why the query is still valid and returns the same output.

Upvotes: 0

Related Questions