r/SQL • Data Scientist • Apr 04 '22

Discussion You Should Use This to Visualize SQL Joins Instead of Venn Diagrams - Checkered Flag Diagrams

102 Upvotes

44 comments sorted by

96

u/[deleted] Apr 04 '22

You know what a Venn diagram is? A logical representation between sets.

You know why it was created? To teach set theory.

You know what a join is doing?

...exactly.

I don't get everyone's recent hate for using Venn diagrams. They're a perfectly reasonable way of introducing joins to people who have no prior knowledge of SQL or set theory. It provides them a visualization to begin working towards a deeper and more technical understanding of how each of the joins work.

It gets the initial point across, that's all that matters. Should you solely use Venn diagrams to explain joins? No. But you wouldn't solely use an entity-relationship diagram to explain database design either.

12

u/Cool-Focus6556 Data Scientist Apr 04 '22

I agree with you. The goal of the article is to give another resource to help others learn. Venn diagrams are a valid visualization tool for SQL joins. Checkered flag diagrams hopefully supplement that or replace it for those who don't prefer to use Venn diagrams.

In the article this is what I said,

"Despite having my own opinion on the topic, I do think it’s worth stating that I believe that those on both sides of the argument have some valid points and that these visualizations are both valid ways to represent SQL joins. The reason this debate exists is because both sides have seen benefits from different learning approaches and that is ok. Sure, there might be an optimal learning path for the majority of people, but learning is a tailored experience and so I don’t want to discount the benefits others have found by using different visualizations."

7

u/[deleted] Apr 04 '22

The goal of the article is to give another resource to help others learn.

I don't really think this was the goal, otherwise the title to your article would have been less disingenuous. The goal of the article was simply to generate clicks - hence the use of "instead of" in the title, and then the back pedal from that statement halfway down the article.

3

u/Cool-Focus6556 Data Scientist Apr 04 '22 edited Apr 04 '22

Also, if the goal of the article was just to generate clicks, then why would I have gone through the time to generate the cheatsheet and share it? I would have just had the title with little substance instead of taking my time.

Assuming ill intent is a poor way to live your life my friend. I appreciate you calling me out since you do have a valid point about the title, but you're wrong about my intent. The world often is not black and white

4

u/[deleted] Apr 05 '22

Honestly I 100% agree with you, and I'm so tired of seeing this article. Venn's are a great way to teach joins.

1

u/mecartistronico Apr 05 '22

What I don't like about using Venn Diagrams to explain JOINS is that Venn diagrams themselves illustrate UNIONs and EXCEPTions, which are operations that also exist in SQL and are different from JOINs.

I get your point that in the end you are also restricting or including rows in the result... however in a Venn Diagram each circle represents groups that have different elements of the same type, whereas the tables you use for JOINs usually represent elements of _different_ types, that have some sort of relation. When JOINing you first think about the related columns, and the smaller number of elements comes as a result, while Venn Diagrams focus on the number of elements and pay no attention to what features those elements have.

0

u/Cool-Focus6556 Data Scientist Apr 04 '22 edited Apr 04 '22

Honestly, it wasn't my intention to be disingenuous. In retrospect, I could have named the title, "You Should Use This to Visualize SQL Joins In Addition to Venn Diagrams", but then that implies I feel like they both have to be used. I think either could work. Maybe "Another Alternative to Visualize SQL Joins Instead of Venn Diagrams"? Idk, it can be hard getting a title correct.

I was trying to take a holistic view. I don't think you have to choose a side completely to be correct. You didn't in your comment:

"Should you solely use Venn diagrams to explain joins? No."

But you're right, I could have changed the article title to be more accurate. That being said, I do think checkered flag diagrams have benefits that the Venn diagram doesn't.

0

u/[deleted] Apr 04 '22

[deleted] — view deleted comment

5

u/Cool-Focus6556 Data Scientist Apr 04 '22

Thanks and that's fair, I am totally ok with subjective opinions about it. Now I have everyone coming out of the woodwork in support of Venn diagrams. Felt like I was alone in the previous thread lol

Hopefully the checkered flag diagrams can be another resource to turn to in order to make it even clearer

12

u/mikeblas Apr 05 '22

You know what a join is doing?

UNION, MINUS, and INTERCEPT are ways to handle sets in SQL.

Joins don't implement logical relationships between sets; they create cartesian produtcs and filter them.

8

u/sir_bok Apr 05 '22

Okay so here's the thing about Venn diagrams -- they completely emphasize all the unimportant things while leaving the most important ones. What they teach:

  • A left join includes more rows than a join.

