Part 2: Exploring Right Joins, Full Outer Joins, and Self Joins

In the previous post, we got comfortable with inner joins and left joins. we will cover the remaining join types right join, full outer join, and self join which help us handle more complex data relationships. Let’s get started!

1. Right Join

A right join (or right outer join) works just like a left join, but from the right side. It retrieves all rows from the right table and any matching rows from the left table. If there is no match, the left table’s values will be NULL.

SELECT users.username, orders.order_id 
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

• We get all orders from the orders table.

• If an order exists without a matching user (maybe a user was deleted), the username will be NULL.

This join ensures we don’t miss any records from the right-side table, even if there are gaps in the left side data.

2. Full Outer Join

A full outer join is the ultimate collector it returns all rows from both tables. If there’s a match, the result includes the data from both sides. If there isn’t, unmatched columns are filled with NULL.

SELECT users.username, orders.order_id 
FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;

• If there’s a match between users and orders, it returns both.

• Users with no orders? Their order_id will be NULL.

• Orders with no matching users? The username will be NULL.

Full outer joins are helpful when we need everything, even incomplete data say, for an audit or a reconciliation report.

3. Self Join

We use it when we need to compare rows within the same table.

SELECT e1.name AS employee, e2.name AS manager 
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.id;

• We’re joining the employees table to itself, treating it as two distinct tables (e1 and e2).

• This query retrieves the name of each employee and their corresponding manager.

Self joins are great when working with hierarchies like employees and managers, product categories, or recursive structures.

When to Use Which Join?

Conclusion

In this second part, we have explored the right join, full outer join, and self join, learning how to connect tables in creative ways. Right joins ensure we capture everything from the right side, full outer joins guarantee no data is left behind, and self joins help us work with hierarchies within a single table.