The FULL OUTER JOIN completes the outer-join family: it keeps every row of both tables, matching or not. It's a LEFT JOIN and a RIGHT JOIN in a single operation.

It's also, by some margin, the JOIN you'll write least often. And for an interesting reason this lesson explains: in a database with properly declared referential integrity, like GreenStore's, orphans can only exist on one side. A FULL OUTER JOIN between two tables related by a foreign key almost always degenerates into a LEFT JOIN. Its real playing field is somewhere else: the reconciliation of two independent data sources, where neither of the two has authority over the other.

1. The rule and the set diagram
2. FULL OUTER JOIN over GreenStore: why orphans only appear on one side
3. The real case: reconciling two data sources
4. The "only what doesn't match on either side" pattern
5. Emulation on engines that don't support it
6. Support by engine, and cost
7. Common Mistakes and Tips
8. Exercises
9. Conclusion

The complete picture of the family, now with all four types:

Syntax: FULL OUTER JOIN and FULL JOIN are the same thing, because OUTER is optional just as in LEFT and RIGHT. In this course we write the full FULL OUTER JOIN, because its rarity justifies being explicit.

And a property that sets it apart from the other two outer joins: the FULL OUTER JOIN is commutative. A FULL OUTER JOIN B and B FULL OUTER JOIN A return the same set of rows, because both sides get the same treatment.

Let's try the most obvious case: catalogue against sales.

50 rows. Exactly the same as the LEFT JOIN in 03-03 returned:

The FULL OUTER JOIN hasn't contributed a single row over the LEFT JOIN. Why?

Because for an orphan to appear on the right side there would have to exist an order line whose product_id corresponded to no product. And that's precisely what the foreign key prevents:

Two constraints act at once:

A very useful general rule comes out of that:

Between two tables joined by a NOT NULL foreign key with declared referential integrity, a FULL OUTER JOIN is always equivalent to a LEFT JOIN from the parent table. Writing it is redundant, and on top of that it pays a cost it doesn't need.

The same happens with customers and orders: orders.customer_id is NOT NULL REFERENCES customers(id), so customers FULL OUTER JOIN orders returns the same 23 rows as the LEFT JOIN.

The only case in GreenStore where a FULL OUTER would contribute something is orders with employees, because employee_id allows NULL:

• Orphans on the left: the 10 web orders with no employee.
• Orphans on the right: the 5 employees with no orders.
• Total: 10 pairings + 10 + 5 = 25 rows.

It's the only FULL OUTER JOIN that makes sense in this schema, and even so it would be clearer to write two separate queries: "orders with no sales rep" and "employees with no orders" answer different business questions, and mixing them in one table with nulls on both sides helps nobody.

The FULL OUTER JOIN shines when the two tables are not joined by a foreign key: when they're two independent sources that ought to agree and you have to work out where they don't. Common examples:

In all of them the key is the same: neither of the two sources is the authority. Each can have rows the other lacks, and the report's goal is precisely to find them.

GreenStore has started selling on an external marketplace too, which every month sends a CSV file with the units sold per SKU. We load that file into a temporary table:

Warning: marketplace_sales isn't part of the GreenStore schema. It's an auxiliary table created only for this example. Don't use it in the rest of the course's exercises, and drop it when you're done (or disconnect: being TEMP, it vanishes with the session).

19 rows. Notice two things: there's no foreign key at all between sku and products.id —the file comes from another system, we can't impose constraints on it— and two strange SKUs show up, 101 and 102.

22 rows = 17 pairings + 3 products with no marketplace sales + 2 unknown SKUs.

Now there really are orphans on both sides, and each one tells a different story:

• Products 13, 19 and 20 haven't sold on the marketplace. They coincide with the ones that don't sell in our own shop either, which reinforces the diagnosis: no stock, discontinued, and a commercial problem.
• SKUs 101 and 102 have generated sales on the marketplace but don't exist in the catalogue. That's a serious operational alert: it could be an old reference under a different numbering, a mapping error between systems, or sales nobody is attributing to any product.

A writing detail: the ORDER BY COALESCE(p.id, ms.sku) sorts by the identifier "wherever it comes from". If you sorted only by p.id, the two marketplace rows would have NULL in that column and would go to the end (or the beginning, depending on NULLS FIRST/LAST, as you saw in 02-05). COALESCE returns the first non-null value in the list, and it's studied in depth in 06-04.

