3 ms·

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 r

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.

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.