如何在一个表中查找在另一表中没有对应行的行 [英] How to find rows in one table that have no corresponding row in another table

问题描述

我在两个表之间有1:1的关系.我想查找表A中所有没有在表B中对应的行.我使用以下查询:

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是两个表中的主键.除了主键索引，我在tableA(id desc)上也有一个索引.

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

使用H2(Java嵌入式数据库)，将对tableB进行全表扫描.我想避免进行全表扫描.

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?

推荐答案

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

这篇关于如何在一个表中查找在另一表中没有对应行的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！