Compare Two SQL Tables and Find Missing Data

I have two tables Users and tmp_jasUsers. I need to know which of tmp_JasUsers are NOT in the Users table. The common field is email (email in users and emailAddr in tmp_jasUsers). This one has me stumped! Thanks in advance.
tryportAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
>I need to know which of tmp_JasUsers are NOT in the Users table. The common field is email
Lots of different ways to pull this off, here's a couple

SELECT tju.*
FROM tmp_jasUsers tju
   -- LEFT means select all rows from tju ...
   LEFT JOIN Users u ON u.Email = tju.emailAddr
-- That don't have a matching u 
WHERE u.Email IS NULL

-- or the IN approach

SELECT * FROM tmp_jasUsers WHERE emailAddr NOT IN (SELECT Email FROM Users)

Open in new window

tryportAuthor Commented:
Thank you. I left out a part of my question - or in fairness to you I could ask it separately - how can I also know if there are duplicates (and which ones are duplicates) from the tmp_jasUsers in the user table?
Jim HornMicrosoft SQL Server Data DudeCommented:
Define 'duplicates'.  
   In tmp_jasUsers table more than once?
   In tmp_jasUsers table, and also in Users table once?
   In tmp_jasUsers table, and also in Users table more than once?
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

tryportAuthor Commented:
In the tmp_jasUserTable once and in the User table more than once
Jim HornMicrosoft SQL Server Data DudeCommented:
Ok.  Give this a whirl...

SELECT * 
FROM tmp_jasUsers 
WHERE emailAddr IN (
   SELECT Email
   FROM Users
   GROUP BY Email
   HAVING COUNT(Email) > 1)

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tryportAuthor Commented:
Thanks so much for sticking with me and for all of the help!
Jim HornMicrosoft SQL Server Data DudeCommented:
btw, couple of articles that may interest you, hit the 'Was this article helpful' button if you liked them.
SQL Server Delete Duplicate Rows Solutions, explains how to select/soft delete/hard delete duplicate rows.
SQL Server GROUP BY Solutions, explains the HAVING clause

Thanks for the grade, good luck with your project.  -Jim
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.