I’d like to compare two tables and get a set of results where the lookup values are mismatched as well as where the key values are missing from the other table. The first part works fine with the following query:
SELECT * FROM (
SELECT mID, mLookup
FROM m) t1
FULL OUTER JOIN (
SELECT aID, aLookup
FROM a) t2
ON t1.mID = t2.aID
WHERE
t1.mID = t2.aID AND
t1.mLookup <> t2.aLookup
However, it doesn’t return rows from t1 and t2 where there is no corresponding ID in the other table (because of the ON t1.mID = t2.aID).
How can I achieve both in the same query?
Advertisement
Answer
Remove the ID part of the WHERE clause. The FULL OUTER JOIN ON t1.mID = t2.aID is enough to link the tables together. The FULL OUTER JOIN will return both tables in the join even if one does not have a match.
However, the WHERE t1.m_ID = t2.aID clause limits the results to IDs that exist in both tables. This effectively causes the FULL OUTER JOIN to act like an INNER JOIN.
In other words:
SELECT * FROM (
SELECT mID, mLookup
FROM m) t1
FULL OUTER JOIN (
SELECT aID, aLookup
FROM a) t2
ON t1.mID = t2.aID
WHERE
--t1.mID = t2.aID AND -- remove this line
t1.mLookup <> t2.aLookup
— EDIT —
Re-reading your question, you wanted only the mismatches. In that case, you need to search on where either side’s ID is NULL:
SELECT * FROM (
SELECT mID, mLookup
FROM m) t1
FULL OUTER JOIN (
SELECT aID, aLookup
FROM a) t2
ON t1.mID = t2.aID
WHERE
t1.mID IS NULL OR
t2.mID IS NULL OR
t1.mLookup <> t2.aLookup