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

sql optimization h2
Steve McLeod picture Steve McLeod · Sep 12, 2009 · Viewed 99.4k times · Source

I have a 1:1 relationship between two tables. I want to find all the rows in table A that don't have a corresponding row in table B. I use this query:

SELECT id 
  FROM tableA 
 WHERE id NOT IN (SELECT id 
                    FROM tableB) 
ORDER BY id desc

id is the primary key in both tables. Apart from primary key indices, I also have a index on tableA(id desc).

Using H2 (Java embedded database), this results in a full table scan of tableB. I want to avoid a full table scan.

How can I rewrite this query to run quickly? What index should I should?

Answer

SquareCog picture SquareCog · Sep 12, 2009 
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 questions

SQL: How to properly check if a record exists

While reading some SQL Tuning-related documentation, I found this: SELECT COUNT(*) : Counts the number of rows. Often is improperly used to verify the existence of a record. Is SELECT COUNT(*) really that bad? What's the proper way to verify the …

Read More
delete all from table

what's faster? DELETE FROM table_name; or DELETE FROM table_name where 1=1; why? does truncate table work in access?

Read More
Fastest way to remove non-numeric characters from a VARCHAR in SQL Server

I'm writing an import utility that is using phone numbers as a unique key within the import. I need to check that the phone number does not already exist in my DB. The problem is that phone numbers in the …

Read More