OUTER JOIN - RIGHT

A right outer join is where all the rows in the "many" table (in the "one-to-many" relationship) are returned and only matching rows from the "one" table.
Selects every record from the second table.
This is often abbreviated and just called a right join.

You create a right outer join by using the keywords OUTER RIGHT JOIN and ON keywords.
This works in both SQL Server and Access

This is an example joining the EMPLOYEES table and the SUPPLIERS table

SELECT            Employees.LastName 
                  ,Suppliers.CompanyName 
FROM              EMPLOYEES 
RIGHT OUTER JOIN  SUPPLIERS 
ON                Employees.City = Suppliers.City 
WHERE             Employees.LastName IS NOT NULL 

This is an example joining the CUSTOMERS table and the ORDERS table

SELECT            Customers.CustomerID 
                  ,Customers.CompanyName 
                  ,Orders.OrderID 
FROM              CUSTOMERS 
RIGHT OUTER JOIN  ORDERS 
ON                Customers.CustomerID = Orders.CustomerID 
ORDER BY          OrderID 

Abbreviation

You can use RIGHT JOIN instead of RIGHT OUTER JOIN and it will have the same effect.

SELECT            Employees.LastName 
                  ,Suppliers.CompanyName 
FROM              EMPLOYEES 
RIGHT JOIN        SUPPLIERS 
ON                Employees.City = Suppliers.City 
WHERE             Employees.LastName IS NOT NULL 

This is not supported in Access

SELECT            Customers.CustomerID 
                  ,Customers.CompanyName 
                  ,Orders.OrderID 
FROM              CUSTOMERS 
                  ,ORDERS 
WHERE             Customers.CustomerID(+) = Orders.CustomerID 
ORDER BY          OrderID 

Also referred to as a Right Join.
A right outer join is where all the rows in the "many" table (in the "one-to-many" relationship) are returned and only matching rows from the "one" table.
The OUTER keyword is optional.

If the Orders tables contained any Orphans (i.e. an order without a customer) this query would find it
If you deleted the first Customer record in the Customers table (Alfreds Futterkiste) this would display the orphans

This displays a list of all orders that do not have a customer associated with them. Everything from the Orders table.

SELECT            Customers.CompanyName, 
                  Orders.OrderID 
FROM              Customers 
RIGHT OUTER JOIN  Orders 
ON                Customers.CustomerID = Orders.CustomerID 
ORDER BY          Customers.CustomerID 

Alternative (not supported in Access)

SELECT            Customers.CompanyName, 
                  Orders.OrderID 
FROM              Customers, 
                  Orders 
WHERE             Customers.CustomerID(+) = Orders.CustomerID 
ORDER BY          Customers.CustomerID