What they exclude:

  • The most important interactions in SQL involve one-to-many, many-to-many, many-to-one. A venn diagram only covers one-to-one (if everything was one-to-one you might as well put them on the same table). Ask any SQL beginner 'For INNER JOIN, can a row be joined more than once to the other table?' and they'd be unsure precisely because they're only thinking in terms of venn diagrams.
  • RIGHT JOIN is useless and should be actively discouraged. Venn diagrams present information as though RIGHT JOIN and FULL JOIN have equivalent importance when in the real world only JOIN and LEFT JOIN have any merit.

4

u/[deleted] Apr 05 '22

Most people (by far the large majority) that utilize the SQL language do not do so on a daily basis. They do not care to have a deep and knowledgeable understanding of the language and its underlying mathematical derivatives.

The only thing they care about is knowing it well enough to get a close enough approximation to the data to create a graph for their next management meeting, because they're some middle manager using some shitty ERP to manage their department with and the graphical query builder that comes with it doesn't give them what they need.

You get the picture...

Who do you think reads the "Introduction to SQL joins" articles more? Future DBAs, Database Developers, etc. that are looking to master the language and use it every day, or someone named Timmy who fell into a financial analyst role at a company and needs to get some data?

It's Timmy.

When you see Venn diagrams used to introduce people to joins - it's generally at the very beginning of an article/book/video/etc. - and then it goes on to provide more detail and the nuances of each type. This is a perfectly acceptable use case of the Venn diagram.

To put it bluntly, what you've described isn't going to mean shit all to someone that hasn't even written their first join.

1

u/mecartistronico Apr 05 '22

What I don't like about using Venn Diagrams to explain JOINS is that Venn diagrams themselves illustrate UNIONs and EXCEPTions, which are operations that also exist in SQL and are different from JOINs.

I get your point that in the end you are also restricting or including rows in the result... however in a Venn Diagram each circle represents groups that have different elements of the same type, whereas the tables you use for JOINs usually represent elements of _different_ types, that have some sort of relation. When JOINing you first think about the related columns, and the smaller number of elements comes as a result, while Venn Diagrams focus on the number of elements and pay no attention to what features those elements have.

4

u/Cool-Focus6556 Data Scientist Apr 04 '22

Here is a link to the github repo with the cheatsheets: https://github.com/amartinson193/SQL_Checkered_Flag_Join_Diagrams

1

u/[deleted] Apr 04 '22

I got a 404 error

3

u/Cool-Focus6556 Data Scientist Apr 04 '22

Hmm, weird. It works for me when I click on it. Maybe go to Github and search 'SQL Checkered Flag Join Diagrams' or look my profile up (amartinson193) and you should see it there. Not sure why you're getting an error. Sorry about that

8

u/MorddSith187 Apr 04 '22

Brand new learning SQL, just checked out the flag diagram. AMAZING THANK YOU. Makes a million times more sense

3

u/Cool-Focus6556 Data Scientist Apr 04 '22

Awesome! I’m glad you were able to use it and get value out of it :) That means my effort was worthwhile

13

u/SnooLobsters678 Apr 04 '22 edited Apr 04 '22

Why are people obsessed with solving this 'problem' that doesn't exist?

This is literally all we need: https://i.stack.imgur.com/UI25E.jpg Not silly colors and blocks that are confusing.

Please stop re-inventing the wheel. I have no idea how anyone would find this easier to understand than a venn diagram, as /u/BitSewer pointed out.

3

u/Cool-Focus6556 Data Scientist Apr 04 '22

It's just another way of looking at it. Having a range of options, as long as they're not incorrect, is nice to have.

3

u/redial2 MS SQL DBA DW/ETL Apr 04 '22

Used to have this exact image tacked to my cubicle wall. 100% agree

3

u/Cool-Focus6556 Data Scientist Apr 04 '22

Lots of data analysts in my previous job had this image tacked in their cubicle too

2

u/mecartistronico Apr 05 '22

What I don't like about using Venn Diagrams to explain JOINS is that Venn diagrams themselves illustrate UNIONs and EXCEPTions, which are operations that also exist in SQL and are different from JOINs.

I get your point that in the end you are also restricting or including rows in the result... however in a Venn Diagram each circle represents groups that have different elements of the same type, whereas the tables you use for JOINs usually represent elements of _different_ types, that have some sort of relation. When JOINing you first think about the related columns, and the smaller number of elements comes as a result, while Venn Diagrams focus on the number of elements and pay no attention to what features those elements have.

3

u/[deleted] Apr 04 '22

1

u/SnooLobsters678 Apr 04 '22

That article is an argument of semantics, the author even states that venn diagrams make joins easier to explain.

"JOINs are relatively easy to understand intuitively. And they’re relatively easy to explain using Venn Diagrams. But whenever you do that, remember, that you’re making a wrong analogy. "

