how to find items which are not in a table

If I have two tables as follows

Table_1
SocialSecurityNumber
FirstName

Table_2
SocialSecurityNumber
Purchase
Date

Suppose I want to find all records in Table_2 except for those which have an identical SocialSecurityNumber in Table_1 ?
LVL 2
brgdotnetcontractorAsked:
Who is Participating?
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.

Maheshvaran_PadiachiCommented:
Hi,

The following code will do it.
It uses a sub query to exclude SSN's from the 1st table.

SELECT *
FROM Table_2
WHERE SocialSecurityNumber NOT IN (
SELECT DISTINCT(SocialSecurityNumber)
FROM Table_1
)

Open in new window


Do let me know if does not make sense?

Regards,
0

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
lluddenCommented:
You can also do a Left outer join to accomplish this.

SELECT T2.*
FROM Table2 T2
   LEFT OUTER JOIN Table1 T1 ON T2.SocialSecurityNumber = T1.SocialSecurityNumber
WHERE T1.SocialSecurityNumber IS NULL
0
awking00Information Technology SpecialistCommented:
A couple more ways -

select * from table_2 t2
where not exists
(select 1 from table_1 t1
 where t2.ssn = t1.ssn)

select * from table_2
where ssn in
(select ssn from table_2
 except
 select ssn from table_1)
0
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.