Select from multiple tables, remove duplicates

Question

I have two tables in a SQLite DB, and both have the following fields:

idnumber, firstname, middlename, lastname, email, login

One table has all of these populated, the other doesn't have the idnumber, or middle name populated.

I'd LIKE to be able to do something like:

select idnumber, firstname, middlename, lastname, email, login 
from users1,users2 group by login;

But I get an "ambiguous" error. Doing something like:

select idnumber, firstname, middlename, lastname, email, login from users1 
union 
select idnumber, firstname, middlename, lastname, email, login from users2;

LOOKS like it works, but I see duplicates. my understanding is that union shouldn't allow duplicates, but maybe they're not real duplicates since the second user table doesn't have all the fields populated (e.g. "20, bob, alan, smith, [email protected], bob" is not the same as "NULL, bob, NULL, smith, [email protected], bob").

Any ideas? What am I missing? All I want to do is dedupe based on "login".

Thanks!

Answer 1

As you say union will remove duplicate records (note that union all won't!). Two records are considered duplicates when all their column values match. In the example you considered in your question it is clear that NULL is not equal to 20 or 'alan' so those records won't be considered duplicates.

Edit:

[...] the only way I can think would be creating a new table [...]

That is not necessary. I think you can do the following:

select login, max(idnumber), max(firstname), max(middlename), max(lastname),
  max(email) from (
    select idnumber, firstname, middlename, lastname, email, login from users1 
    union 
    select idnumber, firstname, middlename, lastname, email, login from users2
) final
group by login

However, if you're sure that you only have different values on idnumber and middlename you can max only those fields and group by all the rest.