RIGHT OUTER JOIN – Joining Multiple Tables in SQL

Table of Contents

• RIGHT OUTER JOIN with 2 tables
  • Result
  • Observations
• RIGHT OUTER JOIN with 3 tables
  • Result
  • Observations
• Summary

Just like LEFT OUTER JOIN, RIGHT OUTER JOIN is also a type of OUTER JOIN which fetches you data from multiple tables but this time it fetches all the data from RIGHT table and only matching data from the LEFT table.

It means that data from RIGHT table will always come and in the LEFT table where there are no matches, NULL will be returned. RIGHT OUTER JOIN is also written as RIGHT JOIN.

For convenience, we will use the same 3 tables as defined in the previous post SQL JOIN Explained with examples.

If we have 3 tables then first 2 tables will be RIGHT JOINed resulting in an intermediate table that will be RIGHT JOINed with the 3rd table.

RIGHT OUTER JOIN with 2 tables

Lets first try to join 2 tables i.e. customer and orders. Below is the query, you should see all the orders but only matching customers. Where customer is not found, null should be produced.

SELECT 
    c.customer_id, c.name, o.order_id 
FROM 
    customer c 
        RIGHT JOIN 
    orders o ON c.customer_id = o.customer_id;

Result

+-------------+---------+----------+
| customer_id | name    | order_id |
+-------------+---------+----------+
|         100 | Michael |       10 |
|         100 | Michael |       11 |
|         101 | Robert  |       12 |
|         103 | Grey    |       13 |
|        NULL | NULL    |       14 |
|        NULL | NULL    |       15 |
|        NULL | NULL    |       16 |
+-------------+---------+----------+

Observations

• Since we have 7 orders, all are fetched.
• In the customer table, we have total 4 customers and only 3 of them qualify the JOIN condition hence 3 customers in the JOIN result. Note that customer with id 100 has 2 orders.
• Non-matching rows in customer table fetched as NULL as there are no customers against the order 14, 15 and 16.

RIGHT OUTER JOIN with 3 tables

Lets now add the 3rd table as well i.e. invoice. This time you should see all the invoices but only matching order and customer. Non matching customer and order should be fetched as null.

SELECT 
    c.customer_id, c.name, o.order_id, i.invoice_id 
FROM 
    customer c 
        RIGHT JOIN 
    orders o ON c.customer_id = o.customer_id 
        RIGHT JOIN 
    invoice i ON o.order_id = i.order_id;

Result

+-------------+---------+----------+------------+
| customer_id | name    | order_id | invoice_id |
+-------------+---------+----------+------------+
|         100 | Michael |       11 |          1 |
|         101 | Robert  |       12 |          2 |
|         103 | Grey    |       13 |          3 |
|        NULL | NULL    |       14 |          4 |
|        NULL | NULL    |       15 |          5 |
|        NULL | NULL    |       16 |          6 |
|        NULL | NULL    |     NULL |          7 |
|        NULL | NULL    |     NULL |          8 |
+-------------+---------+----------+------------+

Observations

• Rows are fetching from all 3 tables.
• Since customer and orders table have been JOINed first, we can see their result which we already have. This result is considered as LEFT table and invoice is the RIGHT table.
• All the rows from RIGHT table invoice are fetching but only matching rows from LEFT table are fetching.
• Since the LEFT table does not have any matching data against the invoice id 7 and 8, NULL is returned.

Summary

This is how JOIN works. For simplicity we have joined only 3 tables in this example but you can join even more tables but the logic will remain same.

As a thumb rule, in LEFT OUTER JOIN the result will always contain all the rows from the left most table, while in RIGHT OUTER JOIN, the result will always contain all the data from right most table.

As a best practice, if you need to append more data from different tables without altering the existing data/query use LEFT OUTER JOIN.