Of the tens of thousands of queries I've written I've needed right join the exactly once. It's a feature which is neat in that it exists, but the prevalence in teaching materials is entirely unjustified. Cross joins are massively more practical and enable some efficient transformations, but are usually taught only as all to all without a clear position on why they are useful.