Comment trouver des lignes dans une table sans ligne correspondante dans une autre table

J’ai une relation 1: 1 entre deux tables. Je veux trouver toutes les lignes de la table A qui n’ont pas de ligne correspondante dans la table 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. Outre les indices de clé primaire, j’ai également un index sur tableA (id desc).

En utilisant H2 (firebase database intégrée Java), cela se traduit par une parsing complète de la table de table. Je veux éviter une parsing complète de la table.

Comment puis-je réécrire cette requête pour qu’elle s’exécute rapidement? Quel index devrais-je avoir?

 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 firebase database sait comment effectuer des intersections d’index, cela ne touchera que l’index de clé primaire

Vous pouvez également utiliser exists , car parfois, il est plus rapide que left join . Vous devrez les comparer pour déterminer celui que vous souhaitez utiliser.

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

Pour montrer que cela peut être plus efficace qu’une left join , voici les plans d’exécution de ces requêtes dans SQL Server 2008:

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

joint gauche

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

existe

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

H2 supporte la syntaxe MOINS donc vous devriez essayer ceci

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

Cela peut être plus rapide. cela vaut certainement la peine de faire des parsings comparatives.

Pour mon petit dataset, Oracle donne à presque toutes 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 à faire moins de gains cohérents malgré le coût du plan plus élevé.

 --Create Sample Data. drop table tableA; drop 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 ); alter table tableA Add Primary Key (ID); alter 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; 

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

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