CODEWITHSUNDEEP
sqlpostgresqlleft-join
Larry
Larry

Reputation: 33

Eliminate records using a JOIN instead of NOT IN

This query works as I would expect it to, but I am trying to avoid using the "not in":

select * from subscribers ls join emailopens eo
    on ls.subscriberid=eo.subscriberid
    where ls.listid=1381 
    and opentime  > 1458864000
    and emailaddress not IN (select emailaddress from subscribers where listid=1384)

I have tried this:

select * from subscribers ls join emailopens eo
    on ls.subscriberid=eo.subscriberid left join subscribers ls2
    on ls.emailaddress=ls2.emailaddress
    where ls.listid=1381 
    and opentime  > 1458864000
    and ls2.listid=1384

The problem is that it only returns the rows that have the same email in both listid=1381 and listid=1384. I thought since I am using a left join it should return ALL rows where listid=1381, and then on rows where the same email is in 1384, would have the data, but if there is an email in 1381 and not 1384, would show null from the ls2.* columns.

Upvotes: 0

Views: 59

Answers (1)

Cᴏʀʏ
Cᴏʀʏ

Reputation: 107586

Putting the ls2.listid in the WHERE effectively turns your LEFT JOIN into an INNER JOIN. Try keeping the listid constraint in the ON, and then, to produce the effect of a NOT IN, make this an anti-join by asking for records where ls2.emailaddress IS NULL:

select * from subscribers ls join emailopens eo
    on ls.subscriberid=eo.subscriberid left join subscribers ls2
    on ls.emailaddress=ls2.emailaddress and ls2.listid = 1384
    where ls.listid=1381 
    and opentime  > 1458864000
    and ls2.emailaddress is null

Upvotes: 1

Related Questions