How to find rows in one table that have no corresponding row in another table

You can also use exists, since sometimes it's faster than left join. You'd have to benchmark them to figure out which one you want to use.

select
    id
from
    tableA a
where
    not exists
    (select 1 from tableB b where b.id = a.id)

To show that exists can be more efficient than a left join, here's the execution plans of these queries in SQL Server 2008:

left join - total subtree cost: 1.09724:

left join

exists - total subtree cost: 1.07421:

exists

select tableA.id from tableA left outer join tableB on (tableA.id = tableB.id)
where tableB.id is null
order by tableA.id desc

If your db knows how to do index intersections, this will only touch the primary key index

Related