Finding data that's missing from the database

Question

I need to figure out some clever MySQL snippet that will allow me to easily see two tables, the ids in the table if they exist or NULL or empty if they don't exist.

I have a users table and a legacy table and outside of manual comparison I can't figure out how to make them appear in a table together so I can compare. What I would love to see is something like this:

+----------------------------+
| user_id | email     | uid  |
| 14      | [email protected] | 26   |
| 16      | [email protected] | NULL |
+----------------------------+

I know there's a way to include NULL or empty values but I'm not sure what it is. Here's my deranged SQL query so far, yes, I know it's horrible to do subselects inside of subselects:

select uid from users where mail IN (
    select email from legacy_users where id NOT IN (
        select sourceid from migrate_map_users
    )
);

There are three tables involved here, legacy_users => migrate_map_users => users. The middle is just an m2m which joins the two. legacy_users and users both have an email column. and their own version of an id.

Thank you all!

Answer 1

??

select u.uid, u.mail, l.email, l.id
from users u
left outer join legacy_users
    on u.mail = l.email

-- two queries to get you going

select u.uid, u.mail, l.email, l.id
from users u
left outer join legacy_users
    on u.mail = l.email
Where l.id is null

select l.email, l.id, u.uid, u.mail
from legacy_users l
left outer join users u
    on l.email = u.mail
Where u.uid is null

Answer 2

You need to learn about join types, in particular left and outer joins:

SELECT u.uid, u.mail, lu.id
FROM users u
LEFT OUTER JOIN legacy_users lu 
    ON u.email = lu.mail
WHERE lu.id NOT IN
   (
        SELECT sourceid 
        FROM migrate_map_users
    );

The LEFT OUTER JOIN will make sure all records in the LEFT table will be returned, whether there is a corresponding one in the right one or not.

Answer 3

Thanks to Oded's answer this is what I ended up with:

SELECT * 
FROM (
  SELECT id, mail, uid 
  FROM users  
  LEFT OUTER JOIN 
    legacy_users lu ON users.mail = lu.email 
  UNION DISTINCT 
  SELECT id, email, uid 
  FROM users  
  RIGHT OUTER JOIN 
    legacy_users lu ON users.mail = lu.email
) j 
WHERE uid IS NULL 
OR id IS NULL;

This also allowed me to do a where on the results. Bonus.

Note that it's using mail in the left join and email in the right join. Since mail wouldn't exist in the right outer join we have to use the email column from legacy_users and vice versa.