Skip to content
Tutorials Center
My learning

Topics

Lesson 37 of 125 ยท Joins

RIGHT and FULL OUTER JOIN

Learn how SQL RIGHT JOIN and FULL OUTER JOIN keep unmatched rows, how to find orphans on both sides, compare two lists, and emulate FULL JOIN in MySQL.

A LEFT JOIN keeps every row of the table on its left, even when nothing matches on the right. SQL has two more outer joins that finish the picture. A RIGHT JOIN keeps every row of the table on the right, and a FULL OUTER JOIN keeps every row of both tables, matched where possible and padded with NULL where not.

You'll use LEFT JOIN far more often than these two, but FULL OUTER JOIN solves a family of problems that is awkward any other way: reconciling two lists, comparing two periods, and finding orphans on both sides of a relationship in a single query. SQLite has supported both joins since version 3.39 (2022), so every example below runs right here in your browser.

The examples use the company database: departments (id, name, office) and employees, whose department_id points at a department. Two employees, the CEO and a contractor, have no department at all.

The three outer joins at a glance

Every outer join starts like an INNER JOIN: it pairs up rows that satisfy the ON condition. Then it adds back rows that found no partner:

Join Matched pairs Unmatched left rows Unmatched right rows
INNER JOIN yes dropped dropped
LEFT JOIN yes kept, right columns NULL dropped
RIGHT JOIN yes dropped kept, left columns NULL
FULL OUTER JOIN yes kept, right columns NULL kept, left columns NULL

The word OUTER is optional: LEFT JOIN means LEFT OUTER JOIN, RIGHT JOIN means RIGHT OUTER JOIN and FULL JOIN means FULL OUTER JOIN. Many teams write FULL OUTER JOIN in full anyway, because it's rare enough that the extra word helps readers notice it.

RIGHT JOIN: keep every row on the right

In the sample data every department has at least one employee, so to see a RIGHT JOIN do something interesting, the next example first adds a new, still empty Legal department. The INSERT only changes this run's private copy of the database:

SQL RIGHT JOIN keeps every department ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

departments is on the right, so every department survives. Sales has four people in Toronto, and Legal appears once with NULL in both employee columns: it had no match, so the left side was filled with NULL. The WHERE clause only keeps Toronto offices so the result stays short; try removing it to see all 35 rows.

A RIGHT JOIN is a LEFT JOIN written backwards

A RIGHT JOIN B returns exactly the same rows as B LEFT JOIN A. Only the default column order changes, and you choose the columns in SELECT anyway. The next example runs both versions and counts the rows:

SQL Same rows, two spellings ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Both return 35: the 34 employees who belong to a department, plus one row for empty Legal. The CEO and the contractor are missing from both results, because they are unmatched rows of employees, and neither query keeps those.

Because of this symmetry, most style guides recommend writing LEFT JOIN only. Queries read top to bottom, and "start from this table, then attach optional extras" is easier to follow when the table you keep is always the first one. RIGHT JOIN is mostly useful when you're adding one more join to the end of a long query and the table you need to preserve is the new one, though even then, reordering the FROM clause is usually clearer.

FULL OUTER JOIN: keep both sides

A FULL OUTER JOIN combines the two ideas. Matched pairs appear once, unmatched left rows appear with NULL on the right, and unmatched right rows appear with NULL on the left:

SQL FULL OUTER JOIN ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The result has 37 rows: 34 matched employees, the CEO and the contractor with a NULL department, and Legal with NULL names. In SQLite, NULL sorts before any other value, so the two employees without a department appear first, and Legal appears in its alphabetical place between Finance and Research.

You can think of the row count like this: a FULL OUTER JOIN returns the INNER JOIN rows, plus the left rows that matched nothing, plus the right rows that matched nothing. Here that's 34 + 2 + 1 = 37.

Finding unmatched rows on either side

The most practical use of a FULL OUTER JOIN is an orphan report: every row that has no partner, from both tables at once. After a full join, a row is unmatched exactly when the key of one side is NULL. Test the primary keys, because those are never NULL in real rows:

SQL Orphans on both sides ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Three rows come back: Kofi Okafor (the CEO) and Amara Ivanova (the contractor) have no department, and Legal has no employees. With only LEFT JOINs you would need two queries, one in each direction, glued together with UNION ALL.

Test a column that can't be NULL in real data. If you wrote WHERE e.department_id IS NULL, you would mix up two cases: employees whose own column is empty and rows that came from the right side only. Testing e.id IS NULL (a primary key) means "this row has no employee side at all".

Comparing two lists

FULL OUTER JOIN shines when you compare two sets of results that overlap only partly: this month vs last month, the warehouse count vs the system count, yesterday's export vs today's. The shop database has an order_items table (order_id, product_id, quantity, โ€ฆ) and orders with an ordered_at timestamp. The next query compares units sold per product in January and February 2025.

