PostgreSQL RIGHT JOIN
Retrieve all records from the right table with matching records from the left
🔗 What is RIGHT JOIN?
RIGHT JOIN returns all records from the right table and matching records from the left table. If no match exists, NULL values appear for left table columns.
-- Basic RIGHT JOIN syntax
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;
Key RIGHT JOIN Concepts
All Right Records
Returns every row from right table
SELECT * FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.id;Opposite of LEFT
Mirror image of LEFT JOIN
-- These are equivalent:
-- A LEFT JOIN B = B RIGHT JOIN A
SELECT * FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.id;NULL for Unmatched
Shows NULL when no left match
-- Orders without customers show NULL
SELECT o.order_id, c.name
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.id;Find Missing Data
Identify right records without left match
SELECT c.name
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.id
WHERE o.order_id IS NULL;🔹 Basic RIGHT JOIN Example
Let's join orders with customers using RIGHT JOIN. This ensures all customers appear in results, even those without orders. RIGHT JOIN prioritizes the right table's completeness.
-- Sample tables
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT,
total_amount DECIMAL(10,2)
);
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);
-- RIGHT JOIN query
SELECT
o.order_id,
o.total_amount,
c.name,
c.email
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.id;Output:
| order_id | total_amount | name | |
|---|---|---|---|
| 101 | 299.99 | John Doe | [email protected] |
| NULL | NULL | Jane Smith | [email protected] |
🔹 RIGHT JOIN vs LEFT JOIN
RIGHT JOIN and LEFT JOIN are interchangeable by swapping table positions. Most developers prefer LEFT JOIN for readability, but RIGHT JOIN is useful in specific scenarios.
-- These queries produce the same result:
-- Using LEFT JOIN
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;
-- Using RIGHT JOIN
SELECT c.name, o.order_id
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.id;Output (Both queries):
| name | order_id |
|---|---|
| John Doe | 101 |
| Jane Smith | NULL |
🔹 Practical RIGHT JOIN Use Case
RIGHT JOIN is helpful when your main focus is the right table. For example, listing all products with their optional supplier information, ensuring no product is excluded.
-- List all products with supplier details
SELECT
p.product_name,
p.price,
s.supplier_name,
s.contact_email
FROM suppliers s
RIGHT JOIN products p ON s.id = p.supplier_id
ORDER BY p.product_name;Output:
| product_name | price | supplier_name | contact_email |
|---|---|---|---|
| Laptop | 999.99 | Tech Corp | [email protected] |
| Mouse | 29.99 | NULL | NULL |