5 ms·

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 misreprese

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.
- 0xfffafaCrash 8y ago
  I have yet to see a cross join represented by a Venn diagram, so this strikes me as a red herring on the topic of whether Venn diagrams for the joins that are usually represented with them make sense.
  - rileyriley 8y ago
    This was my first reaction too, but then I thought back to when I was first learning SQL and, after hours of work trying to tabulate events from users across multiple tables, discovered that I seemed to be double-counting (or more) in mysterious ways. I was using Venn diagrams to understand how joins worked and I think the "intersection" depiction of inner joins _did_ cause me to think that "duplicates would be handled," whatever that would even mean. Really it just caused me to skip over the complexity of thinking about it. If you think of inner joins as a cross join with a filter, then we have all seen cross joins represented by Venn diagrams - and for me at least I lost several hours of work restructuring my understanding of what I was working with!
  - ryanmonroe 8y ago
    The author is arguing for a mental model in which all joins are just cross joins with a filter added, and that Venn Diagrams are a bad representation because they depict set differences, which is not what SQL joins are. Say tbl_1 has 5 rows, and column "A" is the numbers 1 to 5. Then tbl_2 has the numbers 1 and 2, each repeated 5 times. Now how many rows are in the result of the query below? select tbl_1.A from tbl_1 left join tbl_2 using(A) The fact that the result has 13 rows (more rows than either of the tables!) seems like it would be pretty surprising to someone using the left-join venn diagram as a mental model. The Venn diagram depicts a left-join as a set difference, which it clearly is not.
    - nemetroid 8y ago
      For your example, the Venn diagram is a good visualization of the filter being applied. Its sets are the values that are being filtered over. I.e., the left circle contains the values {1, 2, 3, 4, 5}, and the right circle contains the values {1, 2}. No, this doesn't explain the entire join operation, but that wasn't ever the goal. The diagrams that the author of the article comes up with (with the exception of the cross join) are just Venn diagrams rotated 90 degrees.
GavinMcG 8y ago
The article says not to use Venn diagrams when explaining joins – that is, conveying the concepts to someone who doesn't already understand them. You dismiss that as "silly" because anyone who already "grasps the basic principle" could figure it out. You're talking about the use of Venn diagrams by people who already "know how SQL joins basically work". It sounds like you missed the point the article was making.
hinkley 8y ago
I know early in my career I made a lot of hay rewriting database queries that were outer joins that wanted an inner join. DISTINCT in the same statement as OUTER still gives me hives. My point is that people aren’t really getting it. Is that the fault of the pupil or the teacher? If someone wants to investigate the latter I say let them. All your surprises will be good.

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.

0xfffafaCrash 8y ago
I have yet to see a cross join represented by a Venn diagram, so this strikes me as a red herring on the topic of whether Venn diagrams for the joins that are usually represented with them make sense.
- rileyriley 8y ago
  This was my first reaction too, but then I thought back to when I was first learning SQL and, after hours of work trying to tabulate events from users across multiple tables, discovered that I seemed to be double-counting (or more) in mysterious ways. I was using Venn diagrams to understand how joins worked and I think the "intersection" depiction of inner joins _did_ cause me to think that "duplicates would be handled," whatever that would even mean. Really it just caused me to skip over the complexity of thinking about it. If you think of inner joins as a cross join with a filter, then we have all seen cross joins represented by Venn diagrams - and for me at least I lost several hours of work restructuring my understanding of what I was working with!
- ryanmonroe 8y ago
  The author is arguing for a mental model in which all joins are just cross joins with a filter added, and that Venn Diagrams are a bad representation because they depict set differences, which is not what SQL joins are. Say tbl_1 has 5 rows, and column "A" is the numbers 1 to 5. Then tbl_2 has the numbers 1 and 2, each repeated 5 times. Now how many rows are in the result of the query below? select tbl_1.A from tbl_1 left join tbl_2 using(A) The fact that the result has 13 rows (more rows than either of the tables!) seems like it would be pretty surprising to someone using the left-join venn diagram as a mental model. The Venn diagram depicts a left-join as a set difference, which it clearly is not.
  - nemetroid 8y ago
    For your example, the Venn diagram is a good visualization of the filter being applied. Its sets are the values that are being filtered over. I.e., the left circle contains the values {1, 2, 3, 4, 5}, and the right circle contains the values {1, 2}. No, this doesn't explain the entire join operation, but that wasn't ever the goal. The diagrams that the author of the article comes up with (with the exception of the cross join) are just Venn diagrams rotated 90 degrees.

GavinMcG 8y ago

The article says not to use Venn diagrams when explaining joins – that is, conveying the concepts to someone who doesn't already understand them. You dismiss that as "silly" because anyone who already "grasps the basic principle" could figure it out. You're talking about the use of Venn diagrams by people who already "know how SQL joins basically work". It sounds like you missed the point the article was making.

hinkley 8y ago

I know early in my career I made a lot of hay rewriting database queries that were outer joins that wanted an inner join. DISTINCT in the same statement as OUTER still gives me hives. My point is that people aren’t really getting it. Is that the fault of the pupil or the teacher? If someone wants to investigate the latter I say let them. All your surprises will be good.