How do I merge two tables with different column nu

问题:

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.

回答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.

回答2:

Try constraining like follows:

SELECT FirstName, LastName, PhoneNumber FROM Table1
UNION
SELECT FirstName, LastName, Null as PhoneNumber FROM Table2 
WHERE FirstName NOT IN (SELECT FirstName FROM Table1) 
AND LastName NOT IN (SELECT LastName FROM TABLE1);

回答3:

FaddishWorm has a good concept, but the two separate subqueries would eliminate any record with matching firstname OR matching lastname. NOT x AND NOT y = NOT(x OR y). So names like Hernandez and Jim would be omitted from TABLE2.

Try concatenating.

SELECT FirstName, LastName, PhoneNumber FROM Table1
UNION
SELECT FirstName, LastName, Null as PhoneNumber FROM Table2 
WHERE FirstName & Lastname NOT IN (SELECT FirstName & lastname FROM Table1);

There are other solutions. This is slow. HandsUp has the right idea.