4 ms·
> “Here we typically expect that the combined dataset will have the same number of rows as our original left side dataset.” For left join this isn’t entirely t
by jarym 3y ago
> “Here we typically expect that the combined dataset will have the same number of rows as our original left side dataset.”
For left join this isn’t entirely true. If there are more matching cases on the right joined table then you’ll get additional rows for each match. That is unless you take steps to ensure only at most one row is matched per row on the left (eg using something like DISTINCT ON in Postgres)
- wood_spirit 3y agoYes I picked that up and was tempted to comment. But then half way down it addresses this with the section “Many relationships”: > Until now we have discussed scenarios that are considered one-to-one merges. In these cases, we only expect one participant in a dataset to be joined to one instance of that same participant in the other dataset. > However, there are scenarios where this will not be the case…
- jarym 3y agoGot it, in my opinion the structure is likely to mislead people because it doesn’t make clear that it refers to ‘one-to-one’ relationships at the outset (in fact not even, it deals with the cases of one-to-one and one-to-none) - it only refers to ‘left join’.
- wood_spirit 3y agoYeap, the whole article is confusing to us who are already very familiar with eg SQL joins and things. It’s not using mainstream terminology. But I guess we are not the audience.
- HelloNurse 3y agoI agree that this article is introductory, but it makes the many non-standard and anti-standard terms (e.g. "vertical joins") less forgivable: misleading learners is worse than irritating experts.
- petalmind 3y agoThis is my pet peeve. Top google search results and ChatGPT suggest this "same number of rows" mental model which is incomplete and breaks. I wrote about this: https://minimalmodeling.substack.com/p/many-explanations-of-join-are-wrong https://minimalmodeling.substack.com/p/many-explanations-of-...
- shiandow 3y agoThe reason why this misunderstanding is so pervasive is probably because joins are effectively function application (or function composition, bit of the same thing really). I also wrote about this ;-) https://pragmathics.nl/2023/10/24/putting-the-relational-back-in-relational-databases/#functions https://pragmathics.nl/2023/10/24/putting-the-relational-bac...
- contravariant 3y agoThe hidden requirement is that you need to join on a key, in an ideal world this would be the primary key of the right table and a foreign key in the left table. Of course this requirement isn't ever enforced because the real world isn't kind enough to give strictly modelled data. It would simplify the query language a lot though.
- zX41ZdbW 3y agoClickHouse has support for this as an "ANY" JOIN modifier: https://clickhouse.com/docs/en/sql-reference/statements/select/join#supported-types-of-join https://clickhouse.com/docs/en/sql-reference/statements/sele...