Why right join in sql?

Question

Today i was asked a question in an interview:

If A left join B can produce the same result as B right join A, then why do we need right join? Why it was invented if one already exists?

Any advantage? Performance benefits?.. or just a keyword to use?

Guru answer please?

Answer 1

Simply put, because A LEFT JOIN B can produce different results from A RIGHT JOIN B. Whether or not those two join types produce the same result mostly depends what you want to see happen if matching records don't exist in one table or the other.



Let's say you have a table for customers, and each customer can have several addresses, so you have another table for addresses. If you use a LEFT join between them, you'll get a row back for the customer even if the customer doesn't have an address at all. If you use RIGHT join, you'll get a row back for every address that fits the WHERE condition, whether the customer record exists or not.



But if matching-ID records exist in both tables, then as you've probably observed, you'll probably see identical results regardless of which table is on the left or right - depending on what's in your ON and WHERE clauses, of course.



There isn't likely to be a noticeable performance benefit (or degradation) in using B RIGHT JOIN A as opposed to A LEFT JOIN B, as long as both tables are indexed on the join fields. That's a huge generalization, though - every database is different, so you'd just have to try it both ways and choose if you want to optimize things.

Answer 2

Moses appears to have misread the question - A L B indeed is different from A R B, but the question was A L B vs B R A.



While it appears unnecessary, it really comes down to what makes the most sense to whoever is evaluating the results. Performance-wise, I'm unaware of any inherent difference (but then, I use outer joins rarely and can't recall ever having to use an outer right.) The total number of comparisons needed would seem to be the same either way, so even if the relative number of rows is widely different between the two tables, it shouldn't matter whether the larger or smaller is evaluated first in the overall process.

Answer 3

often, there are 2 varieties of joins: inner and outer. (As a definite case, a table could be joined with itself - it is termed self-connect.) An inner connect surely combines the information from 2 tables A and B in accordance with a given connect predicate ( connect predicate could be any predicate supported with the help of sq., as an occasion a the place clause.) The go made of all information in table is computed. hence, each and each checklist in table A is blended with each and every checklist in table B. basically those information in the joined table that fulfill the connect predicate proceed to be. it is the main common kind of connect and is seen the default connect kind. Outer joins do not require that each and each checklist in the two joined tables has an comparable checklist in the different table. The checklist is retained in the joined table if no matching different checklist exists. the object I blanketed under has some very reliable examples.