Need for right and left outer join

Question

Need for right and left outer join

shailesh.thapliyal

Right there with Babe

Points: 750
More actions
September 16, 2010 at 5:59 am

#238253

Hi,
just a question out of curiosity, do we really need both the outer join(Left and Right). both work in same way if we flip the position of the tables in query.
In the below case, both teh queries will produce the same results.
Query1:
SELECT a.ColumnA, a.ColumnB, b.ColumnX, b.ColumnY
FROM TableA a Left Outer join TableB b
ON a.N = b.M
Query 2:
SELECT a.ColumnA, a.ColumnB, b.ColumnX, b.ColumnY
FROM TableB b Right Outer join TableA a
ON b.M = a.N

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

Well you don't need the word 'outer'

I guess it depends, i always use left join as I like to think from the left when bulding queries, other people may be different

Ray K SSC-Dedicated Points: 31163 More actions · Answer 2

You could probably get away with it in theory, but what happens when you start getting into complex table joins where you need to differentiate LEFT and RIGHT join? It starts getting pretty messy.

Also, how do you differentiate it from a FULL join?

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

The only time I ever encountered a requirement for both was a hypothetical question in an interview a few years ago. If I see RIGHT JOIN in a query I've been tasked to optimise, I'll almost always make it the FROM table and shuffle the other joins around accordingly.

Seeing both in a query is a good indication that the query was generated either by a query tool or, more likely, by "trial and error".

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

hallidayd SSCrazy Points: 2490 More actions · Answer 4

I only ever use left outer joins, never right.

The only exception I know of that I might use a right outer join (and this is theoretical only, I've never had cause to do it) is when employing a join hint that requires a particular table order.

Other than that I'd be happy for right outer joins to be deprecated; they serve no purpose other than to confuse.

hallidayd SSCrazy Points: 2490 More actions · Answer 5

By a complete coincidence the next thing I read after posting was this:

http://blogs.msdn.com/b/craigfr/archive/2006/07/26/679319.aspx

Nested loops don't support ROJs. So there's a reason right there not to use them.