Select NOT IN multiple columns

sql database multiple-columns

Gunjan Nigam · Nov 7, 2011 · Viewed 129.7k times · Source

I need to implement the following query:

SELECT * 
FROM   friend 
WHERE  ( friend.id1, friend.id2 ) 
         NOT IN (SELECT id1, 
                        id2 
                 FROM   likes)

But NOT IN can't be implemented on multiple columns. How do I write this query?

Answer

I'm not sure whether you think about:

select * from friend f
where not exists (
    select 1 from likes l where f.id1 = l.id and f.id2 = l.id2
)

it works only if id1 is related with id1 and id2 with id2 not both.