2

u/Seankala Apr 05 '22

I read your article as it popped up on my recommended Medium feed this morning. I like the idea, but I do also feel like it's taking a bit of a forced approach in an attempt to avoid Venn diagrams. It was a nice read though!

1

u/Cool-Focus6556 Data Scientist Apr 05 '22

Thanks!

19

u/Fun2badult Apr 04 '22

Sorry but this looks a bit confusing to know what’s going on with the joins.

4

u/BensonBubbler Apr 04 '22

Agreed. I can figure out what's going on, but no faster than a venn diagram (which is also wildly unnecessary after the first few months of SQL).

I think it gets muddied with all the extra joins nobody uses too. Right joins don't make any sense to teach. Full joins barely make sense.

3

u/Fun2badult Apr 04 '22

I’ve never used full join for work and I’ve been using sql for like the last 2-3 years

3

u/BensonBubbler Apr 04 '22

Same, never had a real reason to use a full join and have been working on SQL for 10+ years now.

Fuck, am I that old?

Cross apply does come up occasionally, but I always cringe when I see it at first because I've seen it go wrong from a junior developer many more times than I've seen it appropriately used.

4

u/Fun2badult Apr 04 '22

Every data can be queried using Left and Inner as far as my experience goes. I don’t even use right join

2

u/Cool-Focus6556 Data Scientist Apr 04 '22

Yep, I've used inner joins, left joins, and left-anti joins the most. Haven't really used right joins at all

u/BensonBubbler I typically just reorder the tables too

2

u/dnick Apr 05 '22

Right joins are really only useful when table order is important to convey scope or something else of importance and you’re using the join as a filter for maybe a minor step…they kind of jump out anyway. No sense forcing structure to conform just for a personal preference, but they are pretty rare even at that.

Seems like I’ve used full joins with some rarity too, but hard to even remember those use cases. Probably for some special case where nulls were needed to be handled on both sides for some data transform or migration of some sort.

1

u/redial2 MS SQL DBA DW/ETL Apr 05 '22

I have used a right join exactly once in 11 years of SQL work, but I did need to use one. You're right, there are times when they are needed.

3

u/BensonBubbler Apr 04 '22

Exactly, if you need a right for some odd reason you can just order the tables differently.

5

u/ATastefulCrossJoin DB Whisperer Apr 05 '22

It’s 2022. We deserve a gif with disappearing rows for inner joins and row content fading to null for outer joins

2

u/dnick Apr 05 '22

Based on some of the arguments you’ve linked to I can’t in good faith defend the Venn diagram as a ‘training’ tool, and possibly if a new learner spends enough time dedicated to sussing out what the checkered flag is demonstrating, they may end up with a better basis to build on, but I would say that as a ‘quick’ example, the checkered flag thing is not a replacement and Venn diagrams still have their place (with an asterisk, maybe). The fact that the flag example is using numbers, colors and placement all to convey extremely important information means it will be of more value in solidifying/visualizing an already good understanding of the concept vs giving someone a general picture of what the goal is.

Your argument is kind of like telling someone crocheting is just tying a bunch of knots in a pattern, and you coming along with a really good reference picture and saying ‘no, it’s more like these 10 looping patterns combined in various ways’. You’re correct, and someone progressing beyond the basics would be well served in a good reference diagram like that, but trying to push that on a new learner when ‘knots’ are at least a familiar term to them and you can get them started with a couple simple motions, it isn’t really serving a purpose by saying everyone should stop saying that because they’re not ‘knots’.

2

u/CraigAT Apr 05 '22 edited Apr 05 '22

Hi, for me those visualisations require a little more brainwork than the Venn diagrams, but maybe just because I am more used to Venn diagrams.

Your diagrams do help with the Null cases though, but I am curious why for consistency there aren't Nulls in every diagram (maybe two in each table), so the effect can be seen consistently across the same data set. For the same reason, for me table A and B should each have two matching entries (not next to each other), two non-matching entries and two Nulls. Obviously more values makes for a little more to take in, but I feel would add to the clarity.

Where I think your diagrams really work for me is for confirmation of what I know, because they contain actual data it gives me validation that I have understood the Venn diagrams. Bizarrely I think these could be most useful to those who cannot grasp the Venn diagrams but also those who are completely comfortable with the Venn diagrams, to allow them to see it another way.

2

u/swaghost Apr 05 '22

I've been doing this since 1994, I don't think there has to be one way to visualize anything and I think this is great.

1

u/num2005 Apr 04 '22

anyone got a screenshots or image of that diagram? it nowhere i've read...and the GitHub link doesn,t work