Symmetric Difference Pattern
What you'll learn: How to find rows that exist in either table but not in both—the "exclusive or" of data.
What Is the Symmetric Difference?
Imagine two overlapping circles in a Venn diagram. The symmetric difference is everything except the middle overlap—only the parts unique to each circle.
In SQL, you achieve this by using a FULL OUTER JOIN and then filtering out the matched rows. You keep only the rows where one side has NULL values, meaning that row exists in one table but not the other.
How It Works
After performing a FULL OUTER JOIN, you'll have:
- Rows matched in both tables (no NULLs)
- Rows only in the left table (right side has NULLs)
- Rows only in the right table (left side has NULLs)
To get the symmetric difference, filter to keep only rows where one side is NULL:
SELECT *
FROM table_a
FULL OUTER JOIN table_b ON table_a.id = table_b.id
WHERE table_a.id IS NULL OR table_b.id IS NULL;
This pattern is powerful for data reconciliation—finding discrepancies between two datasets, like customers in your CRM but not in your billing system, or vice versa.
Real-World Use Case
You might compare last month's active users against this month's to identify who started or stopped using your product—but not those who remained active both months.
Key Takeaway: The symmetric difference pattern uses FULL OUTER JOIN with WHERE ... IS NULL OR ... IS NULL to isolate rows that appear in only one table, perfect for finding mismatches and discrepancies.