webentwicklung-frage-antwort-db.com.de

So finden Sie Zeilen in einer Tabelle, die keine entsprechenden Zeilen in einer anderen Tabelle haben

Ich habe eine 1: 1-Beziehung zwischen zwei Tabellen. Ich möchte alle Zeilen in Tabelle A finden, die keine entsprechende Zeile in Tabelle B haben. Ich verwende diese Abfrage:

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

id ist der Primärschlüssel in beiden Tabellen. Neben den Primärschlüsselindizes habe ich auch einen Index für tableA (id desc).

Bei Verwendung von H2 (Java Embedded Database) führt dies zu einem vollständigen Table-Scan von tableB. Ich möchte einen vollständigen Tabellenscan vermeiden.

Wie kann ich diese Abfrage umschreiben, um schnell ausgeführt zu werden? Welchen Index soll ich machen?

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

Wenn Ihre Datenbankbibliothek weiß, wie Indexkreuzungen ausgeführt werden, berührt dies nur den Primärschlüsselindex

86
SquareCog

Sie können auch exists verwenden, da dies manchmal schneller ist als left join. Sie müssten sie vergleichen, um herauszufinden, welches Sie verwenden möchten.

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

Um zu zeigen, dass exists effizienter sein kann als ein left join, sind hier die Ausführungspläne dieser Abfragen in SQL Server 2008 aufgeführt:

left join- Gesamtteilbaumkosten: 1.09724:

left join

exists- Gesamtteilbaumkosten: 1.07421:

exists

30
Eric

Sie müssen jede ID in Tabelle A mit jeder ID in Tabelle B vergleichen. Ein voll funktionsfähiges RDBMS (wie Oracle) könnte dies zu einem INDEX FULL FAST SCAN optimieren und die Tabelle überhaupt nicht berühren. Ich weiß nicht, ob der Optimierer von H2 so schlau ist.

H2 unterstützt die MINUS-Syntax, Sie sollten es also versuchen

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

Das kann schneller ablaufen; Es ist sicher ein Benchmarking wert. 

5
APC

Für mein kleines Dataset gibt Oracle fast alle diese Abfragen genau den gleichen Plan ab, der die Primärschlüsselindizes verwendet, ohne die Tabelle zu berühren. Die Ausnahme ist die MINUS-Version, die trotz der höheren Planungskosten weniger konsistente Fehler macht.

--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;
4
Leigh Riffel

Ich kann Ihnen nicht sagen, welche dieser Methoden für H2 am besten geeignet ist (oder sogar, wenn alle funktionieren), aber ich habe einen Artikel geschrieben, in dem alle (guten) Methoden beschrieben werden, die in TSQL verfügbar sind. Sie können ihnen einen Schuss geben und sehen, ob einer von ihnen für Sie funktioniert:

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

3
Aaron Alton
select parentTable.id from parentTable
left outer join childTable on (parentTable.id = childTable.parentTableID) 
where childTable.id is null
0
Faysal Maqsood