The complete reconciliation is fine for reviewing, but what gets sent to operations is just the list of discrepancies. It's the equivalent of 03-03's anti-join, now on both counts:

5 rows: the three catalogue discrepancies and the two from the file. This is a genuine reconciliation report, and in a real system it would land in your inbox every morning.

In set terms, this pattern returns the symmetric difference:

Two important writing details:

And the three variants of the filter, depending on what you want:

MySQL and MariaDB don't implement FULL OUTER JOIN. The classic emulation consists of combining a LEFT JOIN and a RIGHT JOIN with UNION:

The logic is straightforward:

The 17 matching rows appear in both queries. That's why you have to use UNION and not UNION ALL: UNION removes duplicates and leaves 20 + 19 − 17 = 22 rows. With UNION ALL you'd get 39 rows, with the 17 matches repeated twice.

UNION, UNION ALL and their rules are the subject of lesson 03-07; here it's enough to know that the emulation exists and why it needs to remove duplicates.

Dialect note: in MySQL the emulation has an unpleasant twist. Since UNION compares whole rows, two rows differing in any column are considered distinct; if your data set legitimately contained repeated rows, UNION would collapse them and you'd lose information. The robust alternative is a complete LEFT JOIN UNION ALLed with only the right-hand orphans (RIGHT JOIN ... WHERE p.id IS NULL), which generates no overlap and needs no deduplication.

Four reasons, in order of importance:

1. Referential integrity makes it unnecessary within a single schema. That's section 2's argument, and it explains most cases.
2. What you want is almost always one of the two halves. "Products with no sales" or "sales with no product" are specific questions that a LEFT JOIN with an anti-join answers better, and with a more readable result.
3. The result is awkward to read. A table with nulls on both sides forces you into COALESCE for sorting, for grouping and for presenting. Every key column exists twice over.
4. It's the most expensive JOIN. The engine has to traverse both relations completely and mark the unmatched rows on both sides: it can't stop early or discard soon. In PostgreSQL it's only implemented with a hash join or a merge join; there's no nested loop plan for a FULL OUTER JOIN, which sometimes forces both sides to be materialised and sorted. With large tables you notice it (module 8).

Practical rule: before writing a FULL OUTER JOIN, ask yourself whether you genuinely need the orphans from both sides in the same result. Nine times out of ten the answer is no, and a LEFT JOIN is clearer and faster. The tenth time —a real reconciliation between two systems— is exactly what it exists for.

• Using FULL OUTER JOIN between two tables joined by a NOT NULL FK. It contributes no row over the LEFT JOIN and costs more. Referential integrity already guarantees there are no orphans on the child side.
• Writing AND instead of OR in the discrepancy filter. WHERE p.id IS NULL AND ms.sku IS NULL always returns 0 rows: no row of the result can be missing from both sides at once.
• Sorting by just one of the two keys. The orphan rows from the other side have that column at NULL and go to the far end of the listing. Use ORDER BY COALESCE(a.key, b.key).
• Forgetting that the key columns are duplicated. In the reconciliation you have p.id and ms.sku. To present a single identifier you need COALESCE(p.id, ms.sku).
• Emulating it with UNION ALL in MySQL. It duplicates every matching row: 39 instead of 22 in this lesson's example.
• Assuming it exists in MySQL. It doesn't, and it isn't planned. If your SQL has to be portable, avoid it.
• Confusing "no partner" with "empty value". Just as in 03-03: always check against a column that can't legitimately be NULL in its source table.
• Tip: start with the LEFT JOIN and check whether you need more. Run the LEFT, count rows, run the FULL OUTER and compare. If the number doesn't change, the FULL OUTER is redundant.
• Tip: in a reconciliation, add a column saying where each row comes from. With CASE (module 6) you can label each row as "catalogue only", "file only" or "matches". The report becomes self-explanatory.
• Tip: keep the discrepancy pattern. FULL OUTER JOIN ... WHERE a.key IS NULL OR b.key IS NULL is a template you'll reuse every time two systems have to reconcile.

Run these two queries and compare the number of rows:

1. How many rows does each one return?
2. Explain the result in terms of the constraints declared on orders.customer_id.
3. What would have to change in the schema for the two queries to give different results?

