Hacker News Reader

Comment by petalmind | Hacker News Reader

> left outer join

> A join that is guaranteed to keep all rows from the first (left) table. Columns from the right table are filled with actual values if available or with null otherwise.

This wording only works for identity equality join condition. It creates misleading mental model of left joins, and unfortunately is very common.

View on HN

I'm not sure I understand, I think this definition still works for left outer joins on conditions other than identity equality, since joins on, say, inequalities or multiple conditions would still be "guaranteed to keep all rows from the first (left) table. Columns from the right table are filled with....".

It kind of implies that the left join process works by first taking the left table and then filling in the right table with a match if one exists, and otherwise null.

That model obviously doesn't work because if there's more than one match as the matching left row is duplicated for each match. However I don't understand their point of this being a problem when you don't have a "identity equality join condition", since this can also occur for equality joins as long as you're not joining on a unique key.

Can you elaborate with a gotcha example?

I assume they mean that row multiplication can occur but otherwise not sure.

I'd say it's misleading at best. It should specify that for multiple matches in the right table, the row from the left table is duplicated per matching row in the right table.

Can you clarify? Is the problem here that the "guaranteed to keep all rows from the left table" piece is accurate, but the section about the right table might not be a good mental model if the join condition is more complex than a simple equality?

There was discussion about this exact same thing about 17 days ago. It comes up surprisingly frequently. https://news.ycombinator.com/item?id=39071550

Reply on news.ycombinator.com