How to Use FULL OUTER JOIN in PostgreSQL

I didn’t fully appreciate FULL OUTER JOIN until I had to reconcile two datasets that were supposed to match but didn’t — a list of expected payments and a list of actual bank transactions. I needed to see everything: payments with no matching transaction, transactions with no matching payment, and the ones that lined up correctly. A regular join wasn’t going to cut it, and that’s exactly the situation FULL OUTER JOIN was built for.

This article walks through what FULL OUTER JOIN does, how it differs from the other join types, and the practical patterns I use it for in real PostgreSQL work.

What Is a FULL OUTER JOIN?

A FULL OUTER JOIN returns all rows from both tables, matching them where possible, and filling in NULL for columns from whichever table doesn’t have a matching row.

Think of it as the union of a LEFT JOIN and a RIGHT JOIN — every row from the left table appears (matched or not), and every row from the right table appears (matched or not).

Setting Up an Example

CREATE TABLE expected_payments (
    id SERIAL PRIMARY KEY,
    invoice_number VARCHAR(20),
    amount NUMERIC(10,2)
);

CREATE TABLE bank_transactions (
    id SERIAL PRIMARY KEY,
    invoice_number VARCHAR(20),
    received_amount NUMERIC(10,2)
);

INSERT INTO expected_payments (invoice_number, amount) VALUES
('INV-001', 500.00),
('INV-002', 750.00),
('INV-003', 300.00);

INSERT INTO bank_transactions (invoice_number, received_amount) VALUES
('INV-001', 500.00),
('INV-004', 200.00);

Notice INV-002 and INV-003 have no matching bank transaction, and INV-004 is a transaction with no matching expected payment. This is exactly the messy real-world scenario FULL OUTER JOIN handles well.

Basic Syntax

SELECT
    ep.invoice_number AS expected_invoice,
    ep.amount AS expected_amount,
    bt.invoice_number AS actual_invoice,
    bt.received_amount AS actual_amount
FROM expected_payments ep
FULL OUTER JOIN bank_transactions bt
    ON ep.invoice_number = bt.invoice_number;

Running this returns:

Every row from both tables shows up. Matched rows have data on both sides. Unmatched rows have NULL on the side that’s missing.

Finding Only the Unmatched Rows

This is, honestly, the most common reason I reach for FULL OUTER JOIN. I usually don’t care about the matched rows — I want to see what’s missing on either side. Adding a WHERE clause after the join filters this down:

SELECT
    ep.invoice_number AS expected_invoice,
    ep.amount AS expected_amount,
    bt.invoice_number AS actual_invoice,
    bt.received_amount AS actual_amount
FROM expected_payments ep
FULL OUTER JOIN bank_transactions bt
    ON ep.invoice_number = bt.invoice_number
WHERE ep.id IS NULL OR bt.id IS NULL;

This gives me exactly the discrepancies: expected payments never received, and transactions that don’t correspond to any expected invoice. In a real reconciliation workflow, this single query does the work that would otherwise take a lot of manual spreadsheet comparison.

FULL OUTER JOIN With Multiple Tables

You can chain multiple FULL OUTER JOIN clauses, though it gets more complex to reason about as you add tables:

SELECT
    a.name,
    b.value AS from_b,
    c.value AS from_c
FROM table_a a
FULL OUTER JOIN table_b b ON a.id = b.a_id
FULL OUTER JOIN table_c c ON a.id = c.a_id;

Be careful here — chaining multiple full outer joins can produce more rows than you expect, since each join independently preserves unmatched rows from its side. I usually test each join step by step rather than writing the whole chain at once.

FULL OUTER JOIN vs Other Join Types

It helps to see all the join types side by side, since they’re often confused:

• INNER JOIN: only rows that match in both tables.
• LEFT JOIN: all rows from the left table, matched rows from the right (NULL if no match).
• RIGHT JOIN: all rows from the right table, matched rows from the left (NULL if no match).
• FULL OUTER JOIN: all rows from both tables, matched where possible, NULL where not.

If I only need “what’s on the left plus matches,” I use LEFT JOIN. FULL OUTER JOIN is specifically for when I care about both directions of unmatched data simultaneously.

Combining FULL OUTER JOIN With COALESCE

Since unmatched rows produce NULLs on one side or the other, COALESCE() is a natural companion for producing a single unified column:

SELECT
    COALESCE(ep.invoice_number, bt.invoice_number) AS invoice_number,
    ep.amount AS expected_amount,
    bt.received_amount AS actual_amount,
    COALESCE(ep.amount, 0) - COALESCE(bt.received_amount, 0) AS difference
FROM expected_payments ep
FULL OUTER JOIN bank_transactions bt
    ON ep.invoice_number = bt.invoice_number;

This gives me one clean invoice_number column regardless of which side the match came from, plus a calculated difference column showing any payment shortfall or overage — treating missing values as zero.

Performance Considerations

FULL OUTER JOIN is generally more expensive than INNER JOIN or a single-direction LEFT/RIGHT JOIN, because PostgreSQL has to track unmatched rows from both sides rather than just one. On large tables, make sure the join column is indexed:

