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