RIGHT JOIN | Engineering Interview Kit

Concept

A RIGHT JOIN (or RIGHT OUTER JOIN) returns all rows from the Right table, regardless of whether they have a match in the Left table.

If there is a match, the Left table’s data is attached.
If there is no match, the Left table’s columns are filled with NULL.

It is the exact mathematical mirror of a LEFT JOIN.

Mental Model

SELECT users.name, orders.product
FROM users
RIGHT JOIN orders 
  ON users.id = orders.user_id;

In this scenario:

Orders is the Right table. The database guarantees every single Order will be returned.
Users is the Left table.
If an order exists in the database but the user who purchased it was somehow deleted (meaning user_id points to nothing), the order is still returned, but users.name will be NULL.

Why is it rarely used?

In standard software engineering, you will almost never see a RIGHT JOIN in a codebase.

Why? Because human beings read from Left to Right (in Western languages), and we mentally construct SQL queries starting with the primary subject.
If you want to focus on Orders and optionally bring in User data, you don’t write Users RIGHT JOIN Orders. You simply flip the tables and write Orders LEFT JOIN Users.

-- These two queries are mathematically identical:

-- Weird and hard to read
SELECT * FROM users RIGHT JOIN orders ON users.id = orders.user_id;

-- Standard and easy to read
SELECT * FROM orders LEFT JOIN users ON orders.user_id = users.id;

By strictly adhering to LEFT JOINs, developers maintain a consistent mental model: “Start with the most important table, and optionally tack on extra data as you go down the page.”

Interview Questions

Q: Are there any specific database engines that do not support RIGHT JOIN?
A: Yes, SQLite historically did not support RIGHT JOIN or FULL OUTER JOIN (though recent versions added support). Because RIGHT JOIN is just syntactic sugar for a flipped LEFT JOIN, the SQLite creators deemed it unnecessary bloat for a lightweight mobile database. You were simply expected to rewrite the query as a LEFT JOIN.

Q: If RIGHT JOIN is just a flipped LEFT JOIN, is there any complex query where you absolutely MUST use a RIGHT JOIN because flipping the tables is impossible?
A: No. Mathematically, the relational algebra is identical. Any query written with a RIGHT JOIN can be rewritten using a LEFT JOIN simply by changing the order of the tables in the FROM and JOIN clauses. It is purely a matter of syntax and developer preference, which is why styling conventions heavily favor sticking to LEFT JOIN exclusively to prevent cognitive overload.