CREATE INDEX idx_expected_payments_invoice ON expected_payments (invoice_number);
CREATE INDEX idx_bank_transactions_invoice ON bank_transactions (invoice_number);

Always verify with EXPLAIN ANALYZE on large datasets — PostgreSQL typically implements a FULL OUTER JOIN using a hash join or merge join strategy, and having indexes on the join columns (especially for merge joins) can meaningfully affect performance.

EXPLAIN ANALYZE
SELECT * FROM expected_payments ep
FULL OUTER JOIN bank_transactions bt ON ep.invoice_number = bt.invoice_number;

Simulating FULL OUTER JOIN Without It

It’s worth understanding what FULL OUTER JOIN saves you from writing manually, because it helps clarify exactly what’s happening under the hood. Before I understood this join type properly, I’d sometimes cobble together the same result using a UNION of a LEFT JOIN and a RIGHT JOIN:

SELECT ep.invoice_number, ep.amount, bt.received_amount
FROM expected_payments ep
LEFT JOIN bank_transactions bt ON ep.invoice_number = bt.invoice_number

UNION

SELECT ep.invoice_number, ep.amount, bt.received_amount
FROM expected_payments ep
RIGHT JOIN bank_transactions bt ON ep.invoice_number = bt.invoice_number;

This produces essentially the same result as a single FULL OUTER JOIN, but it’s more verbose, runs the join logic twice, and requires UNION (not UNION ALL) to avoid duplicating the matched rows that both the LEFT JOIN and RIGHT JOIN would independently return. Once I actually understood FULL OUTER JOIN, I never wrote this workaround again — it’s strictly worse in every way once you know the direct syntax.

A Second Worked Example: Inventory Reconciliation

Reconciliation problems come up constantly in real systems, so let’s walk through a second scenario: comparing a warehouse management system’s recorded stock counts against a physical count taken during an audit.

CREATE TABLE system_inventory (
    sku VARCHAR(20),
    recorded_quantity INT
);

CREATE TABLE physical_count (
    sku VARCHAR(20),
    counted_quantity INT
);

INSERT INTO system_inventory (sku, recorded_quantity) VALUES
('SKU-100', 50), ('SKU-200', 30), ('SKU-300', 12);

INSERT INTO physical_count (sku, counted_quantity) VALUES
('SKU-100', 48), ('SKU-200', 30), ('SKU-400', 5);

SELECT
    COALESCE(si.sku, pc.sku) AS sku,
    si.recorded_quantity,
    pc.counted_quantity,
    COALESCE(pc.counted_quantity, 0) - COALESCE(si.recorded_quantity, 0) AS discrepancy
FROM system_inventory si
FULL OUTER JOIN physical_count pc ON si.sku = pc.sku
WHERE si.recorded_quantity IS DISTINCT FROM pc.counted_quantity;

Notice I used IS DISTINCT FROM here instead of a plain !=. This matters because standard comparison operators return NULL (not TRUE) when one side is NULL, which would silently exclude rows where a SKU only exists on one side. IS DISTINCT FROM treats NULL as a comparable value, so it correctly flags SKU-300 (missing from the physical count) and SKU-400 (found but not in the system) as discrepancies, alongside SKU-100‘s quantity mismatch — while correctly leaving out SKU-200, which matched exactly.

Summarizing Reconciliation Results

Beyond listing individual discrepancies, I often need a quick summary count for a dashboard — “how many mismatches are there right now?” rather than the full detail list. This is a natural extension of the same FULL OUTER JOIN pattern, wrapped in an aggregate:

SELECT
    COUNT(*) FILTER (WHERE ep.id IS NULL) AS transactions_without_expected_payment,
    COUNT(*) FILTER (WHERE bt.id IS NULL) AS expected_payments_never_received,
    COUNT(*) FILTER (WHERE ep.id IS NOT NULL AND bt.id IS NOT NULL AND ep.amount != bt.received_amount) AS amount_mismatches,
    COUNT(*) FILTER (WHERE ep.id IS NOT NULL AND bt.id IS NOT NULL AND ep.amount = bt.received_amount) AS clean_matches
FROM expected_payments ep
FULL OUTER JOIN bank_transactions bt ON ep.invoice_number = bt.invoice_number;

This gives me a single-row summary breaking down exactly what kind of discrepancy each row represents — genuinely useful for a monitoring dashboard that just needs to flag “3 mismatches today” without pulling up the full detail table unless someone clicks in to investigate further. I built almost this exact query for a payment reconciliation dashboard once the initial FULL OUTER JOIN detail query was already working, and it took only a few minutes to adapt since the underlying join logic didn’t need to change at all — just the columns wrapped around it.

Common Use Cases

• Data reconciliation: comparing expected vs actual records (payments, inventory counts, shipment logs).
• Finding orphaned records on either side of a relationship.
• Merging two overlapping but not identical datasets while preserving everything from both.
• Auditing migrations: comparing old system data against new system data to catch what didn’t transfer correctly.
• Building comprehensive reports where you need to know about gaps in both directions, not just one.

Handling Duplicate Keys on Either Side

