MySQL – RIGHT JOIN

RIGHT JOIN keeps every row from the right table, filling the left side with NULL where there is no match. It is LEFT JOIN with the tables the other way round, and that is very nearly the whole lesson.

The same question, both ways

"How many orders has each account placed, including accounts with none?" — as a right join, with app_user on the right:

SELECT u.email, COUNT(o.id) AS orders
FROM   customer_order o
RIGHT  JOIN app_user u ON u.id = o.user_id
GROUP  BY u.email
ORDER  BY u.email;

+---------------------+--------+
| email               | orders |
+---------------------+--------+
| admin@pizza.test    |      0 |
| customer@pizza.test |      8 |
+---------------------+--------+

And as a left join, with app_user written first:

SELECT u.email, COUNT(o.id) AS orders
FROM   app_user u
LEFT   JOIN customer_order o ON o.user_id = u.id
GROUP  BY u.email
ORDER  BY u.email;

+---------------------+--------+
| email               | orders |
+---------------------+--------+
| admin@pizza.test    |      0 |
| customer@pizza.test |      8 |
+---------------------+--------+

Identical results. The admin account has never ordered and appears with 0 either way — and note COUNT(o.id) rather than COUNT(*), for the reason given in the LEFT JOIN lesson: the unmatched row still exists, it just has NULLs in it.

RIGHT OUTER JOIN is the same thing spelled out; OUTER is optional, as it is for LEFT.

Why you will rarely see one

Every RIGHT JOIN can be written as a LEFT JOIN by swapping the table order, and teams almost always do. The reason is readability rather than dogma: a query reads top-down, so "start with accounts, attach their orders" follows the order you wrote the tables in. A right join asks the reader to hold the second table in mind as the important one while the first one is the one they read first — and in a query with four joins, working out which table each RIGHT is preserving is real effort for no benefit.

There is one honest argument for it. In a long chain of joins where the table you need to preserve is introduced late, flipping the whole FROM clause around to make it a left join means rewriting every line — and a rewrite is a chance to introduce a bug. Using RIGHT JOIN for that one line is a smaller change. That situation is uncommon; if you meet it, prefer the smaller change and leave a comment saying why.

Mixing them is where it gets confusing

SELECT o.id, i.product_name, u.email
FROM   customer_order o
LEFT   JOIN order_item i ON i.order_id = o.id
RIGHT  JOIN app_user u   ON u.id = o.user_id
ORDER  BY u.email, o.id
LIMIT  3;

Joins are evaluated left to right, so the RIGHT JOIN applies to the result of everything before it, not to customer_order alone. That is rarely what someone means when they write it. Pick one direction — in practice, LEFT — and order the tables to suit.

What about FULL OUTER JOIN?

MySQL does not have one. Where another database would keep unmatched rows from both sides at once, MySQL needs a UNION of a left join and a right join:

SELECT u.email, o.id FROM app_user u LEFT  JOIN customer_order o ON o.user_id = u.id
UNION
SELECT u.email, o.id FROM app_user u RIGHT JOIN customer_order o ON o.user_id = u.id;

UNION rather than UNION ALL, because the matched rows appear in both halves and need deduplicating. This is one of the few places a right join is genuinely the clear way to write something.

What to remember

• RIGHT JOIN preserves the right table; it is LEFT JOIN with the tables swapped.
• Prefer LEFT JOIN and order the tables to suit — that is what readers expect.
• Joins evaluate left to right, so mixing directions rarely means what it looks like.
• MySQL has no FULL OUTER JOIN; a UNION of both is the workaround.