Join versus IN Operator - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Join versus IN Operator

Author

Topic

Dev@nlkss

134 Posts

Posted - 2008-12-17 : 04:48:56

Hi All

Can anybody tell me Join versus in operator which one is best as compared performance wise

Satya

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-17 : 05:00:52

It depends.
What problem are you facing?

E 12°55'05.63"
N 56°04'39.26"

Dev@nlkss

134 Posts

Posted - 2008-12-17 : 05:35:27

In one of my queries i used multiple subqueries with IN operator where i can use join too.
so which one is better for performance wise if it should perform on huge no. of records

Satya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 05:38:06

did you test using join as well as in approach? what did you observe?

Dev@nlkss

134 Posts

Posted - 2008-12-17 : 06:09:06

Yes i tested both the queries using actual execution plan both consumes 50% and remaining ststistics also same but order is changed
but i have tens of records what happens if they grows

Satya

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-17 : 06:37:02

JOINS can lead to unpredicted results when there are duplicates key column values.
Post you query!

E 12°55'05.63"
N 56°04'39.26"

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-18 : 07:49:56

I tested 3 methods (IN,JOIN,EXISTS) on 2 tables with 1 million rows each where id field in both is identity primary key.

SET STATISTICS TIME on

Select count(*) from Table1 where id in(select id from table2)

Select count(*) from Table1 where exists(select id from table2)

Select count(*) from Table1 join
table2
on table1.id=table2.id

SET STATISTICS TIME off

Performance:
(1 row(s) affected)

SQL Server Execution Times:
CPU time = 438 ms, elapsed time = 461 ms.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 2813 ms, elapsed time = 1425 ms.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 453 ms, elapsed time = 452 ms.

I ran this a few times - the IN and JOIN were about the same and EXISTS always slower.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-18 : 08:49:46

Is the primary key clustered?

E 12°55'05.63"
N 56°04'39.26"

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-18 : 09:09:56

yes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources