• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 173
  • 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.

                      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

  • 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.
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.
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 ...
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

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

JOIN t2 ON t1.id = t2.id

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
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.
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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