4 ms·

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 t

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.
daveFNbuck 8y ago
For 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 ago
Diagrams 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 ago
  The 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.
  - 0xfffafaCrash 8y ago
    This was incorrect:”The Venn diagram depicts a left-join as a set difference, which it clearly is not.” It is in fact a set difference and the sets in question are values for the column being ONed on in each table (as I explained in the top level comment) My reply was with respect to these being cross joins with filters. That’s just one way of thinking about a JOIN that actually adds notational complexity (and does not actually represent how your database would be doing the join because it would be unnecessarily expensive computationally). Thinking of it in terms of a set difference of the values in thr ON column is arguably the better way of thinking of it if you are interested in how such a join should be implemented. Alternatively you can interpret the venn diagrams as just the filtration if you prefer interpreting as a cross join with filters.
    - ryanmonroe 8y ago
      f you have one set with 5 elements and another with 10, the set difference cannot have 13 elements. ------- In anticipation of some possible technical replies, let me be more explicit: It isn't a set difference, no matter how you view it. Viewpoint 1: The original lists of objects are not actually sets because the elements are not distinct. From this viewpoint, it isn't a set difference because they aren't even sets to begin with. Viewpoint 2: There is an implicit "row number" feature of each object so in that sense the objects are distinct and do form sets. From this viewpoint, it still isn't a set difference. If you have one set with 5 elements and another with 10, the set difference cannot have 13 elements.
yyrrll 8y ago
Yes. And, having taught SQL to Excel power users, I can tell you it is a very effective learning model. The "filtered cross-join" model allows students who have learned SELECT and WHERE to think of a JOIN as an extension of those primitives, a combination of two tables on which they can filter. Venn diagrams might be useful to visualize an outcome, but they will not support stepping through primitives to a solution. In "filtered cross-join", JOIN can be an extension of SELECT that combines two tables. The combination is a set of rows, each each of which combines all the fields of one row of one table with all the fields of one row of the second. This is easily visualized with two two-column tables of three rows. They can then use WHERE to find those rows with matches on the key field. With this model, students can build up JOIN as an abstraction of simpler primitives. When they are struggling with a problem, you can ask them to first step through those primitives to accumulate the solution. Say, query the "raw" join and examine a few rows to see which they want returned. What is true of those and not true of the ones you do not want? ("I want those where these two fields match, and none that do not.") Ok, how do you express that condition in SQL? ("Where . . . this equals that?" "Hmm, try that" "HEY THAT WORKED!") With that basis, they have a model that can extend to more complexity -- joins across several tables, joins on the same table, joins with conditions other than field equality. Building up to and using this model, you can have the vast majority of students writing joins with confidence in two days. My suggestion to the site would be, use an example that has two columns on each table, to provide the key field on which the join will be performed.

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.

daveFNbuck 8y ago

For 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 ago

Diagrams 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 ago
The 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.
- 0xfffafaCrash 8y ago
  This was incorrect:”The Venn diagram depicts a left-join as a set difference, which it clearly is not.” It is in fact a set difference and the sets in question are values for the column being ONed on in each table (as I explained in the top level comment) My reply was with respect to these being cross joins with filters. That’s just one way of thinking about a JOIN that actually adds notational complexity (and does not actually represent how your database would be doing the join because it would be unnecessarily expensive computationally). Thinking of it in terms of a set difference of the values in thr ON column is arguably the better way of thinking of it if you are interested in how such a join should be implemented. Alternatively you can interpret the venn diagrams as just the filtration if you prefer interpreting as a cross join with filters.
  - ryanmonroe 8y ago
    f you have one set with 5 elements and another with 10, the set difference cannot have 13 elements. ------- In anticipation of some possible technical replies, let me be more explicit: It isn't a set difference, no matter how you view it. Viewpoint 1: The original lists of objects are not actually sets because the elements are not distinct. From this viewpoint, it isn't a set difference because they aren't even sets to begin with. Viewpoint 2: There is an implicit "row number" feature of each object so in that sense the objects are distinct and do form sets. From this viewpoint, it still isn't a set difference. If you have one set with 5 elements and another with 10, the set difference cannot have 13 elements.

yyrrll 8y ago

Yes. And, having taught SQL to Excel power users, I can tell you it is a very effective learning model. The "filtered cross-join" model allows students who have learned SELECT and WHERE to think of a JOIN as an extension of those primitives, a combination of two tables on which they can filter. Venn diagrams might be useful to visualize an outcome, but they will not support stepping through primitives to a solution. In "filtered cross-join", JOIN can be an extension of SELECT that combines two tables. The combination is a set of rows, each each of which combines all the fields of one row of one table with all the fields of one row of the second. This is easily visualized with two two-column tables of three rows. They can then use WHERE to find those rows with matches on the key field. With this model, students can build up JOIN as an abstraction of simpler primitives. When they are struggling with a problem, you can ask them to first step through those primitives to accumulate the solution. Say, query the "raw" join and examine a few rows to see which they want returned. What is true of those and not true of the ones you do not want? ("I want those where these two fields match, and none that do not.") Ok, how do you express that condition in SQL? ("Where . . . this equals that?" "Hmm, try that" "HEY THAT WORKED!") With that basis, they have a model that can extend to more complexity -- joins across several tables, joins on the same table, joins with conditions other than field equality. Building up to and using this model, you can have the vast majority of students writing joins with confidence in two days. My suggestion to the site would be, use an example that has two columns on each table, to provide the key field on which the join will be performed.