Как найти строки в одной таблице, которые не имеют соответствующей строки в другой таблице

У меня есть соотношение 1:1 между двумя таблицами. Я хочу найти все строки в таблице A, которые не имеют соответствующей строки в таблице B. Я использую этот запрос:

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

id - первичный ключ в обеих таблицах. Помимо индексов первичного ключа, у меня также есть индекс на tableA (id desc).

Используя H2 (встроенная Java-база данных), это приводит к полному сканированию таблицы tableB. Я хочу избежать полного сканирования таблицы.

Как я могу переписать этот запрос для быстрого запуска? Какой индекс я должен использовать?

Ответы

Ответ 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

Если ваш db знает, как выполнять перекрестки индексов, это коснется индекса первичного ключа

Ответ 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:

left join

exists - общая стоимость поддерева: 1.07421:

exists

Ответ 3

Вы должны проверять каждый ID в таблицеA на каждый идентификатор в таблицеB. Полнофункциональная RDBMS (например, Oracle) сможет оптимизировать ее в ПОЛНОМ FAST SCAN INDEX FULL, а не касаться таблицы вообще. Я не знаю, является ли оптимизатор H2 таким же умным, как это.

H2 поддерживает синтаксис MINUS, поэтому вы должны попробовать это

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

Это может работать быстрее; это, безусловно, стоит бенчмаркинга.

Ответ 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;

Ответ 5

Я не могу сказать, какой из этих методов будет лучше всего на H2 (или даже если все они будут работать), но я написал статью, в которой подробно описаны все (хорошие) методы, доступные в TSQL. Вы можете дать им шанс и посмотреть, работает ли кто-нибудь из них:

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