RIGHT (OUTER) JOIN

A RIGHT (OUTER JOIN) in SQL is a type of join that returns all the rows from the right table, and the matched rows from the left table. If there are no matches in the left table, the result is NULL on the left side. Here is a Venn diagram representation of the RIGHT (OUTER) JOIN:

The syntax for a RIGHT OUTER JOIN is as follows:

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Here is the RIGHT (OUTER) JOIN query with corresponding Venn diagram again (this time without the intersection):

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
WHERE table2.column_name IS NULL

Let’s consider the same two tables, Orders and Customers:

Orders

OrderID  | CustomerID  | Product    | Quantity
-------- | ----------- | ---------- | --------
1        | 3           | Book       | 2
2        | 1           | Pen        | 3
3        | 2           | Notebook   | 1
4        | 5           | Pencil     | 4
5        | 1           | Eraser     | 1

Customers

CustomerID | CustomerName
---------- | ------------
1          | John
2          | Maria
3          | Steve
4          | Michael

Example Test Question

Prepare a list of all orders and the customers who made them. If an order doesn’t have a matching customer, the customer fields should be displayed as NULL.

Answer

To solve this, we need to do a RIGHT JOIN on the Customers and Orders tables. We would join them based on the CustomerID which is common in both tables.

Here is the SQL query to achieve this:

SELECT Orders.OrderID, Orders.Product, Orders.Quantity, Customers.CustomerName
FROM Customers
RIGHT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

This would return the following result:

OrderID  | Product  | Quantity | CustomerName
-------- | -------- | -------- | ------------
1        | Book     | 2        | Steve
2        | Pen      | 3        | John
3        | Notebook | 1        | Maria
4        | Pencil   | 4        | NULL
5        | Eraser   | 1        | John

In the result, you can see that all orders are included, regardless of whether they have a corresponding customer or not. Where there is no corresponding customer for an order (in this case, OrderID 4), the customer fields are displayed as NULL.

This is how a RIGHT OUTER JOIN works in SQL, providing a result set that includes all records from the right table and the matching records from the left table. If there is no match, the result is NULL on the left side.