0

In a related question (Is It Appropriate to use Venn Diagrams to Depict SQL Joins where the Tables are Sets?) it was shown that the following image was not appropriate, in general, to characterise Inner Join in terms of Table A and Table B with a very specific interpretation of it as a Venn diagram.

However, my intuition still says that there is some "truth" to this picture, even though it is not formally correct in the general case. So, I ask, is there a special case of INNER JOIN, and an interpretation of the below diagram, or something close, whereby the diagram makes sense?

enter image description here

Colm Bhandal
  • 3,343
  • 2
  • 18
  • 29
  • Because the picture depicts the `INTERSECT` operator -- which would only coincidently be related to any `JOIN` -- it is not an appropriate picture of any type of `JOIN`. – Gordon Linoff May 09 '20 at 11:27
  • @GordonLinoff Yes, that picture above is wrong, when we interpret the set on the left as the set of records in A and the right as the set of records in B. But if we restrict to a special case of join, and we change the definition of the sets to not records but unique IDs, then the Venn diagram is accurate. That's what my answer shows. On a pedagogical note though, I'm afraid this special case is at the heart of all the confusion around JOINs that's all over the web today. – Colm Bhandal May 09 '20 at 11:34
  • . . They really have nothing to do with each other. The result set from an inner join does not consist of records that are both in A and B. That is what `intersect` does. In fact, a join could multiple rows and produce a result set larger than *both* A and B, combined. – Gordon Linoff May 09 '20 at 21:26
  • @GordonLinoff I think we're on the same side. I am 99% against using Venn diagrams to explain joins because they do not characterise the general case, as you correctly point out. This question is simply me playing devil's advocate and trying to see where people are coming from when they misuse Venn diagrams as above. And I think my answer shows this. They are not really doing a Venn diagram on the Tables. They're doing it on the unique IDs, and assuming the ON condition is one-to-one. Please read my answer carefully for full detail. – Colm Bhandal May 10 '20 at 14:53

1 Answers1

1

Yes, there is at least one special case (but maybe more) of join and a closely related Venn diagram to the one shown that does characterise said special case of join. And although this is a special case, in the mathematical sense, it is actually a very common use case in the real world, to the point where it is almost the standard use case of join, practically. So it deserves attention.

The special case of join we will consider has the following restrictions:

  • Both tables being joined have unique keys. Let's call them IDa and IDb
  • The ON condition is IDa = IDb

In this case, we can define two sets SetA and SetB, which are the sets of all the IDs in the columns IDa and IDb. From there, since both sets are of the same type, they can have a non-empty intersection. Every ID in the intersection will define a record in the resulting joined table. Since the ID is in the intersection of both sets, and since the IDs are unique, it will correspond to exactly one record in each table. Thus the record in the resulting table will be the combination (union of column to value mappings) of the record in Table A and the record in Table B, for that ID.

Example

The following picture shows two tables TableA and TableB that satisfy the special case, and the result of a join:

enter image description here

The exact SQL for the join is:

SELECT * FROM TableA JOIN TableB ON TableA.ID = TableB.ID

The ID sets are 1, 2, 3 and 1, 2, 4 respectively. So the intersection is 1, 2. Therefore we select rows with IDs 1 and 2 from both tables and put them together to get the rows in the resultant table. The Venn diagram representation of these sets and their intersection is:

enter image description here

Pedagogical Note

Although joining on a single unique ID is a very common use case in the real world, the limitations of Venn diagrams to explain joins in the general case must be understood. As long as you understand this point, there is no harm in applying Venn diagrams in your understanding of this special case.

Colm Bhandal
  • 3,343
  • 2
  • 18
  • 29