如何在一个表中查找在另一表中没有对应行的行

本文介绍了如何在一个表中查找在另一表中没有对应行的行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在两个表之间有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