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

Question

我有两个表之间的一对一关系。我想找到在A表中所有没有与B表中对应行的行。我使用以下查询：

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

id字段是两个表中的主键。除了主键索引之外，我还在tableA表上建立了一个id字段降序索引。

使用H2（Java嵌入式数据库）会导致对tableB表进行全表扫描。我想避免这种情况。

我该如何重写查询以使其运行速度更快？我应该添加哪个索引？

- Steve McLeod

---

每次你写 'WHERE col [NOT] IN (SELECT col FROM othertable)' 时，最好使用 [NOT] EXISTS 进行重构。 - topchef

Answer 1

select tableA.id from tableA left outer join tableB on (tableA.id = tableB.id)
where tableB.id is null
order by tableA.id desc 

如果你的数据库知道如何执行索引交集，那么这将仅涉及主键索引。

- SquareCog

---

这就是为什么我喜欢 Stack Overflow。星期六，遇到 SQL 问题 - 5分钟内准确并成功地得到了答案！ - Steve McLeod

其他答案中也有一些不错的建议。当然，我认为我的是最快的 :-) 但是数据库实现差异很大，而且我对H2没有经验。如果您对不同的方法进行基准测试并更新问题以显示结果，那将非常好。 - SquareCog

Answer 2

你也可以使用exists，因为有时它比left join更快。 您需要对它们进行基准测试以确定要使用哪个。

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

为了展示exists比left join更高效，这里是SQL Server 2008中这些查询的执行计划:

left join - 整个子树成本: 1.09724:

exists - 整个子树成本: 1.07421:

- Eric

---

+1：如果子查询（在这种情况下是相关的）返回至少一行，则认为满足EXISTS条件。 - OMG Ponies

基准测试是个好主意。我正在绞尽脑汁，试图弄清楚一个数据库在存在+相关子查询方面可能做了什么，以使其比仅索引哈希连接更快。你知道吗？ - SquareCog

“Exists”并没有使用标准的相关子查询，而是使用了半连接。在SQL Server 2008上，“left join”的执行计划是两个索引扫描到哈希匹配再到过滤再到选择。对于“not exists”，它是两个索引扫描到哈希匹配再到选择——没有过滤器。实际上，“exists”哈希匹配比“left join”稍微快一些。“left join”的总成本为1.09，“not exists”的成本为1.07，在“AdventureWorksDW”到“AdventureWorksDW2008”的“DimCustomer”上。 - Eric

太棒了!! 谢谢。这是一个很聪明的优化器。尽管成本是大概的，但我根据“过滤器 vs 半连接”原则来支持它。 - SquareCog

Answer 3

您需要在tableA中的每个ID与tableB中的每个ID进行比较。完整功能的RDBMS（如Oracle）将能够将其优化为INDEX FULL FAST SCAN，而无需触及表格。我不知道H2的优化器是否像那样聪明。

H2支持MINUS语法，因此您应该尝试这样做

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

那可能会更快；值得进行基准测试。

- APC

Answer 4

对于我的小数据集，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;

- Leigh Riffel

---

对于写代码来说，“d r o p”这样的命名方式可以让人更容易读懂代码，你会得到额外的加分。 - Flavius

Answer 5

我无法告诉你哪种方法在H2上效果最好（甚至不确定它们是否都可行），但我写了一篇详细介绍TSQL中所有（好的）可用方法的文章。你可以尝试使用它们，看看哪种方法适合你：

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=QueryBasedUponAbsenceOfData&referringTitle=Home

- Aaron Alton

Answer 6

select parentTable.id from parentTable
left outer join childTable on (parentTable.id = childTable.parentTableID) 
where childTable.id is null

- Faysal Maqsood

---

能否请您再解释一下，这样会更好吗？ - Failed Scientist