0

A related question & answer (Is It Appropriate to use Venn Diagrams to Depict SQL Joins where the Tables are Sets?) demonstrate that it is inappropriate to characterise joins, in general, with the following Venn diagram:

enter image description here

However, my intuition still says that Venn diagrams can be useful to depict some relationships between inner/outer/left/right joins. Can this intuition be formalised?

Colm Bhandal
  • 3,343
  • 2
  • 18
  • 29
  • You have no less that _three_ wordy questions on this topic. Surely this has been discussed to oblivion elsewhere. – Nick.Mc May 09 '20 at 11:49
  • https://blog.jooq.org/2016/07/05/say-no-to-venn-diagrams-when-explaining-joins/ –  May 09 '20 at 13:29
  • @Nick.McDermaid I have indeed got many questions on this topic. They are all asking different things. Indeed, there are other places on the web like blogs that might discuss joins at length, and there are textbooks that explain them at length, but I want people who visit this site to be able to get accurate information on joins. And unfortunately, the existing top-voted explanations of joins are inadequate and the good explanations are buried beneath the inadequate ones. – Colm Bhandal May 10 '20 at 14:47

2 Answers2

1

I agree with the answers to the other question that specify that Venn diagrams are not appropriate for explaining JOINs.

The primary reason is that Venn diagrams DO represents something useful and something important in understanding sets -- and hence SQL which is based on sets. What is depicted as "INNER JOIN" is really INTERSECT. What is depicted as "FULL JOIN" is really UNION.

Hence, I think it is confusing to use diagrams that accurately depict set operators and call them something else. Set operators and JOINs are different things.

If you wanted to visualize joins, you would really need additional dimensions.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • thanks for the comment. However, I'm unsure if this is an answer, a comment on my answer to this question, a comment on my answer to the linked question, or just a general comment? Please clarify... – Colm Bhandal May 09 '20 at 11:31
  • 1
    You don't explain what exactly is being represented how in such a diagram. A "depiction" of what exactly? "is really intersect/union" is not clear. – philipxy May 09 '20 at 17:20
  • @ColmBhandal . . . Your question is about the answer on another question. That is the answer this refers to. – Gordon Linoff May 09 '20 at 21:16
  • 1
    @philipxy: I think it's clear what Gordon means: the diagrams show the result of an INTERSECT, **not** the result of a INNER JOIN –  May 09 '20 at 21:38
  • @a_horse_with_no_name You are just as vague. What are the elements of the sets and what is each set (or if not set, circle)? I too can guess. That doesn't make it clear. Moreover table bodies aren't sets but bags, so explaining how elements end up in one place and not another is non-trivial. It's the same cavalier sloppiness over & over again. – philipxy May 09 '20 at 21:52
  • 1
    @philipxy . . . Tables are sets where each row is a relation. This is well defined. A Venn diagram depicts *sets*. There is not stretch to assuming that the Venn diagram is showing rows. It is in the definition of the Venn diagrams and tables. There is no vagueness if you understand what these things are. – Gordon Linoff May 09 '20 at 22:04
  • I understand quite well. SQL tables are not sets. Please don't make this ad hominem. I'm done. – philipxy May 09 '20 at 22:28
  • 1
    @philipxy: if tables are not sets, you can't use Venn diagrams to begin with, because Venn diagrams describe sets –  May 10 '20 at 06:36
  • @philipxy You have mentioned the sets vs. bags argument before. However, WLOG we can simply assign a unique ID to every row, and characterise the entire table as a set. This characterisation works for defining the joins. However, I think this entire argument misses the point of this question. – Colm Bhandal May 10 '20 at 14:43
  • @GordonLinoff I did not downvote this answer. However, I did not upvote it either because I don't think you fully understand the question. The question says that the exact interpretation of Venn diagrams given is wrong, we know that. But are there any other Venn diagrams that can be used in the exposition of joins? The answer is yes. And you in fact allude to this in your answer, which makes it even more confusing. – Colm Bhandal May 10 '20 at 14:49
  • @GordonLinoff I totally agree. I actually don't agree with anonymous downvotes at all. Meta-topic that's been discussed I'm sure. I notice that you edited your answer... but I think you're missing the subtlety of the question. I ask about characterising the relationship between the joins rather than defining the joins in the first place. For characterising the relationship it's find to use Venn diagrams, but you of course still have to define them in the first place. That's why I posted this Q&A pair together. – Colm Bhandal May 10 '20 at 14:57
-1

Yes. There is a Venn diagram that does make sense to characterise the relationships between the various different types of joins. However, it is not the Venn diagram shown in the question. This is inappropriate, as shown in the linked question. So what Venn diagram is appropriate?

Well, let's say we already have defined the sets representing the result of left Join and right join. Let's call those sets Left and Right. Then we have the following relationship:

  • Left ⋃ Right = Full Outer Join
  • Left ⋂ Right = Inner Join

As a Venn diagram:

enter image description here

Pedagogical Note

While these Venn diagrams accurately characterise the relationship between the various types of joins, they do not suffice to define joins. So if you're looking to understand joins from the ground up, this is not the place to start. But if you're already comfortable with what the JOINs mean, and are looking for a unifying picture that ties together all the various types of joins, this is it.

Of course, technically, one could say that this allows us to define inner and outer join in terms of LEFT and RIGHT joins. But LEFT and RIGHT joins are themselves more complicated to define than INNER joins, so for this purpose the diagram is of limited use.

Colm Bhandal
  • 3,343
  • 2
  • 18
  • 29
  • 1
    Voting is anonymous on SO/SE & need not come with a comment so it is inappropriate to suggest that voting without a comment is somehow rude. Moreover downvotes accompanied by comments frequently are responded to by revenge downvotes. – philipxy May 11 '20 at 02:11
  • The diagrams aren't presented clearly. They are in the wrong order. It's not clear what "inner" & "outer" or the blue are doing. The "outer" should be "full". You don't actually say, this is for the case when the 4 outputs are sets of row values. **Always give a legend for all diagram aspects.** To explain further--like to give a tutorial on how to read the diagram--but otherwise, since the legend is clear, why?--**use words to clearly say what is going on**, like, 'in the left pair of circles, the left circle represents ... the lower label per the blue is the intersection of ...', etc.** – philipxy May 11 '20 at 02:32
  • @philipxy OK fair enough I now see the problem with non-anonymous downvoting. However, anonymous downvoting is rude, in my opinion. Because it doesn't allow one to improve one's answer. In fact, I'm starting to think downvoting is entirely a bad idea. There should be just better flags, allowing things to be flagged away for specific reasons. That way, we can have anonymity but also helpful criticism. – Colm Bhandal May 11 '20 at 08:40
  • As for your specific crisicisms, @philipxy, thank you for these. They are all valid and they are indicative that this answer does, indeed, deserve a downvote. I will edit it if I find the mental strength to do so, knowing that my edit may not prompt the downvoter to remove their downvote. – Colm Bhandal May 11 '20 at 08:42
  • You might be interested to google or SE-search & read some of the trillion [meta.se] & [meta] Qs&As around downvoting. [Why isn't providing feedback mandatory on downvotes, and why are ideas suggesting such negatively received?](https://meta.stackexchange.com/q/325416/266284) – philipxy May 11 '20 at 20:19
  • @philipxy I plan to delve into the issue of downvotes. Thanks. – Colm Bhandal May 12 '20 at 17:05