Compare two tables, find missing rows and mismatched data

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?

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