我在两个表之间有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 descid是两个表中的主键.除了主键索引,我在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
更多推荐
如何在一个表中查找在另一表中没有对应行的行
发布评论