3 ms·
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. B
by 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 agoI 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 agoThis 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 agoThe 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 agoFor 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.
- daveFNbuck 8y agoFor people that already have the correct mental model, the Venn diagram is a quick way to identify which name applies to each join type. The JOIN diagrams are much harder to read.
- 0xfffafaCrash 8y agoDiagrams and notation serve similar symbolic roles. Just because you can represent a real number, say 5, in a complex number's notation (5 + 0i) and you can't represent a complex number as a real number doesn't mean that you should always use the notation for complex numbers when trying to represent different real numbers. You typically want to use a concise representation that gets the message across without adding extra noise. Things can always be abstracted further into a more generalist, but more verbose representation. Complex numbers can be represented as quarternions, and so on. For the same reason, it makes little sense to use the existence of a JOIN that doesn't use ON (and is frankly not as commonly used) as a reason to complicate the representation of the 7 JOIN queries that do use ON. If translating real numbers into a notation that represents the complex numbers helps you anchor your understanding of complex numbers, great, but I wouldn't buy that our natural representation of real numbers is deficient as a result. I also don't buy that using Venn diagrams to represent JOINs that use ON are "PLAIN WRONG!" because there are things that they can't represent and aren't used to represent.
- ryanmonroe 8y agoThe fact that I don't use "on" is just syntax, it doesn't change the query. The following query is equivalent. select tbl_1.A from tbl_1 left join tbl_2 on tbl_1.A = tbl_2.A I don't think this is a special complex example. It's about as simple as you can get.
- appleflaxen 8y agothank you for this explanation / clarification. i could tell the link had an important distinction to make, but i was unable to understand it without your supplemental explanation.
- yoz-y 8y agoThe examples given into article also suffer from this problem because they only illustrate a special case when the ON column is unique. This is not the problem with the representation, just with their particular examples.