SQL Tips: Why we need to have RIGHT OUTER JOIN in SQL

Recently I was reading some article and in comments section I found this interesting question.

Q’s: If we perform left outer join on employee and department it will return all records from left table and only matching from right. similarly, right outer join will return all the records from department and only matching from employee table. NOW… what if we swap the tables in left outer join. i.e if we perform left outer join of department and employee which will return same records as output as right outer join.

THEN THE QUESTION IS WHY DO WE HAVE RIGHT OUTER JOIN IN SQL???

Before I dive into the answer, I would love to give a brief intro about Left outer join and Right outer join.

join_left_right

For the sake of this example, lets say you have 100 students, 70 of which have enrollments. You have a total of 50 enrollments, 40 of which have at least 1 student and 10 enrollments have no student.

LEFT OUTER JOIN would be “show me all students, with their corresponding enrollments if they have one”.
This might be a general student list, or could be used to identify students with no enrollments.
Returns 100 rows

RIGHT OUTER JOIN would be “show me all enrollments, and the students assigned to them if there are any”.
This could be used to identify enrollments that have no students assigned, or enrollments that have too many students.
Returns 80 rows (list of 70 students in the 40 enrollments, plus the 10 enrollments with no student)

Lets get back to our main question,”WHY DO WE NEED RIGHT OUTER JOIN”?

Answer to the above question was also provided by some anonymous user then and there in itself.

Anything you can write as a “RIGHT OUTER” could be written as a “LEFT OUTER” by just flipping the tables, so technically “RIGHT OUTER” isn’t functionally required in order to have working code.

It was created to allow the coder to express it that way if it makes more sense to them that way. So basically it exists for readability and potential maintainability.

Having said that I rarely see anyone code a “RIGHT OUTER”, most people just automatically order their tables to be “LEFT OUTER” joins if an outer is needed.

I found it interesting, so thought to share with others also. May be it can be of some help to some one else.

Advertisements

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