Each side is a small query in parentheses that acts as a temporary table. That technique is called a derived table and is covered in Subqueries; for now, read (SELECT ...) AS jan as "the January totals":

SQL January vs February sales ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Look at the three kinds of rows: products with numbers in both columns sold in both months, a NULL in february means the product sold in January only, and a NULL in january means it sold only in February.

Notice COALESCE(jan.product_id, feb.product_id). After a full join, the key can be NULL on either side, so you can't simply select jan.product_id: the February-only rows would show an empty id. COALESCE returns the first non-NULL argument, which merges the two key columns into one. You'll write this pattern in almost every FULL OUTER JOIN. Try wrapping the month columns in COALESCE(..., 0) too, to show zeros instead of NULL.

ON vs WHERE with outer joins

As with LEFT JOIN, a condition in ON decides which rows match, while a condition in WHERE filters the finished result, including the padded rows. With a FULL OUTER JOIN this matters on both sides. A WHERE condition on a column of one table removes every row where that table's side is NULL, so it quietly undoes the "keep everything" part:

SQL WHERE can cancel the outer join ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The first query returns only the five Toronto rows (four Sales people and Legal), exactly like a RIGHT JOIN would. The second keeps all 36 employees (only the four Sales employees are matched) plus the six departments that matched nobody (the five outside Toronto and the empty Legal department), for 42 rows. Neither is wrong; they answer different questions. Decide whether a condition means "what counts as a match" (put it in ON) or "which final rows do I want" (put it in WHERE).

Emulating FULL OUTER JOIN

MySQL and MariaDB support RIGHT JOIN but not FULL OUTER JOIN, and very old SQLite versions supported neither. The classic workaround is a LEFT JOIN for the matched rows and left orphans, plus the right orphans added with UNION ALL:

SQL FULL JOIN without FULL JOIN ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The first half returns all 36 employees (two of them with a NULL department), and the second half adds the single department that has no employees, for the same 37 rows as the real FULL OUTER JOIN. Use UNION ALL, not UNION: plain UNION removes duplicate rows, which would wrongly merge two different people who happen to share a name and department.

Dialect support for outer joins

Database RIGHT JOIN FULL OUTER JOIN
SQLite yes, since 3.39 (2022) yes, since 3.39 (2022)
PostgreSQL yes yes
SQL Server yes yes
Oracle yes yes
MySQL / MariaDB yes no (use the UNION ALL pattern)

Which outer join should you use?

  • Default to LEFT JOIN when one table is the main subject of the question and the other adds optional details: "every customer and their orders, if any".
  • Use FULL OUTER JOIN when neither side is the main one and you care about mismatches in both directions: reconciliations, period comparisons, data-quality checks, syncing two systems.
  • Avoid RIGHT JOIN in new code unless it genuinely reads better. Flipping the tables and writing a LEFT JOIN gives the same rows.
  • Watch for NULL keys. After any outer join, merge key columns with COALESCE and test primary keys with IS NULL to find unmatched rows.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which query returns the same rows as employees AS e RIGHT JOIN departments AS d ON d.id = e.department_id?

    A RIGHT JOIN keeps every row of the right table. Swapping the tables and using LEFT JOIN keeps the same table, so it returns the same rows.
  2. 2. Table A has 10 rows and table B has 8. An INNER JOIN on their key returns 6 rows, each A row and each B row matching at most once. How many rows does the FULL OUTER JOIN return?

    6 matched pairs + 4 unmatched A rows + 2 unmatched B rows = 12.
  3. 3. After a FULL OUTER JOIN b ON b.id = a.id, which expression gives a usable id for every row?

    Unmatched rows have NULL on one side, so either column alone can be NULL. COALESCE returns whichever one is present.
  4. 4. You add WHERE d.office = 'Berlin' to a FULL OUTER JOIN of employees and departments. What happens to employees with no department?

    WHERE runs after the join and filters the padded rows too. NULL = 'Berlin' is not true, so those rows disappear. Put the condition in ON to keep them.
  5. 5. Which database in this list does NOT support FULL OUTER JOIN?

    MySQL (and MariaDB) lack FULL OUTER JOIN. Emulate it with a LEFT JOIN plus UNION ALL of the right-side rows that have no match.

Summary

  • RIGHT JOIN keeps every row of the right table; FULL OUTER JOIN keeps every row of both tables, with NULL where there was no match.
  • A RIGHT JOIN B equals B LEFT JOIN A, so most code uses LEFT JOIN for readability.
  • A FULL OUTER JOIN returns the inner join rows plus the unmatched rows of each side.
  • Find orphans on both sides with WHERE a.id IS NULL OR b.id IS NULL, testing primary keys.
  • Merge keys after a full join with COALESCE(a.key, b.key).
  • A WHERE condition on one side removes that side's padded rows; put "what counts as a match" in ON.
  • MySQL has no FULL OUTER JOIN: combine a LEFT JOIN with the right-side orphans using UNION ALL.

Next: Self Joins, where you'll join a table to itself to connect employees with their managers.