online tables vs join conditions

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)

online tables vs join conditions

Author

Topic

heze
Posting Yak Master

192 Posts

Posted - 2007-06-06 : 20:25:28

hi, does anybody know what is the best way to join two tables?
if using an online table..

select * from
(select q,w,e from oneTable) a
inner join twoTable b on a.q=b.q

or on the join condition

select * from oneTable
inner join twoTable b on a.q=b.q
------
of course, here Im talking about complex joins with many tricky conditions and I seem to find the 1st form (inline tables) more manageable

thank you

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-06-06 : 20:29:38

This is the practice supported by the industry

I would go with

select * from oneTable
inner join twoTable b on a.q=b.q

but you can always run stats to verify with your tables.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-07 : 10:27:04

When you select columns from more than one table, dont use *. Use column names with proper table alias

Madhivanan

Failing to plan is Planning to fail

heze
Posting Yak Master

192 Posts

Posted - 2007-06-07 : 10:37:21

thanks M, but what option do you suggest for different cases ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-09 : 07:17:52

quote:
Originally posted by heze

thanks M, but what option do you suggest for different cases ?

What did you mean by different cases?

Madhivanan

Failing to plan is Planning to fail

heze
Posting Yak Master

192 Posts

Posted - 2007-06-11 : 06:30:43

--case1 conditions in the inline table
select * from
(select q,w,e from oneTable where {complex conditions} ) a
inner join twoTable b on a.q=b.q

------------------
--case 2--conditions in the join section
select * from oneTable
inner join twoTable b on a.q=b.q and {complex conditions}

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-11 : 06:38:24

Check execution plans and IO statistics for both and decide for yourself.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

pootle_flump

1064 Posts

Posted - 2007-06-11 : 08:19:20

Why not case 3 - conditions in the where clause rather than the from clause? I don't know of any reason (with an inner join anyway) why one should filter in the join condition rather than where clause.

Agreed with Harsh too - sometimes the derived table works out more efficient when evaluated by the optimiser in complex queries so test your queries when writing them.

HTH

heze
Posting Yak Master

192 Posts

Posted - 2007-06-11 : 08:28:53

f, it does not make too much sense for me either but I am new with this databases and somebody else already produced many queries with the conditions on the join section, some are very complex and it seemed easier for me to understand if I switched those conditions to an inline table as in case 1 because instead of conditions it is a better structured complete query, I was not sure however which of 1,2 or 3 are the correct way to go, I believe as H suggested, would have to check the execution plans...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-11 : 08:45:21

quote:
Originally posted by heze

--case1 conditions in the inline table
select * from
(select q,w,e from oneTable where {complex conditions} ) a
inner join twoTable b on a.q=b.q

------------------
--case 2--conditions in the join section
select * from oneTable
inner join twoTable b on a.q=b.q and {complex conditions}

In both the cases you need to specifiy the column names than *
Execution plan will be same for both as long as you use inner join

If you use left outer join, it matters

http://www.sqlteam.com/item.asp?ItemID=11122

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources