16

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

leppie
  • 115,091
  • 17
  • 196
  • 297
user3123794
  • 225
  • 2
  • 10

2 Answers2

13

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

Vineet
  • 146
  • 4
  • The terminology (the optional prefixes inner/outer) always felt imprecise to me. But it just being an implementation detail makes perfect sense. Thanks. – Philippe Dec 28 '22 at 10:05
3

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

Community
  • 1
  • 1
Craig Tullis
  • 9,939
  • 2
  • 21
  • 21
  • Relational algebra is based on set theory. In set theory intersection means common elements between two sets. While joining rows get created. Multiple rows get created if the row in first table matches multiple rows in second table. Hence I don't understand why intersection is an intuitive way of thinking about it. Check the below answer that I marked as correct. Although technical it seems to be precise. But it also means that the names (inner / outer join) stuck based on the implementation. Would like to know your thoughts on it. – user3123794 Dec 01 '14 at 17:27
  • Well, I did say intersection of *keys*. Yes, additional virtual records are created in the result set when a key in one tables has multiple matches in another table. But you're still looking at all the records from both tables where the set of keys intersects, and none of the records where they don't. – Craig Tullis Dec 01 '14 at 19:05
  • @user3123794 The only real issue I have with the accepted answer, I suppose, is that it focuses on the implementation (...inner join, the outer loop iterates over this...inner loop iterates over that...creates composite rows...). Implementations can differ, and I don't see any bibliographical references backing up the assertions. Nothing in that answer actually addresses Cobb's relational algebra, it just talks about loops. And relational algebra doesn't address outer joins. I don't see how the accepted answer explains why an inner join is an inner join, and an outer join is an outer join. – Craig Tullis Dec 02 '14 at 00:53