3 ms·
> 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 avail
by petalmind 3y ago
> 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.
- erehweb 3y agoCan you elaborate with a gotcha example?
- hobs 3y agoI assume they mean that row multiplication can occur but otherwise not sure.
- nomilk 3y agoI'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....".
- orlp 3y agoIt 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.
- simonw 3y agoCan 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?
- reaperman 3y agoThere was discussion about this exact same thing about 17 days ago. It comes up surprisingly frequently. https://news.ycombinator.com/item?id=39071550 https://news.ycombinator.com/item?id=39071550
- magicalhippo 3y agoI'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.