Emulating FULL OUTER JOIN When Native Support Is Missing

1. What a FULL OUTER JOIN actually returns

A FULL OUTER JOIN combines the behavior of LEFT JOIN and RIGHT JOIN at the same time: every row from the left table appears in the result regardless of whether a matching row exists in the right table, and likewise every row from the right table appears regardless of whether a matching row exists in the left table. Where a counterpart is missing on one side, the corresponding columns are filled with null, exactly as with a plain LEFT or RIGHT JOIN.

Typical use cases are data reconciliation between two systems, for example comparing an inventory list in an ERP system against an inventory list in a warehouse management system, where both entries that only exist in one system and entries that only exist in the other system need to become visible. A plain INNER JOIN would silently swallow exactly the most interesting cases in this scenario, namely the discrepancies between the two systems.

-- Native FULL OUTER JOIN, where available
SELECT
    erp.product_id AS erp_product_id,
    wms.product_id AS wms_product_id,
    erp.stock_quantity AS erp_quantity,
    wms.stock_quantity AS wms_quantity
FROM erp_inventory erp
FULL OUTER JOIN wms_inventory wms
    ON erp.product_id = wms.product_id;

2. Basic emulation principle: LEFT JOIN plus RIGHT JOIN via UNION

When native FULL JOIN support is missing, the same effect can be achieved by combining two simpler joins that are available practically everywhere. A LEFT JOIN returns every row of the left table, including non matching ones padded with null, and a second RIGHT JOIN, or equivalently a LEFT JOIN with the table order swapped, correspondingly returns every row of the right table. Combining both result sets with UNION produces exactly the same result overall as a genuine FULL OUTER JOIN.

Using UNION instead of UNION ALL is essential here, because rows where a matching counterpart exists on both sides otherwise appear twice: once from the LEFT JOIN and a second, identical time from the RIGHT JOIN. UNION automatically removes these duplicates, provided the result rows are actually fully identical, which is the normal case with cleanly defined column lists.

-- Emulated FULL OUTER JOIN via LEFT JOIN + RIGHT JOIN + UNION
SELECT
    erp.product_id AS erp_product_id,
    wms.product_id AS wms_product_id,
    erp.stock_quantity AS erp_quantity,
    wms.stock_quantity AS wms_quantity
FROM erp_inventory erp
LEFT JOIN wms_inventory wms ON erp.product_id = wms.product_id

UNION

SELECT
    erp.product_id AS erp_product_id,
    wms.product_id AS wms_product_id,
    erp.stock_quantity AS erp_quantity,
    wms.stock_quantity AS wms_quantity
FROM erp_inventory erp
RIGHT JOIN wms_inventory wms ON erp.product_id = wms.product_id;

3. Alternative: LEFT JOIN combined with LEFT JOIN and NOT EXISTS

On databases without any RIGHT JOIN support at all, or when RIGHT JOIN is avoided for team style reasons, the same emulation can also be formulated using only LEFT JOIN plus an additional NOT EXISTS condition. The first part returns all rows of the left table as before, including matches and non matches. The second part returns exclusively the rows of the right table for which explicitly no matching row exists in the left table.

This variant has a practical advantage over the plain union of two joins: since the second part only ever returns the genuinely non matching rows of the right table from the start, no duplicate removal is needed anymore, UNION ALL instead of UNION suffices and saves the additional sort or hash step for duplicate detection, which can add up noticeably on large result sets.

-- Emulation without RIGHT JOIN, using UNION ALL instead of UNION
SELECT
    erp.product_id AS erp_product_id,
    wms.product_id AS wms_product_id,
    erp.stock_quantity AS erp_quantity,
    wms.stock_quantity AS wms_quantity
FROM erp_inventory erp
LEFT JOIN wms_inventory wms ON erp.product_id = wms.product_id

UNION ALL

SELECT
    NULL AS erp_product_id,
    wms.product_id AS wms_product_id,
    NULL AS erp_quantity,
    wms.stock_quantity AS wms_quantity
FROM wms_inventory wms
WHERE NOT EXISTS (
    SELECT 1 FROM erp_inventory erp
    WHERE erp.product_id = wms.product_id
);

4. Practical case: MySQL before FULL JOIN support

MySQL went without any native FULL JOIN syntax for a very long time, and even in current versions support remains more limited compared to PostgreSQL, SQL Server, and Oracle, where FULL OUTER JOIN has long been part of the standard repertoire. In production MySQL environments that need to reconcile data between two tables, the UNION emulation is therefore a fixed part of every experienced development team's toolkit, regardless of whether it stems from a legacy system or a deliberate technology choice.

Other database systems, especially older or embedded variants such as early SQLite versions, also went without native FULL JOIN syntax for a long time. Anyone writing code intended to run across several database systems with different feature sets should therefore plan for the emulation as the portable default, rather than relying on native FULL JOIN availability that simply is not guaranteed depending on the target system.

5. Correctness pitfall: WHERE conditions in the wrong place

