What do RIGHT JOIN and FULL OUTER JOIN do, and which engines support them?
A right join is the mirror image of a left join, and a full outer join keeps the rows that find no partner on either side.
So what do they do exactly? A right join keeps every row of the table on the right, and puts missing values in the columns of the table on the left for the rows that found no partner. A full outer join keeps every row of both tables.
So which engines support them? Almost all of them. MySQL, PostgreSQL, SQLite and SQL Server all have both. The one that is missing the right join is Oracle, and it got it in version 23. Every engine has had the full outer join for a long time.
There is one practical point about the right join, and it is that most style guides tell you not to use it. You can always write it as a left join with the two tables swapped, and the left join is the one that everybody knows how to read. So the rule of thumb is to write the left join and to save the right join for the case where you are converting an old query.
So why does the full outer join matter? It is the one that answers the question "show me everything from both sides, and say which rows have no partner". That is a common question when you are comparing two lists.
The next two questions in this topic show the right join written the other way, and a trap that catches everybody.