Use Checkerboard Diagrams to Visualize Joins Instead of Venn Diagrams

26

Use Checkerboard Diagrams to Visualize Joins Instead of Venn Diagrams databases towardsdatascience.com
via carlmjohnson 10 months ago | archive
Archive.org Archive.today Ghostarchive
| 6 comments

6

5

sjamaan 10 months ago | link

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.

jonahx edited 10 months ago | link

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

tncowart 10 months ago | link

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

jonahx 10 months ago | link

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

4

knl 10 months ago | link

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.
3

steinuil 10 months ago | link

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…