如何在一个表中查找在另一个表中没有对应行的行
如何在一个表中查找在另一个表中没有对应行的行
提问于 2009-09-12 16:04:48
我在两个表之间有1:1的关系。我想要查找表A中所有在表B中没有对应行的行。我使用以下查询：
SELECT id
FROM tableA
WHERE id NOT IN (SELECT id
FROM tableB)
ORDER BY id descid是两个表中的主键。除了主键索引之外，我还有一个关于tableA(id desc)的索引。
使用H2 (Java嵌入式数据库)，这将导致对tableB进行全表扫描。我想避免全表扫描。
如何重写此查询以快速运行？我应该使用什么索引？
回答 5
Stack Overflow用户
回答已采纳
发布于 2009-09-12 16:09:33
select tableA.id from tableA left outer join tableB on (tableA.id = tableB.id)
where tableB.id is null
order by tableA.id desc 如果您的db知道如何进行索引交集，这将只涉及主键索引。
Stack Overflow用户
发布于 2009-09-12 16:18:50
您必须根据tableB中的每个ID检查tableA中的每个ID。一个功能齐全的RDBMS (如Oracle)将能够将其优化为索引完全快速扫描，并且根本不涉及表。我不知道h2的优化器是否那么聪明。
H2确实支持减号语法，因此您应该尝试这样做
select id from tableA
minus
select id from tableB
order by id desc这可能会执行得更快；它当然值得进行基准测试。
Stack Overflow用户
发布于 2010-12-10 22:36:55
对于我的小型数据集，Oracle为几乎所有这些查询提供了完全相同的计划，该计划使用主键索引，而不涉及表。例外的是负版本，尽管计划成本较高，但它设法执行较少的一致获取。
--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<=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<=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;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/1415438
复制相关文章
相似问题