RIGHT JOIN: Keep All Rows from the Right Table

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

If a row in the right table has no match, it is still in the result. The columns from the left table are NULL.


Syntax

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

Example Query

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

Output:

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

This returns all orders, even the order that has no customer. Order 107 (Tablet) has no matching customer, so its name is NULL. Olivia is not in the result, because she has no orders.


Tip

A RIGHT JOIN B gives the same rows as B LEFT JOIN A. Many people only use LEFT JOIN and put the table they want to keep first. Both ways work.


When to Use RIGHT JOIN?

  • When you want all rows from the right table.
  • To find rows in the right table that have no match in the left table.

Next, you will learn FULL JOIN, which keeps everything from both tables.

In the exercise, use RIGHT JOIN to list every order with its city.