sql 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 2008 Forums

Other SQL Server 2008 Topics

sql join

Author

Topic

shobinmathew
Starting Member

16 Posts

Posted - 2008-06-01 : 23:47:16

see the 4 examples which will give same result. Which will be the the fstest ?

eg1:

Select * from
table1
left join
table2
on table.id=table2.fid
where
table2.column3 >5

eg2:
Select * from
table1
inner join
table2
on table.id=table2.fid
where
table2.column3 >5

eg3:

Select * from
table1
inner join

(select * from table2 where table2.column3 >5) as table2
on table1.id=table2.fid

eg4:

Select * from
table1
inner join
table2
on table.id=table2.fid
and

table2.column3 >5

ALl the three returns same data. Of these eg3 AND 4 will be faster.

Among 3 qnd 4 which will be more faster

nr
SQLTeam MVY

12543 Posts

Posted - 2008-06-02 : 00:48:20

eg1 is confusing as it is actually an inner join but coded as an outer join.

As to which is faster it might depend on indexes and data volumes and resources so it's not a question that can be answered.
You will need to run tests on your system and also check the query plans to see if there is any difference.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

shobinmathew
Starting Member

16 Posts

Posted - 2008-06-03 : 00:25:59

i am asking this for query optimization.
In the same resourse set which will have better performance ?
Recors there will be large number and it will go on increasing...

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-03 : 16:20:34

Try it and see.We don't know what resources you have.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources