where versus inner join - 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

Transact-SQL (2005)

where versus inner join

Author

Topic

marginerazvan
Starting Member

14 Posts

Posted - 2009-01-30 : 03:45:42

I have a table with 3000 records. (ID is the primary column)
I want to query something in this table. Usually my selection is 10 positions.

I have two scenarios
1. SELECT ... FROM Table WHERE ID IN (selection_list)
OR
2.
- save selections into a temporary table. This table can be used by many users, so it has a USER_ID column.
INSERT INTO Temp_Table (ID, USER_ID) SELECT selection_list .....
- query table
SELECT ... FROM Table INNER JOIN Temp_table ON Table.ID = Temp_table.ID WHERE Temp_table.USER_ID = ...

Question.
Which solution is quicker?
Using WHERE clause
OR
Using INNER JOIN clause, but this solution requires that first I have to save my selection into a temporary table

Thank your for your answer.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-30 : 04:07:10

duplicate post
see in
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=118899

Kokkula
Starting Member

41 Posts

Posted - 2009-01-30 : 04:13:01

Where clause will be faster when compared to Joins. It may be better in performance if you use a proper join criteria. But when taken into consideration Where clase will be faster in execution.

--
Thanks,
Pavan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-30 : 04:17:45

What do you base that on?

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

marginerazvan
Starting Member

14 Posts

Posted - 2009-01-30 : 04:35:44

This topic is not a duplicate of the other topic. The problem relates to SQL Server 2000 and SQL Server 2005. I suspect that what is quicker for one SQL, is slower for the other. That's why I wrote the same question in boths SQL 2000 and 2005

Kokkula
Starting Member

41 Posts

Posted - 2009-01-30 : 04:45:32

Refer to the link mentioned below where each and every clause has its own importance in improving the performance.

http://www.sql-server-performance.com/tips/tsql_main.aspx

--
Thanks,
Pavan

Thanks,
Pavan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-30 : 05:05:15

quote:
Originally posted by marginerazvan

That's why I wrote the same question in boths SQL 2000 and 2005

Why do you think there will be a difference in SQL Server 2000 and SQL Server 2005 or even SQL Server 2008?

Go to the other post and use the testing code I posted.
Run it for both SQL Server 2000 and SQL Server 2005. It will return same execution plan.

Here are the execution plans for comparison:

SQL Server 2000

  |--Nested Loops(Inner Join, OUTER REFERENCES:([b].[ID]))
       |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#Subset].[PK__#Subset__63484916] AS [b]), SEEK:([b].[UserID]=77) ORDERED FORWARD)
       |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#Superset].[PK__#Superset__5F77B832] AS [a]), SEEK:([a].[ID]=[b].[ID]) ORDERED FORWARD)

  |--Merge Join(Right Semi Join, MERGE:([b].[ID])=([a].[ID]), RESIDUAL:([b].[ID]=[a].[ID] AND [b].[ID]=[a].[ID]))
       |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#Subset].[PK__#Subset__63484916] AS [b]), SEEK:([b].[UserID]=77) ORDERED FORWARD)
       |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#Superset].[PK__#Superset__5F77B832] AS [a]), ORDERED FORWARD)


SQL Server 2005

  |--Nested Loops(Inner Join, OUTER REFERENCES:([b].[ID]))
       |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#Subset] AS [b]), SEEK:([b].[UserID]=(77)) ORDERED FORWARD)
       |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#Superset] AS [a]), SEEK:([a].[ID]=[tempdb].[dbo].[#Subset].[ID] as [b].[ID]) ORDERED FORWARD)

  |--Merge Join(Right Semi Join, MERGE:([b].[ID])=([a].[ID]), RESIDUAL:([tempdb].[dbo].[#Subset].[ID] as [b].[ID]=[tempdb].[dbo].[#Superset].[ID] as [a].[ID] AND [tempdb].[dbo].[#Subset].[ID] as [b].[ID]=[tempdb].[dbo].[#Superset].[ID] as [a].[ID]))
       |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#Subset] AS [b]), SEEK:([b].[UserID]=(77)) ORDERED FORWARD)
       |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#Superset] AS [a]), ORDERED FORWARD)


SQL Server 2008

  |--Nested Loops(Inner Join, OUTER REFERENCES:([b].[ID]))
       |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#Subset] AS [b]), SEEK:([b].[UserID]=(55)) ORDERED FORWARD)
       |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#Superset] AS [a]), SEEK:([a].[ID]=[tempdb].[dbo].[#Subset].[ID] as [b].[ID]) ORDERED FORWARD)

  |--Merge Join(Right Semi Join, MERGE:([b].[ID])=([a].[ID]), RESIDUAL:([tempdb].[dbo].[#Subset].[ID] as [b].[ID]=[tempdb].[dbo].[#Superset].[ID] as [a].[ID] AND [tempdb].[dbo].[#Subset].[ID] as [b].[ID]=[tempdb].[dbo].[#Superset].[ID] as [a].[ID]))
       |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#Subset] AS [b]), SEEK:([b].[UserID]=(55)) ORDERED FORWARD)
       |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#Superset] AS [a]), ORDERED FORWARD)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources