sql - How do I merge two tables with different column number while removing duplicates?

Question

I have two tables in Access database. Table1 has more columns than Table2. I would like to merge those tables into one while removing duplicates. I have the following query

SELECT FirstName, LastName, PhoneNumber FROM Table1
UNION
SELECT FirstName, LastName, Null as PhoneNumber  FROM Table2

Problem is, I don't want to copy any entry from Table2 that has the same FirstName and LastName in Table1. How can I change the above query to accomplish that? Thanks in advance.

Answer 1

Start with a query which returns only those Table2 rows which are not matched in Table1.

SELECT t2.FirstName, t2.LastName
FROM
    Table2 AS t2
    LEFT JOIN Table1 AS t1
    ON 
            t2.FirstName = t1.FirstName
        AND t2.LastName = t1.LastName
WHERE t1.FirstName Is Null;

Then use that SELECT in your UNION query.

SELECT FirstName, LastName, PhoneNumber FROM Table1
UNION ALL
SELECT t2.FirstName, t2.LastName, t2.Null AS PhoneNumber
FROM
    Table2 AS t2
    LEFT JOIN Table1 AS t1
    ON 
            t2.FirstName = t1.FirstName
        AND t2.LastName = t1.LastName
WHERE t1.FirstName Is Null;

Note I used UNION ALL because it requires less work by the db engine so is therefore faster. Use just UNION when you want the db engine to weed out duplicate rows. But, in this case, that would not be necessary ... unless duplicates exist separately within one or both of those tables.