Comment trouver des lignes dans une table qui n'ont pas de ligne correspondante dans une autre table

Question

J'ai une relation 1:1 entre les deux tables. Je veux trouver toutes les lignes dans Une table qui n'ont pas de ligne correspondante dans le tableau B. j'utilise cette requête:

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

id est la clé primaire dans les deux tables. En dehors de clé primaire indices, j'ai aussi un indice sur tableA(id desc).

À l'aide de H2 (Java embarqué de base de données), il en résulte un full table scan de la tableB. Je veux éviter un full table scan.

Comment puis-je réécrire cette requête à exécuter rapidement? Ce l'indice que je devrais?

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
 

Si votre base de données sait comment faire les intersections d'index, cela ne touchera que l'index de clé primaire

Answer 2

Vous pouvez également utiliser exists,, car il est parfois plus rapide que l' left join. Vous devriez les comparer pour lequel vous souhaitez utiliser.

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

Pour montrer qu' exists peut être plus efficace qu'un left join, voici les plans d'exécution de ces requêtes dans SQL Server 2008:

left join - total sous-arbre coût: 1.09724:

exists - total sous-arbre coût: 1.07421:

Answer 3

Vous devez vérifier chaque ID de la tableA par rapport à chaque ID de la tableB. Un SGBDR complet (tel que Oracle) serait capable de l’optimiser dans un INDEX FULL FAST SCAN et de ne pas toucher la table du tout. Je ne sais pas si l'optimiseur de H2 est aussi intelligent que cela.

H2 supporte la syntaxe MINUS, vous devriez donc essayer ceci

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

Cela peut être plus rapide. il vaut certainement la peine de procéder à une analyse comparative.

Answer 4

Pour mon petit ensemble de données, Oracle donne à la quasi-totalité de ces requêtes le même plan qui utilise les index de clé primaire sans toucher à la table. L'exception est la version MINUS qui parvient à générer moins de gains cohérents en dépit du coût prévisible plus élevé.

 --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;
 

Answer 5

Je ne peux pas vous dire laquelle de ces méthodes sera meilleur sur H2 (ou même si tous va fonctionner), mais j'ai écrit un article détaillant toutes les (bonnes) les méthodes disponibles dans TSQL. Vous pouvez leur donner un coup de feu et voir si l'un d'eux travaille pour vous:

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