Over the temporary table marketplace_sales from section 3, write two separate queries:

1. The catalogue products that don't appear in the marketplace file, with their name, price and stock.
2. The SKUs in the file that don't exist in the catalogue, with the units sold.

Write the first with a LEFT JOIN and the second with a RIGHT JOIN, and then reason it out: why are two separate queries more useful here than section 4's FULL OUTER JOIN?

Your company is migrating a reconciliation query written in PostgreSQL over to MySQL:

1. Rewrite it for MySQL using UNION.
2. Could you use UNION ALL in this particular rewrite? Reason it out by looking at what each half returns.

Solution 1

1. Both return 23 rows.

2. The column is declared like this:

The three parts act together:

• REFERENCES customers(id) prevents inserting an order with a non-existent customer_id.
• NOT NULL prevents inserting an order with no customer.
• ON DELETE RESTRICT prevents deleting a customer who has orders, which is the other way of generating orphans.

Conclusion: no orphan order can exist, so the right side contributes nothing and the FULL OUTER JOIN degenerates into the LEFT JOIN. The 23 rows are the same ones: 20 orders + 3 customers with no orders.

3. It would be enough for customer_id to allow NULL —for example, to record guest orders with no account. Those orders would match no customer and would appear as orphans on the right, visible only with a FULL OUTER JOIN (or with a RIGHT JOIN). It's exactly the situation of orders.employee_id, which does allow nulls: orders FULL OUTER JOIN employees returns 25 rows against the LEFT JOIN's 20.

Solution 2

1. Catalogue products that aren't in the file:

2. SKUs in the file that don't exist in the catalogue:

Why two separate queries are more useful here:

The FULL OUTER JOIN is still useful for the overall picture —section 3's 22-row table, for reviewing at a glance— but the operational work is better handled with two targeted queries.

Solution 3

1. Rewrite for MySQL:

It returns the same 5 rows as the PostgreSQL version.

2. Can you use UNION ALL? Yes, and it's preferable.

The reasoning is what matters: UNION (without ALL) is needed when the two halves can produce the same rows, and the duplicates have to be removed. That's the case with section 5's general emulation, where both JOINs return the 17 matching rows.

That doesn't happen here. Each half carries its own WHERE restricting it to the orphans of its side:

No row can satisfy both conditions at once, so the sets are disjoint and there's nothing to deduplicate. UNION ALL is faster because it saves the sorting or hash-table step UNION needs to detect duplicates. It's a perfect example of the rule you'll see in 03-07: use UNION ALL unless you have a specific reason to remove duplicates.

You've closed the outer-join family:

• The FULL OUTER JOIN keeps the orphan rows from both sides: it's a LEFT and a RIGHT at once. It's the only commutative outer join.
• Over GreenStore it almost never contributes anything, and you can explain why: referential integrity (REFERENCES + NOT NULL + ON DELETE RESTRICT) prevents orphans on the child side from existing. products FULL OUTER JOIN order_lines returns the same 50 rows as the LEFT JOIN.
• Its real ground is the reconciliation of two independent sources, where neither has authority over the other: a catalogue against a marketplace file, an inventory against accounting, a bank statement against invoices.
• You've built that complete reconciliation (22 rows) and the discrepancy report with the pattern WHERE a.key IS NULL OR b.key IS NULL (5 rows): three products the marketplace doesn't sell and two SKUs that don't exist in the catalogue. Remember: the operator is OR, never AND.
• You know how to emulate it with a UNION of a LEFT JOIN and a RIGHT JOIN for MySQL, and why that emulation needs to remove duplicates unless each half is restricted to its own orphans.
• And you know its cost: it admits no nested loop plan, it forces both relations to be traversed in full and it produces a result that needs COALESCE for nearly everything. Use it when you genuinely need both sides.

In the next lesson, SELF JOIN and CROSS JOIN, we'll look at the two "odd" JOINs that confuse beginners the most. The SELF JOIN will finally let you walk GreenStore's two reflexive relationships: the employees hierarchy with manager_id —where you'll need a LEFT JOIN again so as not to lose Rosa, the manager with no manager— and the customers referral network. The CROSS JOIN, which so far has only shown up as an accident, will become a deliberate tool for generating complete combinations.