> In the beginning tried all kinds of joins, but every one of those had some unwanted side effect.
This makes it sound more mysterious than it is. Joins does not have side-effects, they are just different operators. It comes down to if you want to include unmatched rows. Left join: Include unmatched rows from the first table. Right join: Include unmatched rows from the second table. Outer join: Include unmatched rows from both tables. Inner join: Don't include any unmatched rows.
Left and right join is of course the same, the only difference is in which order you list the tables. Left join seem more intuitive to me when writing queries, but logically they are equivalent.
This makes it sound more mysterious than it is. Joins does not have side-effects, they are just different operators. It comes down to if you want to include unmatched rows. Left join: Include unmatched rows from the first table. Right join: Include unmatched rows from the second table. Outer join: Include unmatched rows from both tables. Inner join: Don't include any unmatched rows.
Left and right join is of course the same, the only difference is in which order you list the tables. Left join seem more intuitive to me when writing queries, but logically they are equivalent.