Hacker News Reader

Comment by jarym | Hacker News Reader

> “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)

View on HN

This 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-...

The 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-bac...

Yes 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…

Got 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’.

Yeap, 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.

I 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.

The 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.

ClickHouse has support for this as an "ANY" JOIN modifier: https://clickhouse.com/docs/en/sql-reference/statements/sele...

Reply on news.ycombinator.com