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?
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)
23 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…
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.
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.
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.
Right…
In this condition we need right outer join….
–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
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.
For me mostly it has been LEFT JOINS. Thanks to Michael for sharing his experience in using RIGHT JOINS.
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.
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)
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
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
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.
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.
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
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
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
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.
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.
select * from t1 right join t2 on t1.id=t2.id left join t3 on t2.id=t3.id;
+——+——-+—-+——-+——+——-+
| id | value | id | value | id | value |
+——+——-+—-+——-+——+——-+
| 4 | d | 4 | f | NULL | NULL |
| 5 | e | 5 | g | NULL | NULL |
| NULL | NULL | 6 | i | 6 | ac |
| NULL | NULL | 7 | j | 7 | ad |
+——+——-+—-+——-+——+——-+
4 rows in set (0.00 sec)
select * from t2 left join t1 on t1.id=t2.id left join t3 on t2.id=t3.id;
+—-+——-+——+——-+——+——-+
| id | value | id | value | id | value |
+—-+——-+——+——-+——+——-+
| 4 | f | 4 | d | NULL | NULL |
| 5 | g | 5 | e | NULL | NULL |
| 6 | i | NULL | NULL | 6 | ac |
| 7 | j | NULL | NULL | 7 | ad |
+—-+——-+——+——-+——+——-+
4 rows in set (0.00 sec)
Also using one join (either left or right ) we can achieve our results. I’m not sure when do using only left join or right join then another join exist. I’m fresher So anyone please explain me. Here I only see difference of column positions. Nothing else