How do you emulate a FULL OUTER JOIN on an engine that supports only LEFT and RIGHT joins?

## Why you would need this `FULL OUTER JOIN` is standard SQL, but not every engine implements it — MySQL notably does not, and SQLite only gained it in version 3.39. Since the operation is definable in terms of joins already available, the workaround is mechanical, and interviewers use it to check that you can decompose a join type into its parts. ## The decomposition A full outer join is three disjoint sets of rows: matched pairs, left-only rows NULL-extended on the right, and right-only rows NULL-extended on the left. A `LEFT JOIN` already delivers the first two. So the rewrite is: ``` (LEFT JOIN result) UNION ALL (right-only rows) ``` and the only work is expressing "right-only rows" without a full join. That is an anti-join: run the join with the right side preserved, then keep just the rows where the left side came back empty. ```sql SELECT e.id AS emp_id, e.name AS emp_name, d.id AS dept_id, d.name AS dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.id UNION ALL SELECT e.id, e.name, d.id, d.name FROM employees e RIGHT JOIN departments d ON e.dept_id = d.id WHERE e.id IS NULL; ``` If you would rather avoid `RIGHT JOIN` entirely, flip the tables and keep the projection order the same: ```sql ... UNION ALL SELECT e.id, e.name, d.id, d.name FROM departments d LEFT JOIN employees e ON e.dept_id = d.id WHERE e.id IS NULL; ``` Both forms are equivalent. The second is often preferred stylistically, because a query that mixes `LEFT` and `RIGHT` joins is harder to read. ## Three ways to get it wrong **Forgetting the anti-join filter.** Without `WHERE e.id IS NULL`, the second branch returns the matched rows a second time, so every matched pair appears twice. This is the most common bug in the rewrite, and it is invisible until someone sums a column. **Testing the wrong column, or a nullable one.** The filter must ask "did the left side fail to match?", so it has to test a column that is guaranteed to be populated whenever a left row was found — the primary key is the safe choice. Testing a nullable attribute such as `e.manager_id IS NULL` would also pass matched rows whose manager is unknown, pulling duplicates back in. Testing the wrong side (`WHERE d.id IS NULL` in the branch that preserves `departments`) yields nothing useful at all, because the preserved side is never NULL-extended. **Using `UNION` instead of `UNION ALL`.** `UNION` removes duplicate rows across the whole combined result. If two employees share every projected column value, the real full outer join returns both and `UNION` returns one. It also forces the engine to do extra deduplication work you do not want. Since the branches are already disjoint by construction, `UNION ALL` is both correct and cheaper. ## Column-list discipline Set operators combine branches positionally, not by name: the first column of branch one lines up with the first column of branch two regardless of what they are called, and the types must be compatible. Column names in the final result come from the first branch, which is why the aliases sit there. `SELECT *` in both branches is a trap — `FROM departments d LEFT JOIN employees e` produces a different column order from `FROM employees e LEFT JOIN departments d`, and a positional mismatch may not even raise an error if the types happen to line up. Always list columns explicitly. ## Reconstructing a unified key Since the emulation still emits two key columns, apply the same `COALESCE(e.dept_id, d.id)` treatment you would apply to a real full outer join, and do it identically in both branches so the combined result is consistent. ## A variant worth knowing An alternative decomposition is inner join plus both anti-join branches: matched rows from an `INNER JOIN`, left-only rows from `LEFT JOIN ... WHERE right.pk IS NULL`, right-only rows from the mirror image, all combined with `UNION ALL`. It is three branches instead of two and touches the tables three times, so it is rarely the first choice — but it makes the three-bucket structure explicit, and it is handy when each bucket needs a different projection, for example a status label. ## Reading it back When you meet this pattern in an unfamiliar codebase, recognise it for what it is: a `LEFT JOIN` plus an anti-joined mirror is a full outer join written by hand, usually because the engine lacked one. On an engine that does support `FULL OUTER JOIN`, collapse it back — one statement is clearer and scans each table once.