FULL OUTER JOIN
Overview
A FULL OUTER JOIN is the combination of a Left Join AND a Right Join simultaneously. It is the ultimate preserver of data. It grabs every single row from Table A, and every single row from Table B. Where they match, it fuses them together. Where Table A is missing data, it pads with NULLs. Where Table B is missing data, it pads with NULLs. Absolutely nothing is deleted.
Syntax
-- Syntax: FULL OUTER JOIN
SELECT
c.company_name,
s.software_license
FROM companies c
FULL OUTER JOIN subscriptions s
ON c.id = s.company_id;
-- The Output Possibilities:
-- 1. Match: Shows Company Name + License
-- 2. Left Orphan: Shows Company Name + NULL (Company has no license)
-- 3. Right Orphan: Shows NULL + License (License is floating/unassigned)Common Pitfalls
- Using
FULL OUTER JOINcasually. It is extremely rare in day-to-day web development because standard relational schemas (like Users and Orders) heavily favorLEFT JOIN.FULL OUTER JOINis mostly utilized in Data Warehousing or when attempting to merge two completely disparate datasets (like a list of Employees from an acquired company and a list of internal IT accounts) to find overlaps and gaps. - MySQL doesn't support it! Unlike PostgreSQL and Oracle, MySQL natively lacks a
FULL OUTER JOINcommand. You must manually emulate it by running aLEFT JOIN, aRIGHT JOIN, and merging them together withUNION.
Interview Questions
It is a query designed to find strictly the orphans on BOTH sides. You run a FULL OUTER JOIN, and add WHERE table_a.id IS NULL OR table_b.id IS NULL. It returns everything that completely failed to match.
Real-World Example
A Data Engineering script identifying synchronization bugs between a third-party CRM and the internal database.
SELECT
internal.email AS internal_db_email,
hubspot.email AS crm_email
FROM internal_users internal
FULL OUTER JOIN hubspot_contacts hubspot
ON internal.email = hubspot.email
-- Find the sync bugs! (Users missing in one system or the other)
WHERE internal.email IS NULL OR hubspot.email IS NULL;Check Your Knowledge
Test your understanding of FULL OUTER JOIN with these quick questions.