Join Question 2

[DatabasePrincess]

Hello all,

I'm a bit rusty with database development and am hoping someone could help me figure something out.

Normally when I write select statements with joins, I don't include things such as "and codeDeleteFlag = 'N' " as part of the join clause. I usually put this in a where clause. Is it better to put it in the join or the where clause and why?

Thanks!

 

[markros]

If you're using LEFT JOIN and the condition applies to the right table or vs. versa for RIGHT JOIN, the only way to have the condition is to have it in the JOIN.

Otherwise (for INNER JOIN) it probably would not matter. The JOIN condition evaluates first and then the WHERE condition.

 

[DatabasePrincess]

Thanks for replying. I know what you're saying. Here is some code to show what I'm asking. Which of the two statements below is better?

select o1.name
	  ,Address.streetNumber
	  ,Address.streetName
	  ,Address.zip
	  ,Address.city
	  ,Address.state
	from vwNam o1  
	left join dbo.Contact
		on o1.name = dbo.Contact.businessName
		and dbo.Contact.codeDeleteFlag = 'N'
	left join dbo.ContactPerProject
		on dbo.Contact.contactID = dbo.ContactPerProject.contactID
		and dbo.ContactPerProject.codeDeleteFlag = 'N'
		and dbo.ContactPerProject.roleCode = 'Owner'
	left join dbo.Address
		on dbo.Contact.contactID = dbo.Address.contactID
		and dbo.Address.codeDeleteFlag = 'N'	
	where o1.seq = 1
	and replicate('0',10 - len(ltrim(rtrim(o1.par)))) + ltrim(rtrim(o1.par)) = @par

	select o1.name
	  ,Address.streetNumber
	  ,Address.streetName
	  ,Address.zip
	  ,Address.city
	  ,Address.state
	from vwNam o1  
	left join dbo.Contact
		on o1.name = dbo.Contact.businessName
	left join dbo.ContactPerProject
		on dbo.Contact.contactID = dbo.ContactPerProject.contactID
	left join dbo.Address
		on dbo.Contact.contactID = dbo.Address.contactID
	where o1.seq = 1
	and replicate('0',10 - len(ltrim(rtrim(o1.par)))) + ltrim(rtrim(o1.par)) = @par
		and dbo.Contact.codeDeleteFlag = 'N'
		and dbo.ContactPerProject.codeDeleteFlag = 'N'
		and dbo.ContactPerProject.roleCode = 'Owner'
		and dbo.Address.codeDeleteFlag = 'N'

Thanks!

 

[markros]

These two selects will return different results. The second select acts as INNER JOIN.

So, in this case the first select is definitely better.

 

[gmmastros]

In this thread: thread183-1504081

I explain the situation where you have a left join and you put a where clause condition on the right table (as Markros previously mentioned). I strongly encourage you to play around with the example code I show in that thread. Once you completely understand what's going on, then you will have mastered this topic. It really is important. If you have any questions, asks them here (in this thread).


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DatabasePrincess]

Aha! Thank you both for replying. George, that example shows it perfectly! It makes perfect sense now! Thank you so much!