3 ms·

This seems like a terrible explanation. Firstly, joins are substantially orthogonal to keys. You can join two relations with no keys at all. Keys might help yo

by twic 5y ago

This seems like a terrible explanation. Firstly, joins are substantially orthogonal to keys. You can join two relations with no keys at all. Keys might help you make sure that there are going to be results to your join, but they are not required, and i wouldn't start with them. Secondly, union is not a join. If mentioned at all, it should be in an addendum about other ways to combine tables. Thirdly, left and right joins are specific kinds of outer join. Surely it's much clearer to describe "left, right, and full outer joins" than "left, right, and outer joins". Fourthly, surely the most logical way to explain joins is the progressive refinement cross -> inner -> equi -> natural -> full outer -> left/right outer. A cross join is a Cartesian product, simple to explain. Restrict that with a predicate and you have an inner join (i'd use an example where the predicate is not equality). Introduce ON as a synonym for WHERE in inner joins. Make the predicate equality on a set of the columns and it's an equijoin. Introduce USING as a shorthand for ON in equijoins. Make that set all the corresponding columns and it's a natural join. Now include all the rows that didn't match and it's a full outer join. Note that WHERE wouldn't work here. Now just the rows on one side or the other and it's a left/right outer join.

gopalv 5y ago
> A cross join is a Cartesian product I think that's the most important detail that can't be explained visually like this. Cartesians explode the row-counts - 10 rows x 10 rows is 100, not 10 or less than (which is the assumption for most people as they write joins with a primary key on at least one side). If you have a cartesian and add on the additional entry needed for the null in outers, you can explain the output of the join as a filter over that set (full outers make more sense in that model). In fact, that's a good way to think of when you see WHERE used to produce an inner join (cartesian + filter == inner). Sometime ago, I had to optimize pure cartesian joins which had a user-defined function like ON overlaps(time_range1, time_range2) and this involved a deep-dive[1] into distributing a cartesian join to parallelize the filtering. [1] - http://people.apache.org/~gopalv/xldb18/tez-xprod.pdf http://people.apache.org/~gopalv/xldb18/tez-xprod.pdf
paulddraper 5y ago
1. They are also missing antijoins. There's not a dedicated SQL keyword for it, but I think it's worth mentioning (especially if you're going to talk about union/intersect/except). 2. Actually -- and I suppose I can't blame the article -- but from title I was expecting to see HashJoin, MergeJoin, NestedLoopJoin, etc. I.e. join implementations not specifications.

by twic 5y ago

gopalv 5y ago

> A cross join is a Cartesian product I think that's the most important detail that can't be explained visually like this. Cartesians explode the row-counts - 10 rows x 10 rows is 100, not 10 or less than (which is the assumption for most people as they write joins with a primary key on at least one side). If you have a cartesian and add on the additional entry needed for the null in outers, you can explain the output of the join as a filter over that set (full outers make more sense in that model). In fact, that's a good way to think of when you see WHERE used to produce an inner join (cartesian + filter == inner). Sometime ago, I had to optimize pure cartesian joins which had a user-defined function like ON overlaps(time_range1, time_range2) and this involved a deep-dive[1] into distributing a cartesian join to parallelize the filtering. [1] - http://people.apache.org/~gopalv/xldb18/tez-xprod.pdf http://people.apache.org/~gopalv/xldb18/tez-xprod.pdf

paulddraper 5y ago

1. They are also missing antijoins. There's not a dedicated SQL keyword for it, but I think it's worth mentioning (especially if you're going to talk about union/intersect/except). 2. Actually -- and I suppose I can't blame the article -- but from title I was expecting to see HashJoin, MergeJoin, NestedLoopJoin, etc. I.e. join implementations not specifications.