February 18, 2010 at 10:39 am
Hi,
I have a dought about performance in t-sql.
What is faster:
Make a "inner join on x=Y" or make "select ..... where x not in (select y)"
Is it faster to make a select where value not in table or make a inner join and see the result?
Thank you
February 18, 2010 at 10:48 am
"inner join on x=Y" should be faster.
February 18, 2010 at 10:51 am
Even if the inner join as to return millions of rows?
February 18, 2010 at 10:56 am
river1 (2/18/2010)
Make a "inner join on x=Y" or make "select ..... where x not in (select y)"
Unless I'm missing something, those will not return the same result.
Table1 INNER JOIN Table2 ON X = y
and
WHERE Table1.X IN (SELECT Y from Table2)
are equivalent, but INNER JOIN and NOT IN do not do the same thing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 18, 2010 at 11:08 am
GilaMonster (2/18/2010)
river1 (2/18/2010)
Make a "inner join on x=Y" or make "select ..... where x not in (select y)"Unless I'm missing something, those will not return the same result.
I was thinking the same thing. The 'inner join' and 'not in' logic are antonyms in this case.
Karl Lambert
SQL Server Database Administration
Business Intelligence Development
February 19, 2010 at 2:41 am
Suppose this:
I want to select all the names and addresses of people who have their name in table1 and table2. i can achieve this, for instance, with this two select statements:
1) select a.name, a.address from teste1 as a inner join test2 as b on a.name=b.name
2) select name,address from table1 where name in (select name from table2)
Wich of this two querys is faster? and why?
Thank you
February 19, 2010 at 2:45 am
Obviously first one
because in first query both the table will be scanned only for condition a.name=b.name
In second query whole table2 will be scanned then it will be compared with table1.
Also it depends on the size of the tables too
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
February 19, 2010 at 2:53 am
sanketahir1985 (2/19/2010)
Obviously first onebecause in first query both the table will be scanned only for condition a.name=b.name
In second query whole table2 will be scanned then it will be compared with table1.
Are you sure about that?
The query optimiser is not stupid. It is capable of recognising that the IN is a condition, just like the inner join, and optimising appropriately. In the absence of other conditions, it's likely that both tables will be scanned regardless (unless one of them is very, very small)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 19, 2010 at 2:54 am
Take a look at this blog post. http://sqlinthewild.co.za/index.php/2010/01/12/in-vs-inner-join/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 19, 2010 at 5:02 am
GilaMonster (2/19/2010)
sanketahir1985 (2/19/2010)
Obviously first onebecause in first query both the table will be scanned only for condition a.name=b.name
In second query whole table2 will be scanned then it will be compared with table1.
Are you sure about that?
The query optimiser is not stupid. It is capable of recognising that the IN is a condition, just like the inner join, and optimising appropriately. In the absence of other conditions, it's likely that both tables will be scanned regardless (unless one of them is very, very small)
thats why i said it depends on the table size too
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply