Skip to main content
Joins
3 / 4
0%

RIGHT JOIN and FULL OUTER JOIN

0/3 solved3 to unlock next
PrevLocked

Lesson 03

RIGHT JOIN and FULL OUTER JOIN

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.

Two more outer joins

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.

Syntax

-- 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:

JoinMatched pairsUnmatched left rowsUnmatched right rows
INNER JOINkeptdroppeddropped
LEFT JOINkeptkept, right side NULLdropped
RIGHT JOINkeptdroppedkept, left side NULL
FULL OUTER JOINkeptkept, right side NULLkept, left side NULL

RIGHT JOIN

1. Every doctor, written as a RIGHT JOIN

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.

2. RIGHT JOIN with a filter

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.)

FULL OUTER JOIN

3. Locations and departments, both sides kept

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.

4. Reconciling against a list

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.

5. Showing only the mismatches

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.

Under the hood

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.

Common mistakes

Checking only one side for NULL

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.

Using one side's key as the identifier

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.

Expecting a full join to reveal gaps that do not exist

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.

Other databases

DatabaseRIGHT JOINFULL OUTER JOIN
PostgreSQLyesyes
SQL Serveryesyes
MySQLyesno; emulate with a LEFT JOIN and a RIGHT JOIN combined with UNION
SQLiteyes, since 3.39yes, since 3.39

If you write SQL that has to run on MySQL, plan around the missing full join.

Practice

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.

Key takeaways

  • 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.
  • Full joins are ideal for reconciliation; filter with WHERE a.key IS NULL OR b.key IS NULL to see only mismatches.
  • Use COALESCE(a.key, b.key) to produce one reliable key column after a full join.
  • PostgreSQL requires an equality-style condition for FULL JOIN, and MySQL does not support it at all.

RIGHT JOIN and FULL OUTER JOIN: in depth

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.

Worked examples

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.

Common mistakes

  • Filtering one side in WHERE after a FULL JOIN, which removes the unmatched rows from that side and defeats the purpose.
  • Forgetting that both sides can be NULL. Use COALESCE(a.key, b.key) when you need one combined key column.
  • Mixing LEFT and RIGHT JOINs in one query; it becomes very hard to reason about which rows survive.

Other databases

  • MySQL does not support FULL OUTER JOIN. Emulate it with a LEFT JOIN UNION a RIGHT JOIN.
  • SQLite added RIGHT and FULL joins only in version 3.39 (2022).
Check yourself: In a FULL OUTER JOIN of employees and departments, how do you list only the departments with no staff?

Add WHERE e.id IS NULL. Those rows come only from the departments side.

Concept check

Quick check: RIGHT JOIN and FULL OUTER JOIN

3 quick questions. One at a time. Instant score at the end.

Exercise 1

All customers and their order IDs (RIGHT JOIN)

Commerce Operations IntelligenceeasyPostgreSQL

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.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 2

Jobs and employees (FULL OUTER JOIN)

Enterprise Workforce AnalyticseasyPostgreSQL

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.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 3

All departments and all doctors (FULL OUTER JOIN)

Clinical Care PerformanceeasyPostgreSQL

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.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Joins
3 / 4
0%

RIGHT JOIN and FULL OUTER JOIN

0/3 solved3 to unlock next
PrevLocked