[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 169
  • Last Modified:

Help me understand these SQL Joins

I'm a bit confused with the hierarchy. I'm going to need to make some changes, so I'd first like to understand what's happening and why.

 
FROM         
                      dbo.Table1 RIGHT OUTER JOIN
                      dbo.Table2 LEFT OUTER JOIN
                      dbo.Table3 INNER JOIN
                      dbo.Table4 ON dbo.Table3.ITEMID = dbo.Table4.ITEMID INNER JOIN
                      dbo.Table5 ON dbo.Table4.SEQNO = dbo.Table5.SEQNO AND dbo.Table4.CUSNO = dbo.Table5.CUSNO ON 
                      dbo.Table2.REPNO = dbo.Table5.REPNO ON dbo.Table1.ITEMID = dbo.Table4.ITEMID AND 
                      dbo.Table1.CUSNO = dbo.Table4.CUSNO AND dbo.Table1.SEQNO = dbo.Table4.SEQNO

Open in new window

0
coldchillin
Asked:
coldchillin
  • 3
  • 2
1 Solution
 
Nico BontenbalCommented:
You can paste your query in Management Studio. Then you select the text of the query, right click and choose "Design query in editor...". You'll get a graphical presentation of your query that might be helpfull.
0
 
coldchillinAuthor Commented:
Somewhat so. It makes it easier to see what I already know, which is to say I'm not quite sure I know exactly what's going on and what changes where have what impact.
0
 
Daniel WilsonCommented:
Inner Join -- return only rows with a match on both sides
Left Join -- return all rows on the left, matched with either matches or NULL on the right
Right -- like Left, only ... the opposite.

Now, the LEFT and RIGHT in the same query makes that quite confusing.

I would restructure as:
Table2 LEFT
Table1 LEFT
Table3 INNER
Table4 ...
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
coldchillinAuthor Commented:
Thank you DanielWilson,

That's exactly my point. I know what a left, right, and equi join do...it's just that the way it's written feels like it's all slammed together and it may not even be working as intended.

I'm used to:

FROM t1, t2
WHERE t1.id = t2.id

and
FROM t1
JOIN t2 ON t1.id = t2.id

and
FROM t1
JOIN t2 ON t1.id = t2.id AND t2.id > 1000
left outer join t3 ON t2.CustNo = t3.CustNo AND t3.CustNo > 2000 AND t3.CustNo < 4000
0
 
Daniel WilsonCommented:
I always have found queries that mix LEFT and RIGHT joins confusing.  I've encouraged those working under me to use either all LEFT or all RIGHT joins in a query.  Personally, I use all LEFT joins.  Somehow, those just make more sense to me.

When you say t1 JOIN t2 without specifying the type, that's an INNER join.
0
 
coldchillinAuthor Commented:
Thanks DW. I would do the same - start with the core table and left join all of the other data.

I've inherited this query and now I'm trying to make sense of it. I'm trying to find out what the developer was intending to do so I can first determine if it's correct, before I mod it.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now