One of the most common sources of error in the emulation happens when additional filter conditions accidentally end up in a WHERE clause instead of the JOIN condition itself. A WHERE condition on a column of the right table inside the first LEFT JOIN part unintentionally filters out exactly the rows that were supposed to survive as null rows, because a WHERE clause is evaluated after the join and null values evaluate to unknown rather than true in most comparison operators.

The correct solution is to write every business filter condition that applies to a single table, while still needing non matching rows to survive, directly into the ON clause of the respective join instead of a downstream WHERE clause. Only conditions that genuinely need to restrict both result sets of the UNION together belong in a WHERE clause applied to the overall UNION result.

-- Wrong: WHERE unintentionally filters out null rows
SELECT erp.product_id, wms.product_id, wms.stock_quantity
FROM erp_inventory erp
LEFT JOIN wms_inventory wms ON erp.product_id = wms.product_id
WHERE wms.warehouse_code = 'MAIN';  -- drops rows without a WMS match!

-- Correct: move the filter condition into the ON clause
SELECT erp.product_id, wms.product_id, wms.stock_quantity
FROM erp_inventory erp
LEFT JOIN wms_inventory wms
    ON erp.product_id = wms.product_id
    AND wms.warehouse_code = 'MAIN';

6. Performance pitfall: the base tables get read twice

A structural downside of any UNION based emulation is that both base tables effectively get scanned or read via index twice, once in the LEFT JOIN part and once in the RIGHT JOIN or NOT EXISTS part. On small to medium tables this extra work barely registers, but on very large fact tables in the tens of millions of rows it can noticeably add to the overall runtime, especially when additional complex filter conditions or aggregations build on top of the emulation's result.

In such cases it is worth checking whether the original use case can be reformulated to avoid this altogether, for example by splitting the reconciliation into two separate, independently executable and possibly parallel queries instead of insisting on a single combined result in one query. Where a single result set is genuinely required, a close look at the indexes in use helps, since both parts of the UNION should benefit from the same indexes on the join column.

7. UNION versus UNION ALL: which variant is correct when

In the classic emulation with two full joins, LEFT JOIN and RIGHT JOIN, UNION is mandatory, because both subqueries fully return the same, genuinely matching rows and those duplicates would otherwise appear twice in the final result without duplicate removal. Accidentally using UNION ALL here is one of the most common mistakes in the emulation and leads to a silent but factually wrong result with duplicate rows.

In the variant using NOT EXISTS in the second part, UNION ALL is not only permitted but the better choice, since the NOT EXISTS condition already guarantees that no overlap between the two partial results can occur. UNION ALL avoids the unnecessary duplicate detection step in this case and is therefore generally the more performant of the two emulation variants presented here.

8. Do not forget column order and data type compatibility

As with any UNION operation, both subqueries must return exactly the same number of columns in the same order with compatible data types. A common mistake in FULL JOIN emulation happens when the column order in the second part of the UNION accidentally gets swapped, because the table order in the FROM clause was reversed relative to the first part. The result stays syntactically valid, but returns values incorrectly assigned to the wrong columns from a business logic perspective.

A proven safeguard against this mistake is to write the column list in both parts of the UNION explicitly with the same alias names in the same order, instead of relying on implicit positional matching. A subsequent test run with deliberately constructed test data that includes both matches and rows only on one side reliably uncovers swap errors of this kind before they reach a production report.

9. Building a unified key column with COALESCE

Both the native FULL OUTER JOIN and every emulation produce the same practical follow up problem: the result contains two separate key columns, one from the left and one from the right table, where exactly one of the two can be null depending on the row. For downstream processing, such as a further GROUP BY aggregation or a display in a reconciliation report, a single, consistently populated key column is noticeably more practical than two parallel, partially empty columns.

The COALESCE function offers exactly the simplest solution for this: it returns the first non null value from a list of arguments, so COALESCE over the left and right key columns reliably returns the value that actually exists in every row, regardless of which of the two source tables the row originated from. This unified column then works seamlessly as a GROUP BY key or a display column, without the calling application needing to know which of the two source tables originally supplied that particular record.

SELECT
    COALESCE(erp.product_id, wms.product_id) AS unified_product_id,
    erp.stock_quantity AS erp_quantity,
    wms.stock_quantity AS wms_quantity,
    CASE
        WHEN erp.product_id IS NULL THEN 'WMS only'
        WHEN wms.product_id IS NULL THEN 'ERP only'
        ELSE 'in both systems'
    END AS reconciliation_status
FROM erp_inventory erp
LEFT JOIN wms_inventory wms ON erp.product_id = wms.product_id

UNION ALL

SELECT
    COALESCE(erp.product_id, wms.product_id),
    erp.stock_quantity,
    wms.stock_quantity,
    'WMS only'
FROM wms_inventory wms
LEFT JOIN erp_inventory erp ON erp.product_id = wms.product_id
WHERE erp.product_id IS NULL;

10. Summary

11. FAQ: Emulating FULL OUTER JOIN: Key Takeaways