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

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)

表明 存在 可以比 左连接 ,以下是SQL Server 2008中这些查询的执行计划:

左连接 -子树总成本:1.09724:

存在 -子树总成本:1.07421:

您必须检查表A中的每个ID和表B中的每个ID。一个功能齐全的RDBMS(如Oracle)将能够将其优化为一个索引,完全快速扫描,并且根本不需要触摸表。我不知道H2的优化器是否如此聪明。

h2支持减号语法,所以您应该试试这个

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

这可能执行得更快;这当然值得标杆。

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

我不能告诉你这些方法中哪一个在h2上最好(或者即使所有的方法都有效),但是我写了一篇文章详细描述了TSQL中所有可用的(好的)方法。你可以给他们一个机会看看他们中是否有人适合你:

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

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