SQL SERVER – Challenge – Puzzle – Why does RIGHT JOIN Exists

I had interesting conversation with the attendees of the my SQL Server Performance Tuning course.

I was asked if LEFT JOIN can do the same task as RIGHT JOIN by reserving the order of the tables in join, why does RIGHT JOIN exists?

The definitions are as following:
Left Join – select all the records from the LEFT table and then pick up any matching records from the RIGHT table  

Right Join – select all the records from the RIGHT table and then pick up any matching records from the LEFT table

Most of us read from LEFT to RIGHT so we are using LEFT join. Do you have any explaination why RIGHT JOIN exists or can you come up with example, where RIGHT JOIN is absolutely required and the task can not be achieved with LEFT JOIN.

Other Puzzles:

SQL SERVER – Puzzle – Challenge – Error While Converting Money to Decimal

SQL SERVER – Challenge – Puzzle – Usage of FAST Hint

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

23 thoughts on “SQL SERVER – Challenge – Puzzle – Why does RIGHT JOIN Exists

  1. I think it has to do with query optimization. We tend to think Left-to-Right when reading or when making Joins on tables, but we also might be able to gain addition performance by rewriting a query in a new way, perhaps utilizing a Right Join to “flip” it around and test for additional performance.

    And even thinking about reading Left-to-Right, maybe Right Joins are used more often in cultures that read from Right-to-Left, such as many Middle Eastern cultures?

    Still, I don’t use Right Joins all that often – I can’t even remember the last time…

    Like

  2. Convenience and optimization. Just because we can write our query as a LEFT OUTER JOIN, doesn’t mean that you should. SQL Server provides a RIGHT OUTER JOIN showplan operator (http://msdn.microsoft.com/en-us/library/ms190390.aspx). There are times when it’s going to be most efficient to use a right outer join. Leaving that option in the language 1) gives you the same functionality in the language that you have in the optimizer and 2) supports the ANSI SQL specification. There’s always a chance, in a sufficiently complex plan on a sufficiently overloaded SQL Server, that SQL Server may time out query compilation. In theory, if you specify RIGHT OUTER JOIN instead of a LEFT OUTER JOIN, your SQL could provide SQL Server with the hints it needs to create a better plan. If you ever see this situation, though, you should probably blog about it :)

    No programming task requires a join, but you can also write all of your queries using syntax like SELECT * FROM a, b, c, d WHERE (a.id = b.a_id OR b.a_id IS NULL) and still have perfectly valid, well-formed, and ANSI compliant SQL.

    Like

  3. The only time I have used it is when optimizing a large query. One of the things I had to do was force the join order, and when you do that SQL Server stops re-writing your query. (It had been converting parts to use a RIGHT JOIN to optimize the hash joins.) So once I forced the join order performance got worse until I re-wrote it to use RIGHT JOINs. Since the join order determines which side is turned in to the hash table, and which side is run through to probe the hash table you want to make sure the smaller table is hashed.

    Like

    • –here is a query to support your comments:
      USE AdventureWorks

      SELECT PD.ProductID,PD.Name,PD.ProductNumber,PM.Name AS Product_Model,
      PCH.StandardCost FROM Production.Product PD
      LEFT OUTER JOIN Production.ProductModel PM
      ON PD.ProductModelID = PM.ProductModelID
      LEFT OUTER JOIN Production.ProductCostHistory PCH
      ON PD.ProductID = PCH.ProductID

      Like

      • Hi Jason ,

        Reason was good that it forces me to see how practically it goes . i tried my hand to see it practical but for me it is working in reverse order

        Table1

        c1 c2
        1 NULL
        2 2
        3 3
        4 4
        5 5
        6 6

        Table2

        c1 c2
        2 NULL
        3 2
        4 NULL
        5 NULL
        6 6
        7 NULL

        Table3

        c1 c2
        4 NULL
        5 2
        6 NULL
        7 2
        8 NULL
        9 9

        select table1.c1,table2.c1,table2.c2 from table1
        RIGHT OUTER JOIN table2
        on table1.c2 = table2.c2
        RIGHT OUTER JOIN table3
        on table2.c2=table3.c2

        select table1.c1,table2.c1,table2.c2 from table3
        LEFT OUTER JOIN table2
        on table3.c2 = table2.c2
        LEFT OUTER JOIN table1
        on table1.c2 = table2.c2

        check out this both queries it gives same result?? and i think at all i can complete my task without Right outer join ..

        If somewhere i m wrong correct me..

        Thanks and regards
        kamesh shah

        Like

  4. Let’s consider some Wharehouse example. I may have multiple wharehouses with different account numbers.
    Let’s call them (ACC)
    I’ve got 2 tables, Book and Supplies, Supplies table stores my product incomes into wharehouses and book is a parent
    table for supplies.
    in supplies table I’m storing the following data:

    Product 1 income count – 10 date: Somedate
    Product 2 income count – 5 date: TheSameDate
    Product 3 income count – 3 date: TheSameDate

    Product 1 income count – 5 date: Somedate+2 days
    Product 2 income count – 7 date: Somedate+2 days

    Product 1 income count – 6 date: Somedate+4 days
    Product 2 income count – 7 date: Somedate+6 days
    .
    .
    .

    first three records have the one parent record in book table where I store information about wharehouse, where income happened
    and some other additional important information.

    Ok now I have orders from my clients.
    Orders table and OrderDetails table (Parent Child)
    Orders Table stores Order date,Customer ID, plus some other information and OrderDetails table stores information about products
    and amount of them that customer has ordered.

    and here is a task with solution:
    I want to consider some of my orders. Lets say there are 10 products in this order and now I want to know if I have corresponding
    amount of each product in exaclty my wharehouse to satisfy clients order. And here is my SQL Select clause.

    SELECT OD.prOrdDetID, OD.ProdPPID, OD.OCount, ISNULL(SUM(S.RCount), 0) AS SRCount
    FROM Book AS B INNER JOIN
    Supplies AS S ON B.Book_id = S.Book_id AND B.RecStatus = 0 AND B.db = @Acc RIGHT OUTER JOIN
    OrderDetails AS OD
    WHERE (OD.OrderID = @OrderID)
    GROUP BY OD.prOrdDetID, OD.ProdPPID, OD.OCount

    prOrdDetID – Is order detail ID in OrderDetails Table
    ProdPPID – is product ID
    OCount – is amount of products that has been ordered by customer
    SRCount – is summary of product income in my wharehouse.

    As you can see, first I’m taking Book and Supplies table and then I’m joining to OrderDetails
    if I’d take OrderDetails table first and LEFT OUTER JOIN Them to Supplies + Book tables I’d loose (B.db = @Acc) filter
    whare db indicates wharehouse number (in which I’m want to know product amount)

    Like

    • Hi Mark,

      Very good explanation.Dont you mind may be its a silly doubt but if you make this query as left outer join still you can filter this condition in your where clause right ???

      Let me know if this condition will affect any where or will give me some error…..

      Thanks and regards
      kamesh shah

      Like

  5. Pingback: SQL SERVER – 3 Simple Puzzles – Need Your Suggestions Journey to SQL Authority with Pinal Dave

  6. Interesting!!! topic pinaldave!! I was thought about it earilier!! One of the interviewer asked me the same question long time back.I agree with Jason Yousef
    “if you’re joining 3 tables or more. you can’t reverse the table order when you join the 3rd table to the 2nd.”
    I think it is perfect answer

    Like

  7. It takes a little extra syntax, but you can reverse the order if you really want to:

    Symbolically, a J b RJ c => c LJ ( a J b )
    And the parens are important.

    In T-SQL:

    SELECT * FROM a
    INNER JOIN b ON a.x = b.k
    RIGHT JOIN c ON a.y = c.j

    can be rewritten without RIGHT JOIN as

    SELECT *FROM c
    LEFT JOIN (
    SELECT * FROM a — disambiguate common columns in *
    INNER JOIN b ON a.x = b.k
    ) d ON c.j = d.y

    but the RIGHT JOIN is less noisy.

    Like

  8. what I think is:
    1. imagine you have 3 table. all join together. you need all information of middle table
    so left join+ right join = full outer join
    it is more meaning full

    2.dear Marc maybe it is possible to write every queries with left in diffrent ways but a complicated code is not a good code especialy when language giving you tools.

    3. imagine you have 5 table joined to each other. it is useful to order tables in some way(2 parpose:1. optimization join. 2.readability of the query 3. achive correct data) so when you want use outer join in such condition, it depends which side is your table not what you prefer.

    Like

  9. 4. dear Matt Velic
    I am a right to left language writer and i found no relationship between the language and right outer join
    I think it is play with words and not a reason

    Like

  10. You can fix it with parenthesis however…

    SELECT * FROM a
    INNER JOIN b ON a.x = b.k
    RIGHT JOIN c ON a.y = c.j

    …becomes…

    SELECT * FROM
    c
    LEFT JOIN (a INNER JOIN b ON a.x = b.k) ON a.y = c.j

    Like

  11. Actually, you may not even need the parenthesis since the ORDER of the ON clauses actually determines the order that the tables are joined…

    SELECT * FROM c
    LEFT JOIN a
    INNER JOIN b
    ON a.x = b.k
    ON a.y = c.j

    Like

  12. Pingback: SQL SERVER – Weekly Series – Memory Lane – #005 « SQL Server Journey with SQL Authority

  13. Pingback: SQL SERVER – Weekly Series – Memory Lane – #006 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s