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 (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – Puzzle – Challenge – Error While Converting Money to Decimal
Next Post
SQL SERVER – DBA or DBD? – Database Administrator or Database Developer

Related Posts

22 Comments. Leave new

  • 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…

    Reply
  • Jeremiah Peschka
    November 28, 2010 7:15 pm

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

    Reply
  • 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.

    Reply
  • Yep, if you’re joining 3 tables or more. you can’t reverse the table order when you join the 3rd table to the 2nd.

    Reply
    • Right…
      In this condition we need right outer join….

      Reply
    • –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

      Reply
      • 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

    • I also agree with this . This is one imp reason when u join multiple tables…ROJ needed for proper result.

      Reply
  • For me mostly it has been LEFT JOINS. Thanks to Michael for sharing his experience in using RIGHT JOINS.

    Reply
  • To compare what’s missing from the left table only. But I always started with a LEFT OUTER JOIN to to the same from the right table.

    Reply
  • 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)

    Reply
    • very good explanation,

      Would you be able to come up with similar example in AdventureWorks database?

      Reply
    • 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

      Reply
  • 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

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • 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

    Reply
  • 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

    Reply
  • 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

    Reply
  • Interesting discussion. Whenever I’ve encountered a right join, I’ve typically assumed someone’s used the designer and left it at that. I’ve always rewritten the query to use left joins primarily because it’s easier to digest and maintain queries that lend themselves to a top down, left to right logic flow.

    While it’s not such an issue for simple queries, once you start introducing several joins and conditions, it can quickly become a nightmare to try and get and keep your head around the logic. And a testament to this is the number of times I’ve seen developers wrap a query in a nested view or an outer query rather than wrestle with the existing query. Given how small our world is, perhaps it’s not a bad idea to be thinking of the poor bugger that’s going to inherit what we write ;)

    My preference where I need to ensure that two separate logic streams meet and relate correctly is to use WITH BLOCKs to or APPLY statements. This more often than not also avoids introducing performance related issues or at least makes them easier to pinpoint and work on.

    Reply
  • if we have tables like this,
    T1 as C1 c2
    1 a
    2 b
    3 c
    4 d
    5 e
    and
    T2 as 3 e
    4 f
    5 g
    6 i
    7 j
    and
    T3 as 1 aa
    2 ab
    6 ac
    7 ad
    8 ae

    select * from t1 right join t2 on t1.c1=t2.c1 left join t3 on t2.c1=t3.c1;

    here we used both left and right joins
    T2 is center table which has data mutali in both T1 and T3

    Result;
    c1 c2 c1 c2 c1 c2
    6 i 6 ac
    7 j 7 ad
    5 e 5 g
    4 d 4 f
    3 c 3 e

    When we have join more than one table by reference with another table we need to use both the join as per the requirements.

    Reply

Leave a Reply

Menu