RIGHT OUTER JOIN

Another type of join is called a SQL Server RIGHT OUTER JOIN. This type of join returns all rows from the RIGHT-hand table specified in the ON condition andonlythose rows from the other table where the joined fields are equal (join condition is met).

Syntax

The syntax for the RIGHT OUTER JOIN in SQL Server (Transact-SQL) is:

SELECT columns FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;

In some databases, the RIGHT OUTER JOIN keywords are replaced with RIGHT JOIN.

Visual Illustration

In this visual diagram, the SQL Server RIGHT OUTER JOIN returns the shaded area:

The SQL Server RIGHT OUTER JOIN would return the all records fromtable2_and only those records from_table1_that intersect with_table2.

Example

Here is an example of a RIGHT OUTER JOIN in SQL Server (Transact-SQL):

This RIGHT OUTER JOIN example would return all rows from the orders table and only those rows from the suppliers table where the joined fields are equal.

If a supplier_id value in the orders table does not exist in the suppliers table, all fields in the suppliers table will display as <null_>_in the result set.

Let's look at some data to explain how RIGHT OUTER JOINS work:

We have a table called_suppliers_with two fields (supplier_id and supplier_name). It contains the following data:

We have a second table called_orders_with three fields (order_id, supplier_id, and order_date). It contains the following data:

SELECT orders.order_id, orders.order_date, suppliers.supplier_name
FROM suppliers
RIGHT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

Our result set would look like this:

The row for500127(order_id) would be included because a RIGHT OUTER JOIN was used. However, you will notice that the supplier_name field for that record contains a <null> value.