How to write SQL query to pull only not matching Email IDs

Question

I need to write a query to pull records which Email IDs are not matching in another table.

For example, I have a two tables whose structures looks something like this:

Table1
    ID    | Name    | Email ID
    101   | TOM     | TOM@gmail
    102   | KIM     | KIM@gmail
    103   | JIM     | JIM@gmail
    104   | RIM     | RIM@gmail
    105   | SIM     | SIM@gmail 

Table2
     ID   | Name    | Email ID
    101   | TOM     | TOMMOT@gmail
    102   | KIM     | KIM@gmail
    103   | JIM     | JIMMIJ@gmail
    104   | RIM     | RIM@gmail
    105   | SIM     | SIM@gmail `

So, I need to Pull only

    ID    | Name    | Email ID
    101   | TOM     | TOMMOT@gmail
    103   | JIM     | JIMMIJ@gmail

Answer 1

One way to do this is to leverage LEFT JOINS and subqueries

SELECT Id, Name, EmailId
FROM   (SELECT TABLE2.Id AS 'Id', TABLE2.Name AS 'Name', TABLE2.EmailId AS 'EmailId', TABLE1.Id AS 'SecondId'
        FROM   TABLE2
               LEFT JOIN TABLE1
                 ON TABLE2.EmailId = TABLE1.EmailID) AS myTable
WHERE  SecondId = Null

I have never actually written SQL against Automation Studio but this SQL statement is valid in T-SQL (MS SQL Server)

Answer 2

You can also do it without explicit JOINs, if you don't need to return any values from the other Data Extension.

select
  t2.ID
, t2.Name
, t2.[Email ID]
from Table2 t2
where not exists (
  select top 1 t1.*
  from Table1 t1
  where t1.[Email ID] = t2.[Email ID]
)

Answer 3

Thanks for your input, this query worked for me as I expected

select t2.ID , t2.Name , t2.[Email ID] from Table2 t2 where not exists ( select top 1 t1.* from Table1 t1 where t1.[Email ID] = t2.[Email ID] )