Search code examples
mysqlsqlsubquerywhere-clause

How to do the minus operation given 2 columns using mysql

allpairs

user1 user2
1     1
2     1
3     1
1     2
2     2
3     2
1     3
2     3
3     3

likesPairs

user1 user2
1     2
2     1
3     1

I want to do allPairs - likedPairs to get the relation

notliked

user1 user2
1     1
2     2
3     2
1     3
2     3
3     3

I tried something like this but it just errors

select user1, user2 
from allpairs NOT IN likespairs
Solution

  • This sounds like not exists:

    select ap.*
from allpairs ap
where not exists (
    select 1 
    from likespairs lp 
    where lp.user1 = ap.user1 and lp.user2 = ap.user2
)