sqlsubqueryleft-joinanti-join

SQL antijoin with multiple keys

I'd like to implement an antijoin on two table but using two keys so that the result is all rows in Table A that do not contain the combinations of [key_1, key_2] found in Table B. How can I write this query in SQL?

enter image description here

enter image description here

enter image description here

Solution

  • If you want an anti-left join, the logic is:

    select a.*
from tablea a 
left join tableb b on b.key_1 = a.key_1 and b.key_2 = a.key_2
where b.key_1 is null

    As for me, I like to implement such logic with not exists, because I find that it is more expressive about the intent:

    select a.*
from tablea a
where not exists (
    select 1 from tableb b where b.key_1 = a.key_1 and b.key_2 = a.key_2
)

    The not exists query would take advantage of an index on tableb(key_1, key_2).