Lesson Description
Understand RIGHT JOIN and FULL OUTER JOIN behavior for edge cases where you need to retain unmatched rows from one or both sides. You will compare outputs to choose the right join type intentionally.
Lesson 03
Lesson Description
Understand RIGHT JOIN and FULL OUTER JOIN behavior for edge cases where you need to retain unmatched rows from one or both sides. You will compare outputs to choose the right join type intentionally.
Easy Project
Mini project: Build a people-and-departments report that lists employee names, department names, and validates row counts to prove your join is correct.
JOIN Visual
employees
departments
Match keys first, then verify row counts after the join.
LEFT JOIN from the previous lesson keeps every row from the table on the left. SQL has two siblings that complete the set:
RIGHT JOIN keeps every row from the table on the right.FULL OUTER JOIN keeps every row from both tables.You will write RIGHT JOIN rarely, but you need to read it, because it turns up in other people's code. FULL OUTER JOIN is less common still, but for one job it is the perfect tool: reconciliation, where you compare two lists and want to see what is missing from each. A typical question from the HR sandbox: "Which office locations have no department, and which departments point at a location we do not know about?" Answering both halves in one query needs a full outer join.
-- Keep every row of table_b
SELECT ...
FROM table_a a
RIGHT JOIN table_b b ON b.id = a.b_id;
-- Keep every row of both tables
SELECT ...
FROM table_a a
FULL OUTER JOIN table_b b ON b.id = a.b_id;
As with LEFT JOIN, the word OUTER is optional: RIGHT OUTER JOIN is the same as RIGHT JOIN, and FULL JOIN is the same as FULL OUTER JOIN.
Here is how all four join types compare:
| Join | Matched pairs | Unmatched left rows | Unmatched right rows |
|---|---|---|---|
INNER JOIN | kept | dropped | dropped |
LEFT JOIN | kept | kept, right side NULL | dropped |
RIGHT JOIN | kept | dropped | kept, left side NULL |
FULL OUTER JOIN | kept | kept, right side NULL | kept, left side NULL |
SELECT d.first_name, d.last_name, a.appointment_date, a.status
FROM appointments a
RIGHT JOIN doctors d ON d.id = a.doctor_id;
This returns 67 rows: the 66 appointments, each paired with its doctor, plus one row for Marcus Wright, who has no appointments and therefore gets NULL in appointment_date and status.
Now compare it with this:
SELECT d.first_name, d.last_name, a.appointment_date, a.status
FROM doctors d
LEFT JOIN appointments a ON a.doctor_id = d.id;
Same 67 rows. A RIGHT JOIN is always a LEFT JOIN with the tables written in the other order. That is why many teams adopt a simple style rule: write everything as LEFT JOIN. Queries read top to bottom as "start with this table, then attach optional extras", and you never have to flip directions in your head.
The same ON versus WHERE rules from the previous lesson apply, mirrored. In the store sandbox, list each customer's orders that were not cancelled, keeping customers who have no orders at all:
SELECT c.first_name, c.last_name, o.id AS order_id, o.status
FROM orders o
RIGHT JOIN customers c ON c.id = o.customer_id
WHERE o.status <> 'cancelled'
OR o.id IS NULL;
This returns 49 rows: 47 non-cancelled orders plus the 2 customers with no orders. The OR o.id IS NULL part matters. Without it, the WHERE clause would test NULL <> 'cancelled', which is unknown, and the two order-less customers would be dropped. (Moving the status test into ON is the other way to keep them.)
SELECT l.city, d.name AS department
FROM departments d
FULL OUTER JOIN locations l ON l.id = d.location_id
ORDER BY l.city, d.name;
You get 14 rows. Ten of them pair a department with its city (Seattle alone hosts five: Accounting, Administration, Executive, Finance and Purchasing). The other four are locations with no department at all: Mexico City, Munich, Roma and Utrecht, each with NULL in the department column.
In this data every department has a valid location, so no row has a NULL city. A full outer join would show such rows if they existed, which is the whole point: you do not need to know in advance which side has gaps.
Full outer joins shine when you compare a table with another source that may disagree with it. Suppose HR sends you a headcount plan for four department ids, and one of them, 120, is a department that has not been created yet. You can write the plan inline with VALUES and join it to the real table:
SELECT
COALESCE(d.id, t.department_id) AS department_id,
d.name,
t.target_headcount
FROM departments d
FULL OUTER JOIN (
VALUES (10, 1), (60, 6), (80, 9), (120, 4)
) AS t(department_id, target_headcount)
ON t.department_id = d.id
ORDER BY department_id;
The result has 11 rows: the 10 real departments plus a row for id 120. Three departments (Administration, IT and Sales) have a target. Seven have a NULL target because the plan does not mention them. Department 120 has a target of 4 but a NULL name because it does not exist in departments.
Two details are worth copying:
COALESCE(d.id, t.department_id) builds a single key column. After a full join, the key from either side can be NULL, so you take whichever one is present.ORDER BY department_id sorts by that combined key, so unmatched rows from both sides slot into place instead of clumping at the end.For a reconciliation report you usually want only the problems. Keep rows where either side is missing:
SELECT
COALESCE(d.id, t.department_id) AS department_id,
d.name,
t.target_headcount
FROM departments d
FULL OUTER JOIN (
VALUES (10, 1), (60, 6), (80, 9), (120, 4)
) AS t(department_id, target_headcount)
ON t.department_id = d.id
WHERE d.id IS NULL
OR t.department_id IS NULL
ORDER BY department_id;
This returns 8 rows: the 7 departments missing from the plan and the 1 planned department missing from the table. This is the full-join version of the anti-join you learned with LEFT JOIN, checking both directions at once.
PostgreSQL builds a full outer join by finding all matched pairs, then adding unmatched rows from the left, then adding unmatched rows from the right. To do that efficiently it needs either a hash join or a merge join, both of which depend on an equality condition. If you try a full join on an inequality, it refuses:
SELECT COUNT(*)
FROM employees e
FULL OUTER JOIN jobs j ON e.salary > j.max_salary;
ERROR: FULL JOIN is only supported with merge-joinable or hash-joinable join conditions
LEFT and RIGHT joins do not have this restriction. In practice almost every full join is on matching keys, so you will rarely hit it.
After a full join, WHERE d.id IS NULL finds only the right-only rows. If you meant "all mismatches", you need WHERE d.id IS NULL OR t.department_id IS NULL, as in example 5.
SELECT d.id, d.name, t.target_headcount
FROM departments d
FULL OUTER JOIN (VALUES (10, 1), (120, 4)) AS t(department_id, target_headcount)
ON t.department_id = d.id;
The row for department 120 shows id = NULL, so the report no longer tells you which department is missing. Use COALESCE(d.id, t.department_id) to keep the key visible.
A full outer join returns the same rows as an inner join when both sides match completely. For example, every employee in sandbox_hr has a valid job and every job has at least one employee, so a full join of employees and jobs returns the same 40 rows as an inner join. That is not a bug. It is the data telling you there are no orphans.
| Database | RIGHT JOIN | FULL OUTER JOIN |
|---|---|---|
| PostgreSQL | yes | yes |
| SQL Server | yes | yes |
| MySQL | yes | no; emulate with a LEFT JOIN and a RIGHT JOIN combined with UNION |
| SQLite | yes, since 3.39 | yes, since 3.39 |
If you write SQL that has to run on MySQL, plan around the missing full join.
The exercises below this lesson ask you to count orders per customer using a RIGHT JOIN from orders to customers, and to full-join jobs to employees and hospital departments to doctors, with a specific NULLS LAST ordering. For the right join, remember which table is preserved and what to count so that customers with no orders show 0. For the full joins, pay close attention to the required sort order, and notice whether any NULLs actually appear in this data.
RIGHT JOIN keeps every row of the right-hand table; it is always equivalent to a LEFT JOIN with the tables swapped, so most teams just use LEFT JOIN.FULL OUTER JOIN keeps every row from both sides and pads the missing side with NULLs.WHERE a.key IS NULL OR b.key IS NULL to see only mismatches.COALESCE(a.key, b.key) to produce one reliable key column after a full join.FULL JOIN, and MySQL does not support it at all.RIGHT JOIN keeps every row from the right table, and FULL OUTER JOIN keeps unmatched rows from both sides. FULL JOIN is the tool for reconciliation: comparing two lists and seeing what exists only on one side, only on the other, or in both.
Departments and employees, nothing dropped
SELECT d.name AS department, e.first_name, e.last_name
FROM employees e
FULL OUTER JOIN departments d ON d.id = e.department_id
ORDER BY d.name NULLS LAST;Shows employees without a department (department is NULL) and departments without employees (name columns are NULL).
RIGHT JOIN rewritten as LEFT JOIN
-- Same result as: FROM employees e RIGHT JOIN departments d ...
SELECT d.name, e.first_name
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id;Swapping the table order turns any RIGHT JOIN into a LEFT JOIN, which most teams find easier to read.
Add WHERE e.id IS NULL. Those rows come only from the departments side.
Concept check
3 quick questions. One at a time. Instant score at the end.
From the store sandbox, return customer id, first_name, last_name, and order_count (including 0 for customers with no orders). Use a RIGHT JOIN from orders to customers. Order by id.
No results yet
Run a query to see results here
From the HR sandbox, return job title and employee first_name + last_name using a FULL OUTER JOIN between jobs and employees on job_id. Show NULL where no match exists. Order by title NULLS LAST, then last_name NULLS LAST.
No results yet
Run a query to see results here
From the hospital sandbox, FULL OUTER JOIN departments to doctors on department_id. Return department name and doctor first_name, last_name. Order by name NULLS LAST, last_name NULLS LAST.
No results yet
Run a query to see results here