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

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

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

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    idfrom    tableA awhere    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:

exists - total subtree cost: 1.07421:

You have to check every ID in tableA against every ID in tableB. A fully featured RDBMS (such as Oracle) would be able to optimize that into an INDEX FULL FAST SCAN and not touch the table at all. I don't know whether H2's optimizer is as smart as that.

H2 does support the MINUS syntax so you should try this

select id from tableAminusselect id from tableBorder by id desc

That may perform faster; it is certainly worth benchmarking.