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

两张桌子之间的关系是1:1。我想找到表 A 中所有没有对应行的行，我使用这个查询:

SELECT id
FROM tableA
WHERE id NOT IN (SELECT id
FROM tableB)
ORDER BY id desc

Id 是两个表中的主键。除了主键索引之外，我还在 tableA (id desc)上有一个索引。

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

如何重写此查询以快速运行? 应该使用什么索引？

109417 次浏览
小开
最佳答案 
select tableA.id from tableA left outer join tableB on (tableA.id = tableB.id)
where tableB.id is null
order by tableA.id desc

如果数据库知道如何执行索引交叉，那么这只会触及主键索引

小开

您也可以使用 exists，因为有时它比 left join快。您必须对它们进行基准测试，以确定您想使用哪一个。

select
id
from
tableA a
where
not exists
(select 1 from tableB b where b.id = a.id)

为了证明 exists比 left join更有效率，下面是 SQLServer2008中这些查询的执行计划:

left join -子树总成本: 1.09724:

left join

exists -子树总成本: 1.07421:

exists

小开

我不能告诉您这些方法中哪一个在 H2上是最好的(或者即使它们都能工作) ，但是我确实写了一篇文章，详细介绍了 TSQL 中可用的所有(好的)方法。你可以给他们一个机会，看看他们是否为你工作:

Http://code.msdn.microsoft.com/sqlexamples/wiki/view.aspx?title=querybaseduponabsenceofdata&referringtitle=home

小开

必须对照表 B 中的每个 ID 检查表 A 中的每个 ID。一个功能齐全的 RDBMS (比如 Oracle)可以将其优化为 INDEX FULL FAST SCAN，而且根本不需要触碰表。我不知道 H2的优化器是否如此聪明。

H2确实支持 MINUS 语法，所以您应该尝试这样做

select id from tableA
minus
select id from tableB
order by id desc

这可能会执行得更快; 这当然值得进行基准测试。

小开

对于我的小型数据集，Oracle 为几乎所有这些查询提供了完全相同的计划，即使用主键索引而不触及表。MINUS 版本是个例外，尽管计划成本较高，但它能做的一致性较低。

--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;
小开 
select parentTable.id from parentTable
left outer join childTable on (parentTable.id = childTable.parentTableID)
where childTable.id is null

提交答案