I use INNER JOIN and LEFT OUTER JOINs all the time. However, I never seem to need RIGHT OUTER JOINs, ever.
I've seen plenty of nasty auto-generated SQL that uses right joins, but to me, that code is impossible to get my head around. I always need to rewrite it using inner and left joins to make heads or tails of it.
Does anyone actually write queries using Right joins?
No, I don't for the simple reason I can accomplish everything with inner or left joins.
You usually use RIGHT OUTER JOINS to find orphan items in other tables.
Our standard practice here is to write everything in terms of LEFT JOINs if possible. We've occasionally used FULL OUTER JOINs if we've needed to, but never RIGHT JOINs.
It depends on what side of the join you put each table.
If you want to return all rows from the left table, even if there are no matches in the right table... you use left join.
If you want to return all rows from the right table, even if there are no matches in the left table, you use right join.
Interestingly enough, I rarely used right joins.
The only time I use a Right outer join is when I am working on an existing query and need to change it (normally from an inner). I could reverse the join and make it a left and probably be ok, but I try and reduce the amount of things I change when I modify code.
This is more of a question within a question.
Does this hold?
Tbl_A <right outer join> Tbl_B
is the same as
Tbl_B <left outer join> Tbl_A
I only use left, but let me say they are really the same depending how how you order things. I worked with some people that only used right, becasue they built queries from the inside out and liked to keep their main items at the bottom thus in their minds it made sense to only use right.
I.e.
Got main thing here
need more junk
More Junk right outer join Main Stuff
I prefer to do main stuff then junk... So left outer works for me.
So whatever floats your boat.
You can accomplish the same thing using LEFT or RIGHT joins. Generally most people think in terms of a LEFT join probably because we read from left to right. It really comes down to being consistent. Your team should focus on using either LEFT or RIGHT joins, not both, as they are essentially the same exact thing, written differently.
Rarely, as stated you can usually reorder and use a left join. Also I naturally tend to order the data, so that left joins work for getting the data I require. I think the same can be said of full outer and cross joins, most people tend to stay away from them.