1. 26
  1.  

  2. 5

    I can’t say I find this so obvious to read. Also, if I understand correctly, the checkerboard diagrams require several records of concrete sample data to show how the join works, whereas Venn diagrams are more abstract; they show at a glance the overlap between table names.

    1. 5

      I much prefer simple visualizations. This one is busy and has many distracting details: the unnecessary right angle connections and angled presentation, the light gray graph lines within the connections, the color circles at the right angles – there are either better alternatives for these or they can be dropped. Honestly I would prefer the following plain text explanation:

                      INNER JOIN
      Inputs                     Result
      
      X1  1 --- Y1  1            1  X1   Y1
      X2  2 --- Y2  2            2  X2   Y2
      X3  3     Y3  4
      
                      LEFT JOIN
      Inputs                     Result
      
      X1  1 --- Y1  1            1  X1   Y1
      X2  2 --- Y2  2            2  X2   Y2
      X3  3     Y3  4            3  X3   NULL
      
                      OUTER JOIN
      Inputs                     Result
      
      X1  1 --- Y1  1            1  X1   Y1
      X2  2 --- Y2  2            2  X2   Y2
      X3  3     Y3  4            3  X3   NULL
                                 4  NULL Y3
      
      1. 1

        How would you do a plain text cross join? In particular, how would you “draw” the connections?

        1. 1

          I think this one is conceptually very simple, since it’s just the Cartesian product. Something like this looks good to me:

                          CROSS JOIN
          
                    Note: Every element of 
                    X matched with every element
                    of Y.  Cartesian product.
          
          Inputs                     Result
          
          X1  1 \ / Y1  1            1  X1   Y1
          X2  2 -X- Y2  2            1  X1   Y2
          X3  3 / \ Y3  4            1  X1   Y3
          
                                     2  X2   Y1
                                     2  X2   Y2
                                     2  X2   Y3
          
                                     3  X3   Y1
                                     3  X3   Y2
                                     3  X3   Y3
          
      2. 4

        This is a pretty well thought out visualization, I was looking for something like this! Seems that the original idea comes from Hadley Wickham: https://r4ds.had.co.nz/relational-data.html — he seems to have a knack to produce well-understandable tools and visualizations.

        1. 3

          I think the Venn diagrams are fine for when you already know how joins work and you just need to refresh your mind on which specific one you need. I don’t know how good they are for learning. The thing that helped me get comfortable with joins was understanding unification in Prolog/Datalog, though knowing that also makes me wish I could query the databases using those languages instead of SQL…