Using same tables alternatively in IN clause returns different results - I was expecting same!!

Question

I have read two excel sheets in two temp tables, #temp and #temp1 Replacing the tables in IN Clause returns different resutls. I was expecting the same. Can anyone explain?

select * from #temp where name in (select comp_name from #temp1)

Returns 473 records.

select * from #temp1 where comp_name in (select name from #temp)

Returns 1421 records

Answer 1

In the simplest case, imagine #temp has a total of 473 rows and #temp1 has a total 1421 rows, and ALL the name/comp_name in both tables are the all the same value.

Answer 2

Depends on your data surely. If you have data such as below, then you'll have a similar issue.

#temp
Name  CompName
A     X
A     Y
A     Z

#temp1
Name  CompName
A     X
B     X
C     A

In this case:

select * from #temp where name in (select comp_name from #temp1)

returns 3 rows

select * from #temp1 where comp_name in (select name from #temp)

returns 1 row

Answer 3

If you have duplicate comp_name values in #temp1 but a single name in #temp:

• It will only match once in the first query so return one row
• The 2nd query will give one row per duplicate

What does DISTINCT do?

Note: the queries can only always return the same data if both tables have the same number of rows and same number if duplicate per values. It may return the same number by coincidence if the data is different.

Answer 4

Possibility of multiple records in temp1 with the same comp_name? All of these will match the name values from temp2