13 ms·

Say no to Venn diagrams when explaining SQL joins (2016)

262 points by alecco 8y ago

mabbo 8y ago
I remember being a third year CS student, studying for some database exam coming up, and a friend of mine (a wickedly smart second year student) explained joins as Cartesian products to me. Took her all of 60 second to turn my entire brain over. Blew my mind. I had been using SQL but not understanding it. Mostly because of poor previous explanation via things like Venn diagrams- the wrong model lead me to think incorrectly and make mistakes. With the right model in my head, the exam was dead easy. I don't even know that she realized how impactful that was to me. So on the off chance you're reading HN, thanks Wen!
- alexis_fr 8y ago
  So in a sense she drew a Wen diagram?
- dwild 8y ago
  > Mostly because of poor previous explanation via things like Venn diagrams- the wrong model lead me to think incorrectly and make mistakes. To me the Venn diagrams made it quite clear to understands while this article diagram wouldn't have helped me at all. The best way to learn is in a way that make you understands. Your friend found a way that make you understands, but do you believe his explanation would have been universally understood? We would live in a much better world if it was that simple. For me, Venn worked to make me understands. For you, your friend explanation worked to make you understands. Should we make your friend stop explaining because he can't help everyone? Or should we simply let both way coexist?
scarface74 8y ago
I once interviewed a QA person for a very heavy data centric project. He didn’t know SQL well, but he described a left join problem perfectly by drawing a Venn diagram to explain the data that needed to be retrieved. When I was about to finish up the interview, he was so curious about the answer he insisted that I explained the answer so “he could sleep at night”. I gave him a thumbs up just because he knew the concept and displayed intellectual curiosity. It was a great choice.
gregoriol 8y ago
Thanks for the link, it is so true, well done!
laszlokorte 8y ago
I have always been wondering why Venn diagrams are used for illustrating joins...
- k__ 8y ago
  Seemed like a good idea. It certainly helped me to get inti the topic.
your-nanny 8y ago
I have always found the use of Venn Diagrams for this purpose utterely confusing, and even wrong headed. Bravo.
TravelAndFood 8y ago
Really well done, but I agree with one of the comments in there: need to add patterns to the solid colors; it would differentiate for color blind folks and in general make the relationships easier to grasp.
- daveFNbuck 8y ago
  I tried pasting the cross join and inner join into a colorblindness simulator [1] and the only issue I found is that the A and the 2 in the cross join look similar if you have monochromacy. Looking at the monochromatic version, I don't think this really affects the clarity of the cross join diagram. The inner join is already perfectly distinguishable by how dark the squares are, and anything other than monochromacy preserves the distinctions that the color adds in the cross join, unless the site I used is misleading. [1] https://www.color-blindness.com/coblis-color-blindness-simulator/ https://www.color-blindness.com/coblis-color-blindness-simul...
Vogtinator 8y ago
I really don't see the point here. If I turn the "correct" diagrams sideways, it looks very much like a venn diagram again.
- 0xfffafaCrash 8y ago
  Yep: https://i.imgur.com/cgYkQbS.png https://i.imgur.com/cgYkQbS.png I'm drawing the conclusion that some people just need to see arbitrary concrete values to be able to understand what's going on.
  - _underfl0w_ 8y ago
    This seems to be more accurate. The primary contribution here is the concrete values. Otherwise it's literally a square, sideways Venn Diagram. You can turn your phone sideways to confirm.
  - Thorrez 8y ago
    I see that those circles look like a Venn diagram. But for a Venn diagram to be useful in explaining something, there needs to be an easily understandable definition about what the 2 circles are. That doesn't seem to exist here. I can't come up with a logical definition for what the top circle or the bottom circle represent.
  - johnNash05 8y ago
    This image isn't opening on my end. Can you please share in another format. You can use https://www.coolutils.com/TotalImageConverter https://www.coolutils.com/TotalImageConverter
