Why is inner join and outer join so called?

Question

I understand the working of inner and outer joins. But what is the meaning of the words inner / outer in this context? What is so inner about inner join? What is outer about outer join?

Best Regards, Vishal

Answer 1

One more perspective:

One of the earliest simple implementations for joins used nested loops.

1. For inner join, the outer loop would iterate over any relation and the inner loop would iterate over the other relation and create composite rows whenever join columns matched. Thus the output rows get created and populated in the inner loop. Hence this is called INNER JOIN.

2. When we want all rows in left side relation\table to be retained, the outer loop will have to iterate on the left table and rows would be added not only in the inner loop for matching cases but also in the outer loop for non-matching cases(where left table doesn't have a matching row in right table based on join columns). In this case, the left table needs to go to the outer loop, so it is called LEFT OUTER JOIN.

3. When we want all rows in right side relation\table to be retained, right table will need to go into outer loop, so it is called RIGHT OUTER JOIN.

4. When we want non matching rows of both tables to be retained, in the simplest approach, we would have two nested loops. One nested loop would have left table in the outer loop and the other nested loop would have right table in the outer loop. So both tables go to outer loop, hence it is called FULL OUTER JOIN.

Adding the link to the paper that talks about nested loop implementation : http://www.cs.berkeley.edu/~brewer/cs262/3-selinger79.pdf

Answer 2

An inner join will return only records where the join keys exist in all of the joined tables, or put another way, it will return the records with keys that fall within the intersection of the joined tables. The keys are in that intesection. An outer join will return all the records in the intersection, as well as the records outside the intersection.

Here's a thoroughly beat-up post on the subject here on Stack Overflow: What is the difference between Left, Right, Outer and Inner Joins?

And another: What is the difference between "INNER JOIN" and "OUTER JOIN"?

Actually, I suppose your question is a duplicate. ;-)