3 ms·

That's a great article - however my issue with Atwood's explanation is in the classic use of overlapping circles to describe joins. After ~2 decades of using S

That's a great article - however my issue with Atwood's explanation is in the classic use of overlapping circles to describe joins. After ~2 decades of using SQL in some capacity, the circles metaphor still makes no fucking sense to me. Dataschool's article takes a somewhat non-orthodox approach with coloring rows / columns that would be included in the results of the joins - which I appreciate a lot more.

laszlokorte 5y ago
Yeah the overlapping circles make absolutely no sense because typically they are used to visualize operations on sets of the same kind resulting in a third set of also the same kind. But when joining two tables the two base sets are of different type (containing tuples/rows of different length/element type) and the resulting set containes neither tuples from the one nor from the other original set but totally new concatinated tuples combined from the base sets. It makes absolutely no sense and everytime I see an article explaining joins I just check if it contains those venn-diagrams and if so, just skip it.
- e12e 5y ago
  Would the set analogy hold if the "parts" where first all "promoted" to the ssame type via cross join, then filtered down, and finally joined? (I'm not sure if this would make sense in general for sql, but it just occurred to me in terms of the type/category of sets involved in a join.. )
- stonemetal12 5y ago
  If you view the tables in the join as sets, then the join is the cartesian product of the sets. The venn-digrams work on that cartesian product. The circle labeled Table A is everything in the cartesian product that has data from table A, same for the table B circle. The over lap of the two circles is the part of the cartesian product that contains elements from both tables.
  - laszlokorte 5y ago
    Still does not make sense to me: OrigTableA = {(ruby, 5), (rust,7), (php, 3)} OrigTableB = {(red, 5), (black, 7), (purple, 3)} OrigTableA \cap OrigTableB would be empty, makes no sense OrigTableA \cup OrigTableB would conain all 6 elements, would be a SQL UNION, makes no sense for join OrigTableA \times OrigTableB would be the the cross product: { ((ruby, 5), (red, 5)), ((ruby, 5), (black, 7)), ((ruby, 5), (purple, 3)), ((rust,7), (red, 5)), ((rust,7), (black, 7)), ((rust,7), (purple, 3)), ((php, 3), (red, 5)), ((php, 3), (black, 7)), ((php, 3), (purple, 3)) } Yes the cross product is what happens on a cross join (with the nested tuples being flattened) but now you have a single large set of all combinations and the filtering of only matching tuples (eg where the second element each tuple-pair matches) is simply not a binary operation on two sets but a unary operation on the single cross product set. Or if you tink of it as a binary pairing operation on the two original sets it is neither a set-union nor a set-intersection.
    - stonemetal12 5y ago
      We start with the cross product set, and apply the join on criteria as a subset operation to create the "background" of the venn diagram. We can then define a set "Table A" as the subset of the cross product where (as you have written it) the first element is not null. Same for Table B and the second element. Then joins are set-union and set-intersection between these two sets. I would agree that most people just flip up the diagrams without explaining what they are actually working with.

laszlokorte 5y ago

Yeah the overlapping circles make absolutely no sense because typically they are used to visualize operations on sets of the same kind resulting in a third set of also the same kind. But when joining two tables the two base sets are of different type (containing tuples/rows of different length/element type) and the resulting set containes neither tuples from the one nor from the other original set but totally new concatinated tuples combined from the base sets. It makes absolutely no sense and everytime I see an article explaining joins I just check if it contains those venn-diagrams and if so, just skip it.

e12e 5y ago
Would the set analogy hold if the "parts" where first all "promoted" to the ssame type via cross join, then filtered down, and finally joined? (I'm not sure if this would make sense in general for sql, but it just occurred to me in terms of the type/category of sets involved in a join.. )
stonemetal12 5y ago
If you view the tables in the join as sets, then the join is the cartesian product of the sets. The venn-digrams work on that cartesian product. The circle labeled Table A is everything in the cartesian product that has data from table A, same for the table B circle. The over lap of the two circles is the part of the cartesian product that contains elements from both tables.
- laszlokorte 5y ago
  Still does not make sense to me: OrigTableA = {(ruby, 5), (rust,7), (php, 3)} OrigTableB = {(red, 5), (black, 7), (purple, 3)} OrigTableA \cap OrigTableB would be empty, makes no sense OrigTableA \cup OrigTableB would conain all 6 elements, would be a SQL UNION, makes no sense for join OrigTableA \times OrigTableB would be the the cross product: { ((ruby, 5), (red, 5)), ((ruby, 5), (black, 7)), ((ruby, 5), (purple, 3)), ((rust,7), (red, 5)), ((rust,7), (black, 7)), ((rust,7), (purple, 3)), ((php, 3), (red, 5)), ((php, 3), (black, 7)), ((php, 3), (purple, 3)) } Yes the cross product is what happens on a cross join (with the nested tuples being flattened) but now you have a single large set of all combinations and the filtering of only matching tuples (eg where the second element each tuple-pair matches) is simply not a binary operation on two sets but a unary operation on the single cross product set. Or if you tink of it as a binary pairing operation on the two original sets it is neither a set-union nor a set-intersection.
  - stonemetal12 5y ago
    We start with the cross product set, and apply the join on criteria as a subset operation to create the "background" of the venn diagram. We can then define a set "Table A" as the subset of the cross product where (as you have written it) the first element is not null. Same for Table B and the second element. Then joins are set-union and set-intersection between these two sets. I would agree that most people just flip up the diagrams without explaining what they are actually working with.