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.