How does right join work?

General Questions~4 min read

SQL RIGHT JOIN returns every row from the right table and only the matching rows from the left table. Where there is no match, the left-table columns come back as NULL.

Theory

TL;DR

Analogy: every order on the list gets printed; customer info fills in only if a match exists.
Main difference: RIGHT JOIN keeps all right-table rows. INNER JOIN keeps only matches. LEFT JOIN keeps all left-table rows.
Decision rule: right table = source of truth (all shipments must appear) → RIGHT JOIN. Otherwise → LEFT JOIN or INNER JOIN.
A RIGHT JOIN B is identical to B LEFT JOIN A.

Quick example

sql

CREATE TABLE clients (id INT, name VARCHAR(50));
INSERT INTO clients VALUES (1, 'Alice'), (2, 'Bob');

CREATE TABLE orders (order_id INT, client_id INT, item VARCHAR(50));
INSERT INTO orders VALUES (101, 1, 'Laptop'), (102, NULL, 'Mouse'), (103, 3, 'Keyboard');

-- All orders appear; client name fills in where matched
SELECT c.name, o.item
FROM clients c
RIGHT JOIN orders o ON c.id = o.client_id;

Output:

name  | item
Alice | Laptop
NULL  | Mouse
NULL  | Keyboard

All 3 orders show up. Orders 102 and 103 have no matching client, so name is NULL.

RIGHT JOIN flips LEFT JOIN. LEFT keeps every row from the left table and fills NULLs on the right side when there is no match. RIGHT does the opposite: every row from the right table survives, and left-side columns go NULL without a match. In every codebase I have reviewed, RIGHT JOIN almost always gets rewritten as LEFT JOIN during code review, not because it is wrong, but because putting the driving table first reads more naturally. The query optimizer treats both the same way.

When to use

All rows from the right table must appear → RIGHT JOIN (all shipments, all log events).
All rows from the left table must appear → LEFT JOIN.
Only matching rows matter → INNER JOIN.
Everything from both sides → FULL OUTER JOIN.

Comparison table

Join type	Left table rows	Right table rows	NULLs placed in	Typical use case
INNER JOIN	Matches only	Matches only	None	Customers with orders
LEFT JOIN	All	Matches only	Right columns	All customers + their orders
RIGHT JOIN	Matches only	All	Left columns	All orders + optional customer info
FULL OUTER JOIN	All	All	Both sides	Full audit, complete merge

Common mistakes

Mistake: filtering NULLs with WHERE after RIGHT JOIN

sql

-- Wrong: this turns RIGHT JOIN into INNER JOIN
SELECT * FROM clients c
RIGHT JOIN orders o ON c.id = o.client_id
WHERE c.name IS NOT NULL;

WHERE runs after the join. Adding WHERE c.name IS NOT NULL drops every unmatched right-table row. You end up with the same result as INNER JOIN. To filter the left table while keeping all right-table rows, move the condition into the ON clause:

sql

-- Correct: filter inside ON, right-table rows are preserved
SELECT * FROM clients c
RIGHT JOIN orders o ON c.id = o.client_id AND c.name IS NOT NULL;

Mistake: expecting NULL join keys to disappear

If order.client_id is NULL, that row still appears in the result. NULL never equals NULL, so there is no match, and c.name will be NULL. The row is not dropped.

Mistake: mixing RIGHT JOIN and LEFT JOIN in the same query

FROM clients RIGHT JOIN orders is identical to FROM orders LEFT JOIN clients. Most SQL style guides prefer LEFT JOIN so the driving table stays first. Mixing both types in one query makes the logic hard to follow.

Real-world usage

E-commerce: all shipments with optional customer profiles (shipments RIGHT JOIN customers).
Logging: all events with optional user session data, so no log entry is lost.
HR systems: all employee records with optional department assignments.
Analytics: all ad impressions with optional click data.

Follow-up questions

Q: What happens if the join column contains NULL on the right-table side?
A: The right-table row still appears in the result. NULL does not equal NULL, so there is no match, and the left-side columns come back as NULL.

Q: How do you rewrite a RIGHT JOIN as a LEFT JOIN?
A: Swap the table order and change the join type. FROM A RIGHT JOIN B ON A.id = B.id becomes FROM B LEFT JOIN A ON B.id = A.id.

Q: Why does a WHERE filter on the left table break the RIGHT JOIN behavior?
A: WHERE runs after the join. WHERE left.col IS NOT NULL removes all unmatched right-table rows, which gives the same result as INNER JOIN. Use the ON clause to filter the left table if you still need all right-table rows.

Q: In a chain like A RIGHT JOIN B RIGHT JOIN C, how do NULLs propagate?
A: Each link resolves left to right. If A has no match for B, A's columns become NULL. That NULL then participates in the next join with C. Draw it as two separate joins to trace how NULLs flow through the chain.

Examples

Basic: all orders with optional client names

sql

CREATE TABLE clients (id INT, name VARCHAR(50));
INSERT INTO clients VALUES (1, 'Alice'), (2, 'Bob');

CREATE TABLE orders (order_id INT, client_id INT, item VARCHAR(50));
INSERT INTO orders VALUES (101, 1, 'Laptop'), (102, NULL, 'Mouse'), (103, 3, 'Keyboard');

SELECT c.name, o.order_id, o.item
FROM clients c
RIGHT JOIN orders o ON c.id = o.client_id;
-- name  | order_id | item
-- Alice | 101      | Laptop
-- NULL  | 102      | Mouse     <- no client_id
-- NULL  | 103      | Keyboard  <- client_id 3 does not exist

Orders 102 and 103 have no matching record in clients. RIGHT JOIN keeps them in the result regardless.

Intermediate: e-commerce shipment report

sql

CREATE TABLE customers (cust_id INT, email VARCHAR(100));
INSERT INTO customers VALUES (1, 'alice@shop.com'), (2, 'bob@shop.com');

CREATE TABLE shipments (ship_id INT, cust_id INT, status VARCHAR(20));
INSERT INTO shipments VALUES
  (5001, 1, 'Delivered'),
  (5002, NULL, 'Pending'),
  (5003, 2, 'Shipped'),
  (5004, 3, 'Cancelled');

SELECT s.ship_id, s.status, c.email
FROM customers c
RIGHT JOIN shipments s ON c.cust_id = s.cust_id
ORDER BY s.ship_id;
-- ship_id | status    | email
-- 5001    | Delivered | alice@shop.com
-- 5002    | Pending   | NULL
-- 5003    | Shipped   | bob@shop.com
-- 5004    | Cancelled | NULL

All 4 shipments appear in the logistics report. Shipments 5002 and 5004 have no registered customer, but the business still needs to track them. That is exactly what RIGHT JOIN is for.

Short Answer

Interview ready

Premium

A concise answer to help you respond confidently on this topic during an interview.

Finished reading?