Order of Tables in JOIN ??

Question

Order of Tables in JOIN ??

homebrew01

SSC Guru

Points: 55548
More actions
January 23, 2014 at 2:32 pm

#287555

I thought it didn't matter what order the JOIN statements are in a query, but if I join TABLE_B before Table_C, I get an error. If I join Table_B last, I get results. What am I missing ?
SELECTdistinct
Col1, col2, col3, col4
from InputTable EAP
join DB..Table_A M on MT_GoToMeetingID = WebinarID
join DB..Table_B Mbr on MEP.Mem_ID = Mbr.MEM_ID
join DB..Table_C MEP on email = MEP.memepf_email1
--join DB..Table_B Mbr on MEP.Mem_ID = Mbr.MEM_ID 'B' commented out
'The multi-part identifier "MEP.Mem_ID" could not be bound.'
SELECTdistinct
Col1, col2, col3, col4
from InputTable EAP
join DB..Table_A M on MT_GoToMeetingID = WebinarID
--join DB..Table_B Mbr on MEP.Mem_ID = Mbr.MEM_ID 'B' commented out
join DB..Table_C MEP on email = MEP.memepf_email1
join DB..Table_B Mbr on MEP.Mem_ID = Mbr.MEM_ID
--Returns Normal Results

Viewing 5 posts - 1 through 4 (of 4 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

That's because when SQL Server is "reading" the code, it won't find a reference to Table_B alias until it gets to Table_B.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

homebrew01 (1/23/2014)
I thought it didn't matter what order the JOIN statements are in a query, but if I join TABLE_B before Table_C, I get an error. If I join Table_B last, I get results. What am I missing ?
SELECTdistinct
Col1, col2, col3, col4
from InputTable EAP
join DB..Table_A M on MT_GoToMeetingID = WebinarID
join DB..Table_B Mbr on MEP.Mem_ID = Mbr.MEM_ID
join DB..Table_C MEP on email = MEP.memepf_email1
--join DB..Table_B Mbr on MEP.Mem_ID = Mbr.MEM_ID 'B' commented out
'The multi-part identifier "MEP.Mem_ID" could not be bound.'
SELECTdistinct
Col1, col2, col3, col4
from InputTable EAP
join DB..Table_A M on MT_GoToMeetingID = WebinarID
--join DB..Table_B Mbr on MEP.Mem_ID = Mbr.MEM_ID 'B' commented out
join DB..Table_C MEP on email = MEP.memepf_email1
join DB..Table_B Mbr on MEP.Mem_ID = Mbr.MEM_ID
--Returns Normal Results

In your first query (the one with the error) you are attempting to join MEP.Mem_ID to Mbr.MEM_ID but MEP has not been defined yet.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

Luis Cazares (1/23/2014)
That's because when SQL Server is "reading" the code, it won't find a reference to Table_B alias until it gets to Table_B.

Beat me by 3 seconds!

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

Code validation takes place before any of the other optimizer steps, so it has to read correctly as the others have stated. And the order can matter if you have very complex set of joins because the optimizer may not have much time to try to rearrange join order, so it usually makes sense to try to help it out and put the joins in a logical order. It usually rearranges things at will.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning