SQL antijoin with multiple keys

Question

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?

Answer 1

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).

Answer 2

select a.*
from table_a a 
left anti join table_b b on a.key_1 = b.key_1 and a.key_2 = b.key_2;