Solving tips
- Set operators (UNION, INTERSECT, EXCEPT) match on the full row and, unlike their ALL variants, deduplicate automatically.
- A symmetric difference is (A EXCEPT B) UNION (B EXCEPT A) — the emails in one set but not the other, from both directions.
- Parenthesize each EXCEPT branch, and put the final ORDER BY after the last query so it applies to the combined result.
You have two yearly signup lists. Find the emails that appear in one year but not the other (the symmetric difference).
Schema
CREATE TABLE signups_2023 (email TEXT);
CREATE TABLE signups_2024 (email TEXT);
Sample data:
signups_2023
signups_2024
Task
Return the single column email for every address that appears in exactly one of the two tables (present in 2023 but not 2024, or present in 2024 but not 2023). Order by email ascending.
Expected output
Approach
The symmetric difference of two sets is “in A but not B” combined with “in B but not A.” EXCEPT gives each one-directional difference and UNION merges them; both operators deduplicate, so the result is a clean set. This is more direct and readable than an equivalent FULL OUTER JOIN with NULL checks.
Query
(SELECT email FROM signups_2023
EXCEPT
SELECT email FROM signups_2024)
UNION
(SELECT email FROM signups_2024
EXCEPT
SELECT email FROM signups_2023)
ORDER BY email;
Walkthrough
The first branch, signups_2023 EXCEPT signups_2024, drops carol and dave (they exist in both) and keeps alice and bob. The second branch, signups_2024 EXCEPT signups_2023, drops carol and dave and keeps erin and frank. UNION combines the two disjoint results into alice, bob, erin, frank, and the trailing ORDER BY email sorts them alphabetically.
Complexity & notes
Each operator hashes or sorts its inputs, roughly O(n log n) or O(n) with hashing. Pitfalls: EXCEPT (like INTERSECT and plain UNION) removes duplicates, whereas EXCEPT ALL / UNION ALL keep multiplicity, so pick the ALL variants only when counts matter. Set operators match on every selected column and require the same column count and compatible types across branches. ORDER BY must come once at the very end and cannot sit inside a parenthesized branch. Dialect note: PostgreSQL supports EXCEPT; MySQL added it in 8.0.31, and older MySQL requires a LEFT JOIN ... WHERE ... IS NULL or NOT IN rewrite. NULLs compare as equal under set operators, unlike in NOT IN, which is another reason to prefer EXCEPT here.