SQL JOIN - Questions

Question

Post reply

SQL JOIN - Questions

karthik M

One Orange Chip

Points: 29139
More actions
May 14, 2008 at 5:27 am

#76988

All,
I have some doubts on Join methods. it may be basic questions.I will explain it with a simple example.
Table A: 1000 Rows
Table B: 250 Rows
Key question is :
which table should come first when we are going to use JOIN ?
i.e A table which contains less number of rows than any other table used in the same join or A table which contains large number of rows than other table used in the same join.
My opinion is Large table should come first.But i could be wrong.
Here, I am going to use all kind of join with different combinations.
which one is fast ? Why ?
1) Table A Left Join Table B
2) Table B Left Join Table A
3) Table A Right Join Table B
4) Table B Right Join Table A
5) Table A Inner Join Table B
6) Table B Inner Join Table A
Inputs and suggestions are welcome ! It would be appreciable if anyone explain it with good example.
karthik

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Michael Meierruth SSChampion Points: 10051 More actions · Answer 1

Examples 1 and 2 potentially produce two different results.

Same for 3 and 4.

Regarding 5 and 6, the result is always the same. So do you want to know if performance wise there is a difference?

karthik M One Orange Chip Points: 29139 More actions · Answer 2

karthik M

One Orange Chip

Points: 29139

May 14, 2008 at 6:09 am

#815085

Yes, I want to know.

karthik

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Please don't cross post. It just wastes people's time.

Already answered-

http://www.sqlservercentral.com/Forums/Topic500298-65-1.aspx

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

karthik M One Orange Chip Points: 29139 More actions · Answer 4

Sorry!

I have tried to delete it from there,even though i have pressed delete button,But i am unable to delete it.

karthik

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 5

[font="Verdana"]

Table A: 1000 Rows
Table B: 250 Rows
Key question is :
which table should come first when we are going to use JOIN ?

No matter which type of Join you are using in statement, table with minimum records should always be at Left side.

Mahesh[/font]

MH-09-AM-8694

Michael Meierruth SSChampion Points: 10051 More actions · Answer 6

Where does this 'golden rule' come from? What does it give you?

During the life of these two tables this could change.

What will happen then?

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 7

Mahesh, I disagree. From what I understand, the query optimizer will determine which table to select from first. I've experimented with changing my join order around, and the query plan is identical. I believe there are ways that one can force a particular order with query hints, but from what I read, they are not recommended.

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 8

[font="Verdana"]Might be I have posted some wrong information, but once our DBA had asked us to make a habit of it. (It was my mistake then, that I didn't gone further and asked him WHY?)

I will surely take a look on it.

Mahesh[/font]

MH-09-AM-8694