The RIGHT JOIN is the exact mirror of the LEFT JOIN: it keeps every row of the right table, whether or not it matches the left one. Conceptually it adds nothing new —in fact, in this lesson you'll prove that any RIGHT JOIN can be rewritten as a LEFT JOIN— and yet it deserves a lesson of its own for three very practical reasons: you're going to run into it in other people's code, there are specific situations where it appears naturally, and mixing it with a LEFT JOIN in a three-table chain produces queries almost nobody can read.

Along the way, GreenStore's five employees who have never handled an order finally show up.

1. The RIGHT JOIN rule and the set diagram
2. Every employee with the orders they handled
3. The formal equivalence: A RIGHT JOIN B ≡ B LEFT JOIN A
4. Why almost everybody prefers LEFT
5. When a RIGHT JOIN does feel natural
6. The anti-join in its RIGHT version
7. The real danger: mixing LEFT and RIGHT in a chain
8. Support by engine
9. Common Mistakes and Tips
10. Exercises
11. Conclusion

Compare the module's three diagrams so far:

Everything learned in 03-03 applies just the same, with the sides swapped:

• RIGHT JOIN = RIGHT OUTER JOIN; the word OUTER is optional.
• The NULLs are manufactured by the engine, now in the left table's columns.
• Step 1d of the logical order reintroduces the orphan right-hand rows inside the FROM, before the WHERE.
• And therefore a condition in the WHERE over a column of the left table degrades the RIGHT JOIN to an INNER JOIN. It's exactly 03-03's trap, reflected.

The question: "give me the eight employees, with the orders each one has handled". Since we want every employee to appear, the table that must be kept in full is employees. If we write it on the right, the operator is a RIGHT JOIN:

15 rows: the 10 orders with a sales rep plus 5 employees with no order assigned. The arithmetic is 03-03's, reflected:

And the result reads like a portrait of the org chart:

• Óscar and Laia are the two sales reps: four orders each.
• Marc, from customer support, handled two.
• Irene Salvador Mira (warehouse operator) and Daniel Vercher Lluch (data analyst) have handled none, and that's what you'd expect: their roles don't sell.
• Rosa, Andrés and Beatriz haven't either, for the same reason: management and area managers don't handle orders directly.

An INNER JOIN would have returned 10 rows and three employees. The other five wouldn't have existed for the report, and an "activity by employee" query that leaves out five people out of eight isn't a report: it's a problem.

Let's write the same question the other way: putting employees on the left and using a LEFT JOIN.

An identical result, row by row. It doesn't resemble it: it is it.

The general rule, which you can apply mechanically:

A RIGHT JOIN B ON <cond> ≡ B LEFT JOIN A ON <cond>

To turn a RIGHT JOIN into a LEFT JOIN: swap the order of the two tables and change the word. The ON condition stays exactly as it is.

The only things that can change between the two versions are the column order if you were using SELECT *, and the row order if there were no ORDER BY. The data set is the same.

An honest conclusion comes out of this: the RIGHT JOIN is dispensable. There's no query that can only be written with a RIGHT JOIN. And yet you have to know it, because it exists in the code you'll inherit.

If they're equivalent, why do practically all professional style guides recommend LEFT?

Course convention: we'll write LEFT JOIN whenever we can choose, and we'll put as the FROM's first table the one whose rows must all appear. The RIGHT JOIN appears in this course so that you can read it and rewrite it, not as recommended style.

That said, there are two situations where writing a RIGHT JOIN is reasonable.

Imagine you have this query running in production, with its twelve-column SELECT and its filters:

And you're asked to make the report include the products that weren't sold too. The "correct" rewrite forces you to put products as the first table and reorder the whole FROM. Adding a line at the end is far less invasive:

It's a legitimate use, with one condition: document it. A one-line comment (-- RIGHT to keep the products with no sales) stops the next reader thinking it's a typo.

When the requirement arrives worded as "the order lines, and on top of that every product even if it has no lines", writing it in that same order is the most faithful thing to do:

It returns 50 rows: the 47 lines plus the 3 never-sold products. It's exactly the same result as 03-03's products LEFT JOIN order_lines.

