When to Use Right Outer Joins in SQL

Never.

There is no need to use RIGHT JOINs in SQL.  

An outer join is a method of joining two tables where the records from one table are always included even if there is no match in the other table.  The way you tell the SQL engine which table you want to include ALL records from is by (A) the order you list the tables in the query and (B) whether you tell it to perform a LEFT JOIN or a RIGHT JOIN.

In other words, a RIGHT JOIN is nothing more than a LEFT JOIN with the order of the tables reversed in the SQL.

Hand-written SQL Example

For example:

FROM InvoiceItem AS II 
  RIGHT JOIN Invoice AS I ON II.InvoiceID = I.InvoiceID

...is identical to...

FROM Invoice AS I 
  LEFT JOIN InvoiceItem AS II ON I.InvoiceID = II.InvoiceID

The Query By Example Window in Access

To ensure you use LEFT JOINs when using the query builder window, always drag the field from the table where you want to show all records to the table where there may be missing records.

If you do this correctly, when you go into the "Join Properties" window you will choose option "2":

Why Do I Insist on Only Using LEFT JOINs?

In Access, there are situations where the Jet/ACE engine will get confused if you mix LEFT and RIGHT joins in the same query.  Using one or the other will often avoid the problem.

More importantly, though, it makes your SQL statements easier to read.  While the SQL engine (usually) doesn't care whether you use LEFT or RIGHT JOINs, it's easier to keep everything straight if you stick with one or the other.  And, since the rest of the database world tends to use LEFT JOINs, that's the one to go with.

Image by Clker-Free-Vector-Images from Pixabay