How to find out unmatched column values between two tables.

• asita

  SSCarpal Tunnel

  Points: 4144

  More actions

  March 21, 2018 at 2:51 pm

  #327991

  Create Table #tab1 (SSN varchar(9), Email varchar(50))

  INSERT INTO #tab1 Values('123456789','abc@email.com')
  INSERT INTO #tab1 Values('123456789','bcd@email.com')
  INSERT INTO #tab1 Values('123456789','cde@email.com')

  INSERT INTO #tab1 Values('123456780','a1bc@email.com')
  INSERT INTO #tab1 Values('123456780','b1cd@email.com')

  INSERT INTO #tab1 Values('123456781','abc1@email.com')
  INSERT INTO #tab1 Values('123456781','bcd1@email.com')
  INSERT INTO #tab1 Values('123456781','cde1@email.com')

  INSERT INTO #tab1 Values('123456782','cde1@email.com')

  SELECT * FROM #TAB1

  Create Table #tab2 (SSN varchar(9), Email varchar(50) )

  INSERT INTO #tab2 Values('123456789','abc@email.com')
  INSERT INTO #tab2 Values('123456789','bcd@email.com')

  INSERT INTO #tab2 Values('123456780','a1bc@email.com')
  INSERT INTO #tab2 Values('123456780','b1cd@email.com')

  INSERT INTO #tab2 Values('993456780','aabc@email.com')
  INSERT INTO #tab2 Values('993456780','bbcd@email.com')

  INSERT INTO #tab2 Values('883456780','zaabc@email.com')
  INSERT INTO #tab2 Values('113456780','zbbcd@email.com')

  SELECT * FROM #TAB2

  How to find out which sss exist in first table but not in second table
  How to find out which sss exist in second table but not in first table

  How to find out ssn that are matched in both tables and email addresses for those ssns matched
  How to find out ssn that are matched in both tables but email address were not matched

  Please advise, I used the left, right join for first two questions but checking 3 & 4 also any better way for 1&2 also

  Thank you a ton in advnace
  Best Regards
  Asita

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  March 21, 2018 at 3:29 pm

  #1984200

  You could use EXISTS, LEFT JOIN... what did you try?

• asita

  SSCarpal Tunnel

  Points: 4144

  More actions

  March 23, 2018 at 9:13 am

  #1984424

  I used left join t find out only SSN. but not sure how to do for emails any idea? or query? please advise

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  March 23, 2018 at 9:26 am

  #1984426

  SELECT * FROM #TAB1 EXCEPT SELECT * FROM #TAB2

  SELECT * FROM #TAB2 EXCEPT SELECT * FROM #TAB1

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Chris Harshman

  SSC-Forever

  Points: 42192

  More actions

  March 23, 2018 at 11:55 am

  #1984442

  if you want to find it all in one pass, you could try something like this:
  SELECT t1.SSN, t1.Email, t2.SSN, t2.Email,
  CASE
  WHEN t2.SSN IS NULL THEN 'not in T2'
  WHEN t1.SSN IS NULL THEN 'not in T1'
  WHEN ISNULL(t1.Email,'~') <> ISNULL(t2.Email,'~') THEN 'same SSN different e-mail'
  ELSE 'both columns match'
  END AS msg
FROM #tab1 t1
FULL OUTER JOIN #tab2 t2 ON t1.SSN = t2.SSN;