SQL / JOINS
RIGHT JOIN and when to rewrite it instead
Read a RIGHT JOIN correctly, rewrite the two-table form as a LEFT JOIN safely, and spot the chained case where reversing table order changes the answer.
What you will learn
- Predict which rows get NULL-padded by A RIGHT JOIN B, and which rows still vanish
- Rewrite a two-table RIGHT JOIN as a LEFT JOIN by swapping tables and keeping ON
- Recognise that a chained RIGHT JOIN preserves rows against the whole prior result
- Reproduce a chained RIGHT JOIN with a derived table, not by reversing FROM order
Understanding RIGHT JOIN and when to rewrite it instead
RIGHT JOIN keeps every row of the table named to the right of the keyword. Rows that find a partner on the left look exactly like an inner join result; rows that find nobody are emitted anyway, with every column taken from the left side set to NULL. The protection is one-directional, and that is what beginners miss: below, Dov has a NULL dept_id, matches no department, and is simply gone, because employees sits on the left. Hold it as: take the inner join result, then add back the unmatched rows of the preserved table, padded with NULLs.
Which side is preserved is decided by the keyword, not by the ON predicate, so employees RIGHT JOIN departments ON e.dept_id = d.dept_id and departments LEFT JOIN employees ON e.dept_id = d.dept_id return the same rows, and only the order of the table names differs. Teams standardise on LEFT for a readability reason rather than a semantic one: with LEFT, the table whose rows are guaranteed is the first thing in the FROM clause, and every later join reads as optional detail hanging off something already introduced. RIGHT inverts that, so in a long FROM clause you have to scan to the bottom to learn what the query is a report of.
The rewrite is only mechanical for two tables. Joins in a FROM clause combine left to right, so in staff JOIN offices ... RIGHT JOIN regions ... the left input of the RIGHT JOIN is the already-joined staff/offices result, not the offices table. Every region survives, but a region whose only office has no staff loses office_name and staff_name together, because the inner join discarded that office before regions was attached. Reversing the table order into regions LEFT JOIN offices LEFT JOIN staff does not reproduce that, since it re-asks the question with the office kept; the faithful rewrite wraps the earlier joins in a derived table so the nesting survives.
Keeping a RIGHT JOIN is defensible when you are appending a table to a query whose FROM clause you cannot reorder, such as generated SQL or a view you are extending, and the intent really is "and give me a row for every value in this last table".
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(10)
);
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(10),
dept_id INT
);
INSERT INTO departments VALUES (1,'Sales'), (2,'Support'), (3,'Research');
INSERT INTO employees VALUES (10,'Ada',1), (11,'Brix',1), (12,'Cyd',2), (13,'Dov',NULL);
SELECT d.dept_name, e.emp_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id
ORDER BY d.dept_name, e.emp_name;A RIGHT JOIN preserves the rows of whatever stands on its right against everything already joined to its left, which makes the LEFT rewrite exact for two tables but a change of question for three.
Worked examples
The two-table rewrite is exact
Runs both forms side by side to show the RIGHT and LEFT versions produce the same rows.
CREATE TABLE departments (dept_id INT, dept_name VARCHAR(10));
CREATE TABLE employees (emp_id INT, emp_name VARCHAR(10), dept_id INT);
INSERT INTO departments VALUES (1,'Sales'), (2,'Support'), (3,'Research');
INSERT INTO employees VALUES (10,'Ada',1), (11,'Brix',1), (12,'Cyd',2), (13,'Dov',NULL);
SELECT 'RIGHT' AS written_as, d.dept_name, e.emp_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id
UNION ALL
SELECT 'LEFT', d.dept_name, e.emp_name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
ORDER BY dept_name, emp_name, written_as;Example explained
Line 1Both branches use the identical ON predicate; only the FROM order and the join keyword change.
Line 2Research appears once per branch with emp_name NULL, so the RIGHT form buys nothing the LEFT form cannot express.
Line 3Dov is absent from both halves, because neither form preserves rows of the employees side.
Line 4Eight rows, four per branch, in matching pairs is the evidence that the rewrite is row-for-row faithful.
A RIGHT JOIN in a chain
Shows that the preserved side is compared against the accumulated join result, not against the table written just before it.
CREATE TABLE regions (region_id INT, region_name VARCHAR(10));
CREATE TABLE offices (office_id INT, office_name VARCHAR(10), region_id INT);
CREATE TABLE staff (staff_id INT, staff_name VARCHAR(10), office_id INT);
INSERT INTO regions VALUES (1,'North'), (2,'South'), (3,'West');
INSERT INTO offices VALUES (10,'Oslo',1), (11,'Lima',2);
INSERT INTO staff VALUES (100,'Eve',10);
SELECT r.region_name, o.office_name, s.staff_name
FROM staff s
JOIN offices o ON o.office_id = s.office_id
RIGHT JOIN regions r ON r.region_id = o.region_id
ORDER BY r.region_name;Example explained
Line 1staff JOIN offices runs first and yields exactly one row, Eve at Oslo; Lima is dropped there because no staff row points at it.
Line 2The RIGHT JOIN then attaches regions to that one-row result, so its left input spans two tables at once.
Line 3South matches nothing, so office_name and staff_name both go NULL even though the office Lima exists in the table.
Line 4Two columns from two different tables going NULL together is the signal that the preserved side was a joined result.
Faithful rewrite with a derived table
Reproduces the chained RIGHT JOIN result using only a LEFT JOIN, by preserving the original nesting.
CREATE TABLE regions (region_id INT, region_name VARCHAR(10));
CREATE TABLE offices (office_id INT, office_name VARCHAR(10), region_id INT);
CREATE TABLE staff (staff_id INT, staff_name VARCHAR(10), office_id INT);
INSERT INTO regions VALUES (1,'North'), (2,'South'), (3,'West');
INSERT INTO offices VALUES (10,'Oslo',1), (11,'Lima',2);
INSERT INTO staff VALUES (100,'Eve',10);
SELECT r.region_name, staffed.office_name, staffed.staff_name
FROM regions r
LEFT JOIN (
SELECT o.region_id, o.office_name, s.staff_name
FROM offices o
JOIN staff s ON s.office_id = o.office_id
) AS staffed ON staffed.region_id = r.region_id
ORDER BY r.region_name;Example explained
Line 1The derived table staffed contains exactly the rows the inner JOIN produced in the previous example.
Line 2regions is now the left table of a LEFT JOIN, so the FROM clause names the guaranteed table first.
Line 3South gets NULL for both columns because the entire derived-table row is missing, matching the RIGHT JOIN result exactly.
Line 4Flattening this into regions LEFT JOIN offices LEFT JOIN staff still returns three rows, but South then shows Lima instead of NULL, which is a different question.
Important notes
RIGHT JOIN and RIGHT OUTER JOIN are the same operator, and OUTER changes nothing. SQLite only gained RIGHT JOIN in 3.39, and many query builders never emit it, which is a practical argument for the LEFT form.
The order of columns inside ON does not choose the preserved side: ON e.dept_id = d.dept_id and ON d.dept_id = e.dept_id behave identically, and only the keyword decides.
Common mistakes
Believing RIGHT JOIN rescues unmatched rows on both sides, so an employee with a NULL dept_id is expected in the output and is instead silently missing.
Rewriting staff JOIN offices RIGHT JOIN regions as regions LEFT JOIN offices LEFT JOIN staff and trusting the row count: both give three rows here, but the flattened form reports the office Lima that the original had already filtered away.
Mixing LEFT and RIGHT JOIN in one FROM clause, then reordering tables during a later edit; the set of preserved rows changes and no error is raised.
Try it yourself
Change, predict, then run
Create courses(course_id, title) and enrolments(enrolment_id, course_id, student) with one course nobody enrolled in and one enrolment whose course_id is NULL. Write the report as enrolments RIGHT JOIN courses, rewrite it as courses LEFT JOIN enrolments, and confirm both results agree, including which row never shows up.
Open the SQL workspaceCheck your understanding
A query reads FROM staff s JOIN offices o ON o.office_id = s.office_id RIGHT JOIN regions r ON r.region_id = o.region_id. A region has exactly one office, and that office has no staff. What does the result contain for that region?
- One row for the region with NULL in both office_name and staff_name
- One row for the region with the office name filled in and NULL staff_name
- No row for that region, because its office was filtered out by the inner join
- One row per office in the region, each with NULL staff_name
Show answer
The RIGHT JOIN attaches regions to the result of staff JOIN offices, and that inner join has already discarded the staffless office, so the region matches nothing and every column from both left tables is NULL-padded. Option 1 is what regions LEFT JOIN offices LEFT JOIN staff would return, a differently nested query; option 2 confuses filtering on the offices side with the region's guaranteed row, which the RIGHT JOIN protects.