blocks|key|1454869|text|select+tableA.id+from+tableA+left+outer+join+tableB+on+(tableA.id+=+tableB.id)
where+tableB.id+is+null
order+by+tableA.id+desc+|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1454870|如果您的db知道如何进行索引交集，这将只涉及主键索引。|unstyled|1454871|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>select tableA.id from tableA left outer join tableB on (tableA.id = tableB.id)
where tableB.id is null
order by tableA.id desc 
</code></pre>

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

blocks|key|1239979|text|我不能告诉您这些方法中的哪一个在H2上是最好的(或者即使它们都可以工作)，但是我写了一篇文章，详细介绍了TSQL中所有可用的(好的)方法。你可以给他们一次机会，看看他们中是否有适合你的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1239980|http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=QueryBasedUponAbsenceOfData&referringTitle=Home|offset|length|1239981|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|33|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@$D|O|E|P|1|Q]]|A|$]]|$1|F|3|-4|5|6|7|R|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|C]]]]

I can't tell you which of these methods will be best on H2 (or even if all of them will work), but I did write an article detailing all of the (good) methods available in TSQL. You can give them a shot and see if any of them works for you:

<a href="http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=QueryBasedUponAbsenceOfData&amp;referringTitle=Home" rel="nofollow noreferrer">http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=QueryBasedUponAbsenceOfData&amp;referringTitle=Home</a>

blocks|key|1454970|text|您必须根据tableB中的每个ID检查tableA中的每个ID。一个功能齐全的RDBMS+(如Oracle)将能够将其优化为索引完全快速扫描，并且根本不涉及表。我不知道h2的优化器是否那么聪明。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1454971|H2确实支持减号语法，因此您应该尝试这样做|1454972|select+id+from+tableA
minus
select+id+from+tableB
order+by+id+desc|code-block|syntax|javascript|1454973|这可能会执行得更快；它当然值得进行基准测试。|1454974|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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

<pre><code>select id from tableA
minus
select id from tableB
order by id desc
</code></pre>

That may perform faster; it is certainly worth benchmarking.

blocks|key|1455038|text|对于我的小型数据集，Oracle为几乎所有这些查询提供了完全相同的计划，该计划使用主键索引，而不涉及表。例外的是负版本，尽管计划成本较高，但它设法执行较少的一致获取。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1455039|--Create+Sample+Data.
d+r+o+p+table+tableA;
d+r+o+p+table+tableB;

create+table+tableA+as+(
+++select+rownum-1+ID,+chr(rownum-1%2B70)+bb,+chr(rownum-1%2B100)+cc+
++++++from+dual+connect+by+rownum<=4
);

create+table+tableB+as+(
+++select+rownum+ID,+chr(rownum%2B70)+data1,+chr(rownum%2B100)+cc+from+dual
+++UNION+ALL
+++select+rownum%2B2+ID,+chr(rownum%2B70)+data1,+chr(rownum%2B100)+cc+
++++++from+dual+connect+by+rownum<=3
);

a+l+t+e+r+table+tableA+Add+Primary+Key+(ID);
a+l+t+e+r+table+tableB+Add+Primary+Key+(ID);

--View+Tables.
select+*+from+tableA;
select+*+from+tableB;

--Find+all+rows+in+tableA+that+don't+have+a+corresponding+row+in+tableB.

--Method+1.
SELECT+id+FROM+tableA+WHERE+id+NOT+IN+(SELECT+id+FROM+tableB)+ORDER+BY+id+DESC;

--Method+2.
SELECT+tableA.id+FROM+tableA+LEFT+JOIN+tableB+ON+(tableA.id+=+tableB.id)
WHERE+tableB.id+IS+NULL+ORDER+BY+tableA.id+DESC;

--Method+3.
SELECT+id+FROM+tableA+a+WHERE+NOT+EXISTS+(SELECT+1+FROM+tableB+b+WHERE+b.id+=+a.id)+
+++ORDER+BY+id+DESC;

--Method+4.
SELECT+id+FROM+tableA
MINUS
SELECT+id+FROM+tableB+ORDER+BY+id+DESC;|code-block|syntax|javascript|1455040|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

For my small dataset, Oracle gives almost all of these queries the exact same plan that uses the primary key indexes without touching the table. The exception is the MINUS version which manages to do fewer consistent gets despite the higher plan cost.

<pre><code>--Create Sample Data.
d r o p table tableA;
d r o p table tableB;

create table tableA as (
 select rownum-1 ID, chr(rownum-1+70) bb, chr(rownum-1+100) cc 
 from dual connect by rownum&lt;=4
);

create table tableB as (
 select rownum ID, chr(rownum+70) data1, chr(rownum+100) cc from dual
 UNION ALL
 select rownum+2 ID, chr(rownum+70) data1, chr(rownum+100) cc 
 from dual connect by rownum&lt;=3
);

a l t e r table tableA Add Primary Key (ID);
a l t e r table tableB Add Primary Key (ID);

--View Tables.
select * from tableA;
select * from tableB;

--Find all rows in tableA that don't have a corresponding row in tableB.

--Method 1.
SELECT id FROM tableA WHERE id NOT IN (SELECT id FROM tableB) ORDER BY id DESC;

--Method 2.
SELECT tableA.id FROM tableA LEFT JOIN tableB ON (tableA.id = tableB.id)
WHERE tableB.id IS NULL ORDER BY tableA.id DESC;

--Method 3.
SELECT id FROM tableA a WHERE NOT EXISTS (SELECT 1 FROM tableB b WHERE b.id = a.id) 
 ORDER BY id DESC;

--Method 4.
SELECT id FROM tableA
MINUS
SELECT id FROM tableB ORDER BY id DESC;
</code></pre>

blocks|key|1455059|text|select+parentTable.id+from+parentTable
left+outer+join+childTable+on+(parentTable.id+=+childTable.parentTableID)+
where+childTable.id+is+null|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1455060|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>select parentTable.id from parentTable
left outer join childTable on (parentTable.id = childTable.parentTableID) 
where childTable.id is null
</code></pre>

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:

<pre><code>SELECT id 
 FROM tableA 
 WHERE id NOT IN (SELECT id 
 FROM tableB) 
ORDER BY id desc
</code></pre>

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?

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

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我在两个表之间有1:1的关系。我想要查找表A中所有在表B中没有对应行的行。我使用以下查询：SELECT id   FROM tableA  WHERE id NOT IN (SELECT id                     FROM tableB) ORDER BY id descid是两个表中的主键。除了主...

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

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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