RIGHT JOIN — Include All Records from the Right Table

A RIGHT JOIN returns all rows from the right table, and the matching rows from the left table.

If there is no match, the result will still include the right table's data, and show NULL for the left table's columns.

Syntax

SELECT table1.column, table2.column
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;

Example Scenario

Using the same two tables:

customers

+-------------+--------+-------------+-----------+
| customer_id | name   | city        | country   |
+-------------+--------+-------------+-----------+
| 1           | Alice  | New York    | USA       |
| 2           | Bob    | Los Angeles | USA       |
| 3           | Emma   | London      | UK        |
| 4           | Paul   | Berlin      | Germany   |
| 5           | Olivia | Madrid      | Spain     |
+-------------+--------+-------------+-----------+

orders

+----------+-------------+--------------+--------------+
| order_id | customer_id | product_name | order_status |
+----------+-------------+--------------+--------------+
| 101      | 1           | Laptop       | Delivered    |
| 102      | 1           | Phone        | Shipped      |
| 103      | 2           | Charger      | Cancelled    |
| 104      | 3           | Watch        | Delivered    |
| 105      | 4           | Headphones   | Shipped      |
| 106      | 4           | Speakers     | Pending      |
| 107      | NULL        | Tablet       | Incomplete   |
+----------+-------------+--------------+--------------+

Here, Order 107 has no matching customer (customer_id is NULL).

Example Query

SELECT customers.name, orders.product_name, orders.order_status
FROM customers
RIGHT JOIN orders
ON customers.customer_id = orders.customer_id;

This returns all orders, even if there’s no matching customer in the customers table.

Output

+--------+--------------+--------------+
| name   | product_name | order_status |
+--------+--------------+--------------+
| Alice  | Laptop       | Delivered    |
| Alice  | Phone        | Shipped      |
| Bob    | Charger      | Cancelled    |
| Emma   | Watch        | Delivered    |
| Paul   | Headphones   | Shipped      |
| Paul   | Speakers     | Pending      |
| NULL   | Tablet       | Incomplete   |
+--------+--------------+--------------+

The last row has NULL for name because the order has no matching customer.

When to Use RIGHT JOIN?

  • When you want all data from the right table.
  • Useful for finding orphan records in the right table that don't match anything in the left table.

Next, we’ll explore FULL JOIN, which brings everything together!