Even so, the usual practice is to translate the requirement first and rewrite it afterwards as a LEFT before pushing it to the repository.

The pattern from 03-03 works the same, changing which side you check. To answer "which employees have never handled an order?":

5 rows. And the same query written as a LEFT, which is how we'd write it in the course:

An identical result. Notice the detail that stays the same across both versions: the column checked with IS NULL is always the primary key of the optional table (o.id), not that of the side being kept. The word LEFT or RIGHT changes; the anti-join's logic doesn't.

Reading the result: five of the eight employees having no orders isn't a data problem. Only two sales reps and one customer support person handle sales; management, logistics and data analysis don't. It's a query that confirms the business model rather than contradicting it.

Here's the serious reason to have style discipline. Consider this query, written by someone who wanted "every customer, with their orders and the employee of each order":

15 rows, and not a trace of Núria, Hugo or Inés. The LEFT JOIN written to keep them has been of no use whatsoever.

JOINs are resolved left to right:

The RIGHT JOIN keeps the right table (employees) in full and discards the left rows that don't match. And Núria's, Hugo's and Inés's rows have o.employee_id = NULL, so they match no employee and off they go. Just like the 10 web-order rows.

It's the same logic as 03-03's "LEFT followed by INNER", but in disguise: here the RIGHT acts on the accumulation of everything before it, not on the table sitting next to it.

The query above actually answers "every employee with the orders they handled and their customers". Written with LEFT alone it becomes transparent:

The same 15 rows. Now the FROM reads top to bottom: employees → their orders → the customer of each order, and the first table announces unambiguously that the report has at least one row per employee.

And if what the author really wanted was to keep the three customers with no orders, the query is a different one:

Which returns 23 rows, as you saw in 03-03.

Golden rule: in a chain of three or more tables, don't mix LEFT and RIGHT. Choose the side you want to keep, put it as the FROM's first table and use only LEFT JOIN from there on. A query with LEFT and RIGHT alternating is correct for the engine and unreadable for people, which is the worst possible combination.

A comparison of the three versions over the same three tables:

That last case is an extra argument in favour of the LEFT JOIN: a LEFT JOIN works on any engine and any version, including the SQLite versions embedded in old mobile apps. Always writing LEFT is a portability decision too.

Dialect note: in old Oracle, A RIGHT JOIN B was written by putting the (+) on the opposite side to the one being kept: WHERE a.id(+) = b.id. That notation is an inexhaustible source of mistakes and shouldn't be used in new code, although you'll find it in legacy systems.

• Confusing which side is kept. In A RIGHT JOIN B, what's kept in full is B, the table that comes after the word. It's the opposite of what left-to-right reading suggests.
• Mixing LEFT and RIGHT in the same chain. The RIGHT acts on everything accumulated up to that point, not on the adjacent table, and it can silently cancel a previous LEFT.
• Putting a condition on the left table in a RIGHT JOIN's WHERE. It degrades it to an INNER JOIN, exactly as in 03-03 but with the sides swapped.
• Doing the anti-join against the wrong column. In a RIGHT JOIN, the column checked with IS NULL is the primary key of the left table (the optional one), not that of the one being kept.
• Using a RIGHT JOIN without commenting on why. Whoever reviews the code will assume it's an oversight. A one-line comment sorts it out.
• Writing RIGHT JOIN in code that has to run on old SQLite. It doesn't exist before version 3.39.
• Tip: learn the mechanical conversion. Swap the two tables, change RIGHT for LEFT, leave the ON untouched. It always works and it's the fastest way to understand somebody else's RIGHT JOIN.
• Tip: when reviewing code, mentally rewrite every RIGHT as a LEFT before reasoning about it. It's quicker than trying to follow the inverted logic.
• Tip: decide the protagonist side before writing a single line. "Which noun has to come out complete in the report?" That table goes first, and everything else is a LEFT JOIN.

Write with a RIGHT JOIN the query that returns every product with the sales lines it may have had: the product's id and name, the line's id and quantity. Then rewrite it with a LEFT JOIN and check that the result is identical.

How many rows does it return and where does that number come from?

HR wants the listing of employees who have never handled an order, with their job title, their city and the first name of their manager.

1. Write it with a RIGHT JOIN for the orders part.
2. Rewrite it entirely with LEFT JOINs.
3. Explain why the JOIN with the managers table must be a LEFT and not an INNER.

(Hint: joining employees to itself to get the manager is a SELF JOIN; it's explained fully in 03-06, but here it's enough to use two different aliases for the same table.)

Analyse this query without running it:

1. What do you think the author's intention was?
2. What does it actually do? Do the never-sold products appear?
3. Fix it so that it returns every product, showing only the lines of more than 2 units and NULL for the rest.

Solution 1

With a RIGHT JOIN:

With a LEFT JOIN (the preferred form):

Both return 50 rows:

The three orphan rows are:

The transformation applied is section 3's mechanical one: order_lines and products are swapped, RIGHT becomes LEFT, and the condition ON ol.product_id = p.id isn't touched.

Solution 2

1. With a RIGHT JOIN on the orders part:

2. Rewritten with LEFT only:

5 rows.

3. Why the JOIN with the managers must be a LEFT: because Rosa Alcázar Vives has no manager (manager_id IS NULL, she's the general manager). With an INNER JOIN against employees AS manager, her row would find no partner and would disappear from the result, which would drop from 5 to 4 rows. It's exactly 03-03's "LEFT followed by INNER" case: a single INNER in the chain removes the very row that matters most. The complete employees hierarchy is studied in 03-06.

Solution 3

1. The intention was, in all likelihood, "every product with its lines of more than 2 units", keeping the whole catalogue thanks to the RIGHT JOIN.

2. What it actually does. The condition ol.quantity > 2 is in the WHERE and affects a column of the left table, which is precisely the optional one in a RIGHT JOIN. The three never-sold products reach the WHERE with ol.quantity = NULL, and NULL > 2 isn't TRUE. They disappear. The RIGHT JOIN degrades to an INNER JOIN and the query returns only the lines of more than 2 units with their product: no never-sold product appears at all.

It's 03-03's trap seen in the mirror: there the dangerous condition was on the right table of a LEFT; here, on the left of a RIGHT.

3. The fix. You have to move the condition into the ON and, while you're at it, rewrite everything with LEFT putting products as the protagonist table:

Now all 20 products always appear; the ones with no line of more than 2 units show *(null)* in line_id and quantity. Notice as well that the JOIN with categories has been written as a LEFT out of caution and stylistic consistency: it changes nothing here because every product has a category, but it upholds the rule "once you've opened a branch with LEFT, carry on with LEFT".

The RIGHT JOIN won't surprise you in anybody's code any more:

• It keeps every row of the right table, the one that comes after the word JOIN, padding the left-hand columns with NULL. RIGHT JOIN = RIGHT OUTER JOIN.
• You've finally met the five employees with no orders: Rosa, Andrés, Beatriz, Irene and Daniel. 15 rows against the INNER JOIN's 10.
• You know the formal equivalence: A RIGHT JOIN B ≡ B LEFT JOIN A. Swap the tables, change the word, leave the ON untouched. No query needs a RIGHT JOIN.
• You know why LEFT is preferred: the protagonist table reads first, long chains read top to bottom, and it works on every engine and version (SQLite doesn't support RIGHT until 3.39).
• You know when a RIGHT is defensible: when adding a table to the end of an already written query or when translating a requirement literally. Always with a comment justifying it.
• The anti-join in its RIGHT version works the same, checking the left table's primary key with IS NULL.
• And above all: don't mix LEFT and RIGHT in a chain. The RIGHT acts on everything accumulated and can silently cancel a previous LEFT, as in the example where Núria, Hugo and Inés disappeared despite the LEFT JOIN written to keep them.

In the next lesson, FULL OUTER JOIN, we'll close the outer-join family with the one that keeps the orphans on both sides at once. You'll see why in a schema with referential integrity —like GreenStore's— there are almost never orphans on both sides, and why its real playing field is the reconciliation of two independent data sources: a catalogue against an external sales file, an inventory against an accounting system.