- Too 8y ago
  In a venn diagram the sum of the area combined by A and B can never be larger than A+B. In a join the result can multiply and you get up to A*B rows instead. Hence the geometrical representation can mislead you into thinking your data is smaller than what it actually is. As a quick mental model i think the diagrams can help but you have to understand they are only a reminder of how the join is being matched, not a representation of the complete data set.
0xfffafaCrash 8y ago
This is silly. They're a cheatsheet for those who know how SQL joins basically work and what they represent actually makes perfect sense if you don't misrepresent what sets the circles refer to. Two tables (A & B) are somehow being joined (in the sql sense, by some query) ON a column from each table expressing the same type of id key. Consider the union of values for those columns in either table. That is the set containing everything in either circle in the Venn diagram. Then consider the values which appear just in A or just in B -- those, respectively are the non-overlapping sides. Then consider the intersection (values which appear as values in both A & B), that is the middle part shared by both circles. The highlighting on the Venn diagram shows which sets of values for the key could be made to appear in the resulting join for each type of query (and, yes, by extension indicating whether some other non-null data could also be coming from a given table in the cases where the given value is present for the table with the ON'ed column in question) That's perfectly valid for a Venn diagram. The representation is meaningful and concise. If you don't understand the fundamental nature of what is happening in a SQL JOIN I can see why you may need a more detailed/verbose diagram for each type of join, but the diagrams don't purport to be about the sets of rows from a table in the result and anyone who grasps the basic principle behind the joins won't struggle with the Venn diagrams because they strike the nail on the head with respect to the differences between each type of join without the need for verbosity & redundancy.
- mabbo 8y ago
  Because it's the wrong model. Take two tables of 1m rows each and do a cross join on them. The Venn diagram model says you would have at most 2m output rows. But that's wrong. You would have 1t rows output. When when you're using a normal join on a specific value matching, if the columns are unsorted, that's 1t comparisons potentially. The right mental model leads to the right intuition of what's going under the surface. And it's all well and good to say "If you don't understand the fundamental nature of what is happening in a SQL JOIN..." But that is exactly who these diagrams are intended for! People who don't understand yet and are trying to. Teaching people with the right model means they don't have to unlearn the wrong model later when questions get more complicated.
  - 8y ago
40four 8y ago
I love this premise! I have always hated the venn diagram approach. When I started programming, I never found them useful. I agree it is the wrong model to teach what is happening with joins. It think it produces more confusion. The author's approach is a better visual model. Using boxes makes so much more sense, since we are dealing with rows of data. The circles of a venn diagram don't represent a result set visually, and i think that's why I always found it confusing. The the main point I think should be expressed to really explain what's happening in a join is about null values. If one table has nulls in the matching set, which ones do we want to keep, or discard? The box approach shows this beautifully, while a venn approach doesn't really make this obvious. The best visual representation I've ever seen is similar to the author's, but animated. https://news.ycombinator.com/item?id=16983007 https://news.ycombinator.com/item?id=16983007
burnstek 8y ago
What a silly little article. Venn diagrams are an extremely illustrative and useful way to visualize joins.
obeattie 8y ago
Honestly, if it were not for Venn diagrams, I may have never learned how SQL joins work. It might not be perfect, but if you'd tried to make 16-year-old me understand relational algebra in order to grok how the joins in his Django site were working, he'd have given up before he began. That's not to say that other ways of illustrating things don't have merit, but broad statements like "say NO to Venn diagrams" just aren't helpful. If people find Venn diagrams a useful tool (and their proliferation indicates they do), I don't see the harm. They didn't stop me going deeper on stuff like relational algebra when I was ready to.
- Boxxed 8y ago
  Maybe you didn't read the article? He presents a pretty nice visual alternative to Venn diagrams.
  - obeattie 8y ago
    I did read the article and I don't find what the author thinks is a "nice illustration of a cartesian product" easy to grok at all. Similarly, I think I would have found the the diagrams further down hard to understand versus a Venn diagram. But that wasn't my point; if other people find these illustrations helpful, that's awesome and I wouldn't want to stop someone inventing something new and useful. My point was that I violently disagree with one-size-fits-all proclamations like "Say NO to Venn Diagrams."
  - zzbzq 8y ago
    The hardest part of joins is remembering which is which. Venn diagrams are used specifically because they solve that problem with the inner/outer/left/right terminology. His visualizations ultimately help nothing. You have to stare at them for a while to see what the pattern is, but there's absolutely no mnemonic hook to remember what's what, so in the end all you learn from his visualizations are that there exist different ways to slice and dice the products of sets, which is already a given if you're trying to figure out what the differences are.
    - 40four 8y ago
nkozyra 8y ago
I think being overly concerned with an accurate representation of a concept is less important than conveying information. A Venn diagram may not be a perfect analog for joins but they relay the concept quite well.
- okl 8y ago
  I don't think that is true. If you base your understanding on an inaccurate representation, then you may later on run into problems trying to fit new information into your inaccurate thought-model. For example explaining electric circuits with water analogies (like described here: https://learn.sparkfun.com/tutorials/voltage-current-resistance-and-ohms-law/voltage https://learn.sparkfun.com/tutorials/voltage-current-resista...) cannot account for EMF.
  - paulgb 8y ago
    Every model or analogy has this problem, but it doesn't make models and analogies useless for learning.
    - nkozyra 8y ago
      Exactly. Abstractions are lossy. The goal here is comprehension, I don't see how the models here differ in that regard.
quietbritishjim 8y ago
Here's the key point I think the article misses: Very nearly always (I mean really really nearly always), when you do a left/right/inner join, the condition you use for the join is a unique (usally primary) key on one side and a foreign key on the other side. So although in theory you start with the full cartesian join and then filter some of the results out (picking out of n×m entries), in practice you are only left with at most max(n,m) entries. So something like a Venn diagram is much less wrong than it is for the outer join (but still a bit wrong). What I would like to see is inner/left/right joins explained with colours used to indicate value (as the article already does) but in the form of entries in a PivotTable (as the article used only for outer join), perhaps with faded entries for those that were filtered out. Most of all, it would be great to see this with non-unique values in both tables, as rare as this admittedly is. This could be followed up by the more usual primary key / foreign key combination. At that point it would be visually clear that only max(n,m) entries are left, so you could finish up by showing those evil Venn diagrams after all, with the disclaimer that you understand that there's a full n×m combination under the hood in theory.
zoomablemind 8y ago
Many ways to skin a cat... SQL used to have an intriguing aura of exotic knowledge back in time when SQL was still a Language to highlight on a resume and to have an RDBMS server meant spending thousands. So Venn diagrams were just adding to such aura. But it's a visual aid and is usually supplemented with examples. If it doesn't ring a bell, some hands-on or detailed explanation helps crack it. Personally, I believe JOIN should first be touched upon when introducing normalization. It just makes sense: break tables apart then join them together. Here one could also grasp a utility of an index on join column. Then progress to more advanced use of joins - outer join (needs a concept of NULL), union (basically a synthetic set) etc. Finally, a cross join - as one of those cases that may lock your terminal if you don't know what you're doing. Of course learning Cartesian product concept here, if not yet. As anything in Computer, the hands-on aspect usually clears thing much better then science-first. The science would ease in much better once the utility is grasped. Teaching means helping someone to learn, not making them understand your way of teaching. So different aids may work for different audiences.
coldtea 8y ago
>Honestly, if it were not for Venn diagrams, I may have never learned how SQL joins work. It might not be perfect, but if you'd tried to make 16-year-old me understand relational algebra in order to grok how the joins in his Django site were working, he'd have given up before he began. The whole point of the article is that there are different visualizations that are much better than Venn diagrams (in fact Venn diagrams are totally misleading), and they don't require any relational algebra. That said, if someone doesn't know what a cartesian product is (high school level math), perhaps they shouldn't be allowed near a database...
- deleted 8y ago
  [deleted]
- obeattie 8y ago
  >if someone doesn't know what a cartesian product is (high school level math), perhaps they shouldn't be allowed near a database... This is such an intellectually snobbish position to take. I can tell you that I didn’t know this term when I first interacted with a database, and if someone had told me the above, I would have just felt stupid and given up. The fact you learned what a Cartesian product was at high school shouldn’t preclude someone who hasn’t from trying to build stuff and experiment with tech. Not everyone learns things the same way, or in the same order, and that’s okay.
  - coldtea 8y ago
    >This is such an intellectually snobbish position to take. Intellectually snobbish is "everyone should listen to Mahler and read Epictetus in the original language". This is just basic professional requirement... >The fact you learned what a Cartesian product was at high school shouldn’t preclude someone who hasn’t from trying to build stuff and experiment with tech. Just not in any capacity where people depend on their output...
    - cortesoft 8y ago
      You didn't say "should not be allowed near a production database" you said "database" That is very different. This was a 16 year old kid trying to mess around and learn stuff, he should be able to be around databases all he wants. I wouldn't hire him as a DBA, but he should be able to learn without having to wait for more advanced math.
iiioooppp 8y ago
Why does SQL have both WHERE and ON, plus various kinds of JOINs? It seems to me that it would be simpler to have just have one JOIN which represents the Cartesian product, plus WHERE for filtering - and then the DB engine could apply optimizations so that queries would actually run as fast as in existing SQL. Am I missing something?
- rzzzt 8y ago
  Cartesian product could be the default behavior if you select from multiple tables. I'm not sure what the standard says about this one.
  - CuriousSkeptic 8y ago
    It is. On the topic as such, besides ON and WHERE there is also USING and NATURAL. Som very influential db-people have argued that NATURAL should be the only supported join. Checkout the third manifesto. ON as such allows for outer joins, something you can’t do starting with a Cartesian product. But Oracle has syntax for outer joins using WHERE if you prefer that.
    - rzzzt 8y ago
      Aah, very good! Outer joins could be achieved with NVL and a suitable default value, if you don't want to do the (+) thing? Edit: you don't get to pair all rows from one table with an all-NULL "fictional" row from the other table in a cartesian product, so the answer is no
- barrkel 8y ago
  The bit your missing is the awkwardness of expressing outer joins. The way Oracle does it is an existence proof of its awkwardness. The 'FROM' clause defaults to Cartesian product for multiple tables. You can then specify your join conditions in the where clause, but it makes outer joins harder to write - Oracle does them with special (+) operator, but it's clumsy. It doesn't (shouldn't) make much difference at all to the database whether you specify your join condition after the `ON` or in the `WHERE` - if it doesn't hoist `WHERE` conditions into the join clause, and potentially change the join order because of it, it's missing big optimization opportunities.
Groxx 8y ago
Since by far the most errors I see in SQL queries are places where Venn diagrams do not accurately describe SQL behavior... yeah, I'm gonna have to agree, though I'm not sold on the alternative here. Like 90%+ of the flaws I run across in queries are due to multiple matching rows on the right side, without `distinct` or an aggregation of some kind. 1:1 matches on rows are trivial. Venn diagrams can describe these too. But you really can't let people develop an intuition based on that, early on, or they kinda get stuck and it takes a while to unravel.
tarabanga 8y ago
Either the author is not proposing an alternative, or the alternative is formulae and poorly formatted screenshots, or my ublock origin settings are blocking the perfect method to explain SQL joins in 100x100 pixels.
haroldl 8y ago
“A JOIN is really a cartesian product (also cross product) with a filter.” It always boils down to this for me.
roywiggins 8y ago
This makes me glad I learned SQL first with relational algebra. Probably that wouldn't work for a lot of people, but as a math major it made the basic concepts pretty clear.
xivzgrev 8y ago
Lol I used Venn diagrams to explain joins just a week or two ago. It does the trick but doesn’t account for many to one joins
jmmcd 8y ago
A lot of people are saying hang on, they're useful for explaining. But my experience when teaching relational databases is that they give the illusion of understanding. I'd rather have the students know that they do not know (and work harder) than look at a Venn diagram, think they get it, and move on.

262 points by alecco 8y ago

I remember being a third year CS student, studying for some database exam coming up, and a friend of mine (a wickedly smart second year student) explained joins as Cartesian products to me. Took her all of 60 second to turn my entire brain over. Blew my mind. I had been using SQL but not understanding it. Mostly because of poor previous explanation via things like Venn diagrams- the wrong model lead me to think incorrectly and make mistakes. With the right model in my head, the exam was dead easy. I don't even know that she realized how impactful that was to me. So on the off chance you're reading HN, thanks Wen!

alexis_fr 8y ago
So in a sense she drew a Wen diagram?
dwild 8y ago
> Mostly because of poor previous explanation via things like Venn diagrams- the wrong model lead me to think incorrectly and make mistakes. To me the Venn diagrams made it quite clear to understands while this article diagram wouldn't have helped me at all. The best way to learn is in a way that make you understands. Your friend found a way that make you understands, but do you believe his explanation would have been universally understood? We would live in a much better world if it was that simple. For me, Venn worked to make me understands. For you, your friend explanation worked to make you understands. Should we make your friend stop explaining because he can't help everyone? Or should we simply let both way coexist?

scarface74 8y ago

I once interviewed a QA person for a very heavy data centric project. He didn’t know SQL well, but he described a left join problem perfectly by drawing a Venn diagram to explain the data that needed to be retrieved. When I was about to finish up the interview, he was so curious about the answer he insisted that I explained the answer so “he could sleep at night”. I gave him a thumbs up just because he knew the concept and displayed intellectual curiosity. It was a great choice.

gregoriol 8y ago

Thanks for the link, it is so true, well done!

laszlokorte 8y ago

I have always been wondering why Venn diagrams are used for illustrating joins...

k__ 8y ago
Seemed like a good idea. It certainly helped me to get inti the topic.

your-nanny 8y ago

I have always found the use of Venn Diagrams for this purpose utterely confusing, and even wrong headed. Bravo.

TravelAndFood 8y ago

Really well done, but I agree with one of the comments in there: need to add patterns to the solid colors; it would differentiate for color blind folks and in general make the relationships easier to grasp.

daveFNbuck 8y ago
I tried pasting the cross join and inner join into a colorblindness simulator [1] and the only issue I found is that the A and the 2 in the cross join look similar if you have monochromacy. Looking at the monochromatic version, I don't think this really affects the clarity of the cross join diagram. The inner join is already perfectly distinguishable by how dark the squares are, and anything other than monochromacy preserves the distinctions that the color adds in the cross join, unless the site I used is misleading. [1] https://www.color-blindness.com/coblis-color-blindness-simulator/ https://www.color-blindness.com/coblis-color-blindness-simul...

Vogtinator 8y ago

I really don't see the point here. If I turn the "correct" diagrams sideways, it looks very much like a venn diagram again.

0xfffafaCrash 8y ago
Yep: https://i.imgur.com/cgYkQbS.png https://i.imgur.com/cgYkQbS.png I'm drawing the conclusion that some people just need to see arbitrary concrete values to be able to understand what's going on.
- _underfl0w_ 8y ago
  This seems to be more accurate. The primary contribution here is the concrete values. Otherwise it's literally a square, sideways Venn Diagram. You can turn your phone sideways to confirm.
- Thorrez 8y ago
  I see that those circles look like a Venn diagram. But for a Venn diagram to be useful in explaining something, there needs to be an easily understandable definition about what the 2 circles are. That doesn't seem to exist here. I can't come up with a logical definition for what the top circle or the bottom circle represent.
- johnNash05 8y ago
  This image isn't opening on my end. Can you please share in another format. You can use https://www.coolutils.com/TotalImageConverter https://www.coolutils.com/TotalImageConverter
Too 8y ago
In a venn diagram the sum of the area combined by A and B can never be larger than A+B. In a join the result can multiply and you get up to A*B rows instead. Hence the geometrical representation can mislead you into thinking your data is smaller than what it actually is. As a quick mental model i think the diagrams can help but you have to understand they are only a reminder of how the join is being matched, not a representation of the complete data set.

0xfffafaCrash 8y ago

This is silly. They're a cheatsheet for those who know how SQL joins basically work and what they represent actually makes perfect sense if you don't misrepresent what sets the circles refer to. Two tables (A & B) are somehow being joined (in the sql sense, by some query) ON a column from each table expressing the same type of id key. Consider the union of values for those columns in either table. That is the set containing everything in either circle in the Venn diagram. Then consider the values which appear just in A or just in B -- those, respectively are the non-overlapping sides. Then consider the intersection (values which appear as values in both A & B), that is the middle part shared by both circles. The highlighting on the Venn diagram shows which sets of values for the key could be made to appear in the resulting join for each type of query (and, yes, by extension indicating whether some other non-null data could also be coming from a given table in the cases where the given value is present for the table with the ON'ed column in question) That's perfectly valid for a Venn diagram. The representation is meaningful and concise. If you don't understand the fundamental nature of what is happening in a SQL JOIN I can see why you may need a more detailed/verbose diagram for each type of join, but the diagrams don't purport to be about the sets of rows from a table in the result and anyone who grasps the basic principle behind the joins won't struggle with the Venn diagrams because they strike the nail on the head with respect to the differences between each type of join without the need for verbosity & redundancy.

mabbo 8y ago
Because it's the wrong model. Take two tables of 1m rows each and do a cross join on them. The Venn diagram model says you would have at most 2m output rows. But that's wrong. You would have 1t rows output. When when you're using a normal join on a specific value matching, if the columns are unsorted, that's 1t comparisons potentially. The right mental model leads to the right intuition of what's going under the surface. And it's all well and good to say "If you don't understand the fundamental nature of what is happening in a SQL JOIN..." But that is exactly who these diagrams are intended for! People who don't understand yet and are trying to. Teaching people with the right model means they don't have to unlearn the wrong model later when questions get more complicated.
- 8y ago

40four 8y ago

I love this premise! I have always hated the venn diagram approach. When I started programming, I never found them useful. I agree it is the wrong model to teach what is happening with joins. It think it produces more confusion. The author's approach is a better visual model. Using boxes makes so much more sense, since we are dealing with rows of data. The circles of a venn diagram don't represent a result set visually, and i think that's why I always found it confusing. The the main point I think should be expressed to really explain what's happening in a join is about null values. If one table has nulls in the matching set, which ones do we want to keep, or discard? The box approach shows this beautifully, while a venn approach doesn't really make this obvious. The best visual representation I've ever seen is similar to the author's, but animated. https://news.ycombinator.com/item?id=16983007 https://news.ycombinator.com/item?id=16983007

burnstek 8y ago

What a silly little article. Venn diagrams are an extremely illustrative and useful way to visualize joins.

obeattie 8y ago

Honestly, if it were not for Venn diagrams, I may have never learned how SQL joins work. It might not be perfect, but if you'd tried to make 16-year-old me understand relational algebra in order to grok how the joins in his Django site were working, he'd have given up before he began. That's not to say that other ways of illustrating things don't have merit, but broad statements like "say NO to Venn diagrams" just aren't helpful. If people find Venn diagrams a useful tool (and their proliferation indicates they do), I don't see the harm. They didn't stop me going deeper on stuff like relational algebra when I was ready to.

Boxxed 8y ago
Maybe you didn't read the article? He presents a pretty nice visual alternative to Venn diagrams.
- obeattie 8y ago
  I did read the article and I don't find what the author thinks is a "nice illustration of a cartesian product" easy to grok at all. Similarly, I think I would have found the the diagrams further down hard to understand versus a Venn diagram. But that wasn't my point; if other people find these illustrations helpful, that's awesome and I wouldn't want to stop someone inventing something new and useful. My point was that I violently disagree with one-size-fits-all proclamations like "Say NO to Venn Diagrams."
- zzbzq 8y ago
  The hardest part of joins is remembering which is which. Venn diagrams are used specifically because they solve that problem with the inner/outer/left/right terminology. His visualizations ultimately help nothing. You have to stare at them for a while to see what the pattern is, but there's absolutely no mnemonic hook to remember what's what, so in the end all you learn from his visualizations are that there exist different ways to slice and dice the products of sets, which is already a given if you're trying to figure out what the differences are.
  - 40four 8y ago

nkozyra 8y ago

I think being overly concerned with an accurate representation of a concept is less important than conveying information. A Venn diagram may not be a perfect analog for joins but they relay the concept quite well.

okl 8y ago
I don't think that is true. If you base your understanding on an inaccurate representation, then you may later on run into problems trying to fit new information into your inaccurate thought-model. For example explaining electric circuits with water analogies (like described here: https://learn.sparkfun.com/tutorials/voltage-current-resistance-and-ohms-law/voltage https://learn.sparkfun.com/tutorials/voltage-current-resista...) cannot account for EMF.
- paulgb 8y ago
  Every model or analogy has this problem, but it doesn't make models and analogies useless for learning.
  - nkozyra 8y ago
    Exactly. Abstractions are lossy. The goal here is comprehension, I don't see how the models here differ in that regard.

quietbritishjim 8y ago

Here's the key point I think the article misses: Very nearly always (I mean really really nearly always), when you do a left/right/inner join, the condition you use for the join is a unique (usally primary) key on one side and a foreign key on the other side. So although in theory you start with the full cartesian join and then filter some of the results out (picking out of n×m entries), in practice you are only left with at most max(n,m) entries. So something like a Venn diagram is much less wrong than it is for the outer join (but still a bit wrong). What I would like to see is inner/left/right joins explained with colours used to indicate value (as the article already does) but in the form of entries in a PivotTable (as the article used only for outer join), perhaps with faded entries for those that were filtered out. Most of all, it would be great to see this with non-unique values in both tables, as rare as this admittedly is. This could be followed up by the more usual primary key / foreign key combination. At that point it would be visually clear that only max(n,m) entries are left, so you could finish up by showing those evil Venn diagrams after all, with the disclaimer that you understand that there's a full n×m combination under the hood in theory.

zoomablemind 8y ago

Many ways to skin a cat... SQL used to have an intriguing aura of exotic knowledge back in time when SQL was still a Language to highlight on a resume and to have an RDBMS server meant spending thousands. So Venn diagrams were just adding to such aura. But it's a visual aid and is usually supplemented with examples. If it doesn't ring a bell, some hands-on or detailed explanation helps crack it. Personally, I believe JOIN should first be touched upon when introducing normalization. It just makes sense: break tables apart then join them together. Here one could also grasp a utility of an index on join column. Then progress to more advanced use of joins - outer join (needs a concept of NULL), union (basically a synthetic set) etc. Finally, a cross join - as one of those cases that may lock your terminal if you don't know what you're doing. Of course learning Cartesian product concept here, if not yet. As anything in Computer, the hands-on aspect usually clears thing much better then science-first. The science would ease in much better once the utility is grasped. Teaching means helping someone to learn, not making them understand your way of teaching. So different aids may work for different audiences.

coldtea 8y ago

>Honestly, if it were not for Venn diagrams, I may have never learned how SQL joins work. It might not be perfect, but if you'd tried to make 16-year-old me understand relational algebra in order to grok how the joins in his Django site were working, he'd have given up before he began. The whole point of the article is that there are different visualizations that are much better than Venn diagrams (in fact Venn diagrams are totally misleading), and they don't require any relational algebra. That said, if someone doesn't know what a cartesian product is (high school level math), perhaps they shouldn't be allowed near a database...

deleted 8y ago
[deleted]
obeattie 8y ago
>if someone doesn't know what a cartesian product is (high school level math), perhaps they shouldn't be allowed near a database... This is such an intellectually snobbish position to take. I can tell you that I didn’t know this term when I first interacted with a database, and if someone had told me the above, I would have just felt stupid and given up. The fact you learned what a Cartesian product was at high school shouldn’t preclude someone who hasn’t from trying to build stuff and experiment with tech. Not everyone learns things the same way, or in the same order, and that’s okay.
- coldtea 8y ago
  >This is such an intellectually snobbish position to take. Intellectually snobbish is "everyone should listen to Mahler and read Epictetus in the original language". This is just basic professional requirement... >The fact you learned what a Cartesian product was at high school shouldn’t preclude someone who hasn’t from trying to build stuff and experiment with tech. Just not in any capacity where people depend on their output...
  - cortesoft 8y ago
    You didn't say "should not be allowed near a production database" you said "database" That is very different. This was a 16 year old kid trying to mess around and learn stuff, he should be able to be around databases all he wants. I wouldn't hire him as a DBA, but he should be able to learn without having to wait for more advanced math.

iiioooppp 8y ago

Why does SQL have both WHERE and ON, plus various kinds of JOINs? It seems to me that it would be simpler to have just have one JOIN which represents the Cartesian product, plus WHERE for filtering - and then the DB engine could apply optimizations so that queries would actually run as fast as in existing SQL. Am I missing something?

rzzzt 8y ago
Cartesian product could be the default behavior if you select from multiple tables. I'm not sure what the standard says about this one.
- CuriousSkeptic 8y ago
  It is. On the topic as such, besides ON and WHERE there is also USING and NATURAL. Som very influential db-people have argued that NATURAL should be the only supported join. Checkout the third manifesto. ON as such allows for outer joins, something you can’t do starting with a Cartesian product. But Oracle has syntax for outer joins using WHERE if you prefer that.
  - rzzzt 8y ago
    Aah, very good! Outer joins could be achieved with NVL and a suitable default value, if you don't want to do the (+) thing? Edit: you don't get to pair all rows from one table with an all-NULL "fictional" row from the other table in a cartesian product, so the answer is no
barrkel 8y ago
The bit your missing is the awkwardness of expressing outer joins. The way Oracle does it is an existence proof of its awkwardness. The 'FROM' clause defaults to Cartesian product for multiple tables. You can then specify your join conditions in the where clause, but it makes outer joins harder to write - Oracle does them with special (+) operator, but it's clumsy. It doesn't (shouldn't) make much difference at all to the database whether you specify your join condition after the `ON` or in the `WHERE` - if it doesn't hoist `WHERE` conditions into the join clause, and potentially change the join order because of it, it's missing big optimization opportunities.

Groxx 8y ago

Since by far the most errors I see in SQL queries are places where Venn diagrams do not accurately describe SQL behavior... yeah, I'm gonna have to agree, though I'm not sold on the alternative here. Like 90%+ of the flaws I run across in queries are due to multiple matching rows on the right side, without `distinct` or an aggregation of some kind. 1:1 matches on rows are trivial. Venn diagrams can describe these too. But you really can't let people develop an intuition based on that, early on, or they kinda get stuck and it takes a while to unravel.

tarabanga 8y ago

Either the author is not proposing an alternative, or the alternative is formulae and poorly formatted screenshots, or my ublock origin settings are blocking the perfect method to explain SQL joins in 100x100 pixels.

haroldl 8y ago

“A JOIN is really a cartesian product (also cross product) with a filter.” It always boils down to this for me.

roywiggins 8y ago

This makes me glad I learned SQL first with relational algebra. Probably that wouldn't work for a lot of people, but as a math major it made the basic concepts pretty clear.

xivzgrev 8y ago

Lol I used Venn diagrams to explain joins just a week or two ago. It does the trick but doesn’t account for many to one joins

jmmcd 8y ago

A lot of people are saying hang on, they're useful for explaining. But my experience when teaching relational databases is that they give the illusion of understanding. I'd rather have the students know that they do not know (and work harder) than look at a Venn diagram, think they get it, and move on.