CODEWITHSUNDEEP
sqlhivedifference
DSSD
DSSD

Reputation: 39

Find the difference between 2 tables

I have 2 tables and I would like to find the difference between the 2 tables based on email

Table 1 is a history table and has duplicates for emails address but Table 2 has been de-duped for email ( has no duplicates).

Table 1

    Email.           validity.   recency.  last Modified
    [email protected]     1                0-3.    1/21/2020
    [email protected]     0                12-16.   1/22/2020
    [email protected].     1               NULL     12/20/2019
    [email protected]      1               12-16     11/30/2018
    [email protected].     1                0-3    1/18/2020

Table 2

    Email.           validity   recency     last Modified type  partner 
    [email protected]     1                0-3    1/21/2020.    C.    Goo
    [email protected].    0.               12-16.  1/22/2020.    P.   Azz
    [email protected]     1                0-3    1/18/2020.    P.    Goo

Expected Output

    Email.           validity.   recency.  last Modified
    [email protected]     1            0-3      1/18/2020

I would like to find the list of all emails that are present in Table 1 but not table 2. In my understanding, join can only be used to find a subset/similarity. How can I find differences between the 2 tables? Thanks.

Upvotes: 0

Views: 2743

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

If I understand correctly, you just want not exists:

select t1.*
from table1 t1
where not exists (select 1
                  from table2 t2
                  where t2.email = t1.email
                 );

You can use validity in the logic if that is appropriate. But the description of the problem does not mention any conditions other than email.

Upvotes: 0

Koushik Roy
Koushik Roy

Reputation: 7407

You can use NOT IN based on key columns. Assuming Email as key column here is sample SQL.

SELECT distinct * FROM TABLE1 
WHERE 
validity=1 AND
Email 
NOT IN (
SELECT Email FROM TABLE2 WHERE validity=1
)

You can use join as well as you mentioned. In this case just select those that are absent from Table2 and viola you will get non existent records.

SELECT *
FROM Table1 t1
LEFT JOIN Table2 t2 ON T1.Email = T2.Email
WHERE  T2.Email IS NULL

Upvotes: 1

Related Questions