CODEWITHSUNDEEP
mysqldatabasesubquery
Stangn99
Stangn99

Reputation: 117

Select records that don't exist in another DB table

I'm trying to tackle what I thought was a simple query.

I have two databases each with one table in the DB.

What I would like to do is find all of the emails from DB1.Table that don't exist in DB2.Table

I'm using this query, but the result is incorrect because I know DB1.Table contains emails that don't exist in DB2.Table (result always comes back as 0)

SELECT DB1.20180320.email FROM DB1.20180320 WHERE DB1.20180319.email NOT IN (SELECT DB2.20180319.email FROM DB2.20180319 WHERE Status = 'active')

Any ideas on what I'm doing wrong here? I'm working with about 80k rows in each table.

Thanks.

Upvotes: 1

Views: 1281

Answers (2)

Eric
Eric

Reputation: 3257

NOT EXISTS query should do it. It returns email that exist in DB1, but not DB2.

SELECT DB1.20180320.email
FROM DB1.20180320 
WHERE NOT EXISTS(
    SELECT 1 
    FROM DB2.20180319 
    WHERE DB1.20180320.email = DB2.20180319.email 
        AND DB2.20180319.Status = 'active'
)

Upvotes: 0

Brian Hoover
Brian Hoover

Reputation: 7991

without seeing your data, try something like this.

SELECT DB1.20180320.email
   FROM DB1.20180320 
   left join DB2.20180319 on DB1.20180320.email = DB2.20180319.email 
           AND DB2.20180319.Status = 'active'
   WHERE DB2.20180319.email IS null;

This should show all the emails in DB1.20180320 that don't exist in DB2.20180319

Upvotes: 2

Related Questions