One thing worth planning for before running a FULL OUTER JOIN in production: what happens if the join column isn’t actually unique on one or both sides. If invoice_number appeared twice in expected_payments — say, due to a data entry mistake — the join would produce two rows for that invoice, each matched independently against bank_transactions. This isn’t a bug in FULL OUTER JOIN itself; it’s simply how joins work when the matching column doesn’t uniquely identify a row.

Before running a reconciliation-style FULL OUTER JOIN on real data, I usually run a quick sanity check for duplicate keys first:

SELECT invoice_number, COUNT(*)
FROM expected_payments
GROUP BY invoice_number
HAVING COUNT(*) > 1;

If this returns any rows, I know I need to either fix the underlying data, or explicitly decide how to handle the duplication — for example, by aggregating expected_payments down to one row per invoice before joining, using a GROUP BY subquery, rather than letting the join silently multiply rows I wasn’t expecting.

Adding a Unique Constraint to Prevent Future Issues

If a reconciliation table is genuinely supposed to have one row per key, it’s worth enforcing that at the schema level rather than relying on discipline alone:

ALTER TABLE expected_payments ADD CONSTRAINT unique_invoice_number UNIQUE (invoice_number);

This doesn’t change how FULL OUTER JOIN behaves, but it prevents the underlying data quality issue that would cause unexpected row multiplication in the first place — and it gives you an immediate, clear error the moment someone tries to insert a duplicate, rather than a silently confusing reconciliation report weeks later.

Troubleshooting Tips

My result set is much larger than expected. This usually means your join condition is matching more loosely than intended — check for duplicate keys on either side, since each match combination produces its own row (a classic join fan-out issue).

I’m getting unexpected NULLs in columns I expected to always have data. Remember that in a FULL OUTER JOIN, any column from a table can be NULL if that side didn’t have a match. Always account for this with COALESCE() or explicit NULL checks downstream.

Query is slow on large tables. Confirm the join columns are indexed, and check EXPLAIN ANALYZE to see whether PostgreSQL is choosing a hash join, merge join, or (worst case) a nested loop — nested loops on unindexed large tables are a common cause of slow full outer joins.

I only wanted the unmatched rows but got everything. Don’t forget the WHERE ep.id IS NULL OR bt.id IS NULL filter if you specifically want discrepancies rather than the full combined dataset.

Best Practices I Follow

1. Index your join columns — this matters more with full outer joins since PostgreSQL has extra bookkeeping to do on both sides.
2. Use COALESCE() to merge key identifying columns into a single clean output column.
3. Filter for unmatched rows explicitly with IS NULL checks when reconciliation is the actual goal.
4. Test join chains incrementally when combining more than two tables with full outer joins.
5. Always run EXPLAIN ANALYZE on full outer joins over large tables before shipping to production.
6. Be explicit about which table’s ID you’re checking for NULL — using the wrong table’s key column in your filter condition is an easy mistake to make.

Frequently Asked Questions

What’s the difference between FULL OUTER JOIN and FULL JOIN? Nothing — FULL JOIN is simply shorthand for FULL OUTER JOIN in PostgreSQL. Both keywords do exactly the same thing.

Can I use FULL OUTER JOIN without an ON condition? Technically no, PostgreSQL requires a join condition for FULL OUTER JOIN (unlike a CROSS JOIN, which doesn’t need one). If you truly want every combination of rows from both tables, use CROSS JOIN instead.

Does FULL OUTER JOIN work with more than two tables? Yes, you can chain multiple full outer joins, though the resulting row combinations can grow quickly and become harder to reason about — test incrementally.

Is FULL OUTER JOIN supported in every PostgreSQL version? Yes, it’s part of standard SQL and has been supported in PostgreSQL for a very long time — no version concerns here.

When should I use FULL OUTER JOIN instead of two separate LEFT JOIN queries combined with UNION? They can produce similar results, but FULL OUTER JOIN is simpler to write, easier to read, and generally more efficient than manually combining a LEFT JOIN and RIGHT JOIN with UNION.

Does FULL OUTER JOIN work with GROUP BY? Yes, you can group and aggregate a FULL OUTER JOIN result exactly like any other join, though remember that unmatched rows will contribute NULLs to any columns from the missing side, which can affect aggregate calculations like SUM() or AVG() if you’re not accounting for them with COALESCE().

Is there a performance difference between FULL OUTER JOIN and doing two LEFT JOINs with UNION? Yes, FULL OUTER JOIN is generally more efficient, since PostgreSQL can compute it in a single coordinated pass rather than running the underlying join logic twice and then deduplicating with UNION afterward. Stick with the native FULL OUTER JOIN syntax whenever it’s available.

Wrapping Up

FULL OUTER JOIN doesn’t come up as often as INNER JOIN or LEFT JOIN in everyday application queries, but when you need it, nothing else does the job as cleanly. Anytime you’re comparing two datasets that are supposed to align but might not — payments and transactions, expected and actual inventory, old and new system records — this is the tool built exactly for that gap-finding work. Once you’ve used it for a real reconciliation task, you’ll immediately recognize the next situation that calls for it.