Help me understand these SQL Joins

Posted on 2011-05-12
Last Modified: 2012-06-21
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

Question by:coldchillin
    LVL 22

    Expert Comment

    by:Nico Bontenbal
    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.
    LVL 1

    Author Comment

    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.
    LVL 32

    Accepted Solution

    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 ...
    LVL 1

    Author Comment

    Thank you DanielWilson,

    That's exactly my point. I know what a left, right, and equi join'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 =

    FROM t1
    JOIN t2 ON =

    FROM t1
    JOIN t2 ON = AND > 1000
    left outer join t3 ON t2.CustNo = t3.CustNo AND t3.CustNo > 2000 AND t3.CustNo < 4000
    LVL 32

    Expert Comment

    by:Daniel Wilson
    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.
    LVL 1

    Author Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now