RIGHT JOIN

Hook: Interviewers love this one because it checks whether you know which side of a join is being preserved, not just whether you can type the syntax.

Question: What is a RIGHT JOIN?

Answer: A RIGHT JOIN returns every row from the table on the right side of the join. When a row on the right has no match on the left, the left-side columns come back as NULL, which means “missing value.” It is basically the mirror image of a LEFT JOIN.

Interview-Ready Answer: I think of a RIGHT JOIN as “keep all rows from the right table, and bring in matches from the left when they exist.” If there is no match, the left columns are filled with NULL. In practice, I often rewrite it as a LEFT JOIN by swapping the table order, because that is usually easier to read and reason about. One important detail is that filters on the preserved side should usually stay in the ON clause if you want to keep the unmatched rows.

What a RIGHT JOIN really means

A RIGHT JOIN is an outer join, which means it does not throw away unmatched rows from one side. In this case, the right table is the preserved side. That preserved side is the key idea: every row from that table survives, even if the other table has no match.

How it works under the hood

1. The database reads the two inputs and the join condition, usually written with ON.
2. It decides on a join algorithm. Common choices are hash join (build a lookup structure for one side) or nested loop join (check rows pair by pair).
3. For each row in the preserved right table, it searches for matching rows on the left side using the join condition.
4. If matches exist, it outputs one result row per match.
5. If no match exists, it still outputs the right row, but every left-side column is set to NULL.
6. After that, the database applies WHERE, GROUP BY, HAVING, and ORDER BY in that order. This is why a WHERE filter can accidentally remove the unmatched rows that the RIGHT JOIN just preserved.

That last step is a classic interview trap. SQL uses three-valued logic, meaning a condition can be TRUE, FALSE, or UNKNOWN. Comparisons with NULL usually become UNKNOWN, and WHERE keeps only TRUE, so unmatched rows often disappear if you filter on the non-preserved side after the join.

RIGHT JOIN vs other joins

In pure logic, a RIGHT JOIN is not special; it is just a LEFT JOIN written from the other side. That is why many teams prefer LEFT JOIN for readability. The optimizer may even rewrite one into the other internally if it can prove the result is the same.

When and why to use it

• Use it when the right table is the one you must keep completely.
• Use it for reports where the right side is the primary list, such as all orders, all invoices, or all events.
• Avoid it if your team finds LEFT JOIN easier to scan, because consistency matters more than the keyword choice.

Performance notes

There is no fixed time complexity for a RIGHT JOIN because the optimizer chooses the plan. With a hash join, the work is often close to O(n + m) on average, with memory roughly proportional to the smaller input. With nested loops, worst-case work is O(n * m), which becomes huge fast: 1,000,000 rows by 1,000,000 rows is far too many comparisons. In practice, indexes on join keys and good statistics matter much more than whether the keyword is RIGHT or LEFT.

Important edge cases

• Duplicates multiply rows: if one left row matches three right rows, you get three output rows.
• NULL does not equal NULL: a normal equality join will not match null keys.
• WHERE can cancel the outer join: a filter on left-side columns after a RIGHT JOIN can remove the preserved right rows.
• Order is not guaranteed: if you care about order, use ORDER BY.

Memory trick: think “the table on the right gets the VIP pass.” Everyone on that side gets in; the left side only joins if it has a matching ticket.

Real-world story

Imagine a subscription billing service that needs a daily reconciliation report. The report must show every invoice that was generated today, even if the customer profile is missing or late to sync from another system. The invoice table is the right table, so the report uses a RIGHT JOIN to keep all invoices and attach customer details when available.

What goes wrong when someone misunderstands it? A developer adds WHERE customer.status = 'active' after the RIGHT JOIN. That looks harmless, but it removes invoices whose customer row is missing, because the customer columns are NULL for unmatched rows. The finance dashboard suddenly undercounts billed revenue. The symptom is a row-count mismatch: the invoice table says 120,000 rows for the day, but the report only shows 117,842. Logs look normal, yet support tickets start saying “missing invoices” and “payment totals do not match.” The real bug is not the join itself; it is filtering after the join in a way that destroys the preserved rows.

Follow-up & Tricky Questions:

• How is RIGHT JOIN different from LEFT JOIN? They are mirror images. RIGHT JOIN preserves the right table; LEFT JOIN preserves the left table. In practice, many developers rewrite RIGHT JOIN as LEFT JOIN by swapping table order because it is easier to read.
• What happens if there is no match? The preserved side still appears in the result, and the other side’s columns become NULL. That is the defining feature of an outer join.
• What is the difference between ON and WHERE? ON decides which rows match during the join, while WHERE filters the final rows afterward. A filter in WHERE can remove the null-extended rows and accidentally make the result behave like an inner join.
• Can RIGHT JOIN produce duplicate rows? Yes. If one row on the preserved side matches multiple rows on the other side, you get one output row per match. That is normal and often desired in reporting.
• Does RIGHT JOIN guarantee order? No. SQL result order is undefined unless you use ORDER BY.
• What if the join keys contain NULL? A normal equality join will not match NULL to NULL. If you need null-safe matching, use a database-specific null-safe operator or a condition built for that purpose.
• Is RIGHT JOIN the same as FULL OUTER JOIN? No. RIGHT JOIN preserves only the right side. FULL OUTER JOIN preserves both sides, so unmatched rows from both tables survive.
• Trick: If I add WHERE left_table.id IS NOT NULL after a RIGHT JOIN, what happens? You remove the null-extended rows, so the query behaves much more like an inner join. That is a common bug because the JOIN looked outer, but the WHERE clause erased the outer part.
• Trick: If both tables have a row with the same key, do I get one row or two? You get one combined row for each match, not two separate rows. If either side has duplicates for that key, the duplicates multiply.

Common Mistakes:

• Mistake: Thinking RIGHT JOIN means “right table columns come first.” Fix: It means the right table is preserved, not that the output columns are ordered a certain way.
• Mistake: Putting a filter on the non-preserved side in WHERE. Fix: Move the condition into ON if you want to keep unmatched rows.
• Mistake: Assuming RIGHT JOIN is special. Fix: It is usually just a LEFT JOIN written from the opposite direction.
• Mistake: Forgetting that NULL does not match NULL with =. Fix: Use null-safe logic when that behavior is needed.

Memory Hook: “Right side gets the spotlight.” The right table is always kept; the left table only joins when it can follow along.

Cheat Sheet:

• RIGHT JOIN = keep all rows from the right table.
• Missing matches on the left become NULL.
• RIGHT JOIN is the mirror image of LEFT JOIN.
• ON matches rows; WHERE filters after the join.
• Outer joins do not guarantee output order.
• Many teams rewrite RIGHT JOIN as LEFT JOIN for readability.

Practice Tasks:

• Write a RIGHT JOIN that returns all orders and customer names, including orders with no customer.
• Add a filter for customer city, once in ON and once in WHERE, and compare the result.
• Rewrite a RIGHT JOIN as an equivalent LEFT JOIN and verify the output rows are the same.