Als PDF Downloaden!

SET-Operatoren/Abgleich von Tabelleninhalten

Gelegentlich kommt es vor, dass man die Inhalte zweier Tabellen miteinander vergleichen muss. Mögliche Fragestellungen sind:

1.    Was ist in den beiden Tabellen gleich?
2.    Was ist in zumindest einer der beiden Tabellen enthalten?
3.    Was ist nur in Tabelle A, aber nicht in Tabelle B?
4.    Was ist nur in je einer der beiden Tabellen, aber nicht in der anderen?

Der effizienteste Weg dazu ist in der Regel der Einsatz von SET-Operatoren, da diese normalerweise effizienter sind als die Verwendung von Unterabfragen mit [NOT] IN-Operator. Mit diesen Operatoren, auch Mengen-Operatoren genannt, kann man zwei oder mehr Abfragen miteinander verbinden. Zu beachten ist hierbei, dass die beteiligten Abfragen in Anzahl und Datentyp der selektierten Spalten übereinstimmen müssen. Der Vergleich bezieht alle angegebenen Spalten mit ein; zwei Einträge werden nur dann als gleich erachtet, wenn sie in allen angegebenen Spalten übereinstimmen.
SET-Operatoren sind auch in Inline-Views und DML-Anweisungen mit SELECT-Klausel zulässig.
Bei den folgenden Beispielen wird von der Tabelle SCOTT.EMP ausgegangen. Zusätzlich gibt es eine Tabelle SCOTT.EMP_HIST, die der Historisierung von Änderungen (UPDATE oder DELETE) in EMP dienen soll; sie ist teilweise identisch mit EMP.

Fragestellung 1: Was ist in den beiden Tabellen gleich?

Hier bietet sich INTERSECT an. INTERSECT wirkt wie die Angabe des DISTINCT-Schlüsselworts bei SELECT-Anweisungen: Doppelte Einträge werden ausgeblendet, und es wird standardmäßig aufsteigend sortiert.

Beispiel:

SQL> SELECT ename, sal FROM EMP
     INTERSECT
     SELECT ename, sal FROM EMP_HIST;

ENAME             SAL
---------- ----------
JAMES             950
KING             5000
MILLER           1300
SCOTT            3000
TURNER           1500

Fragestellung 2: Was ist in zumindest einer der beiden Tabellen enthalten?

Soll nur ermittelt werden, welche Einträge es generell gibt, so ist UNION der Operator der Wahl; sollen mehrfach vorhandene Einträge auch mehrfach angezeigt werden, ist stattdessen UNION ALL zu verwenden.
Durch UNION werden doppelte Einträge ausgeblendet, und es wird standardmäßig aufsteigend sortiert. Beides ist bei UNION ALL nicht der Fall.
Beispiel 1 (UNION ALL): Hier wird nicht sortiert, und doppelte Einträge erscheinen auch dann, wenn sowohl Name als auch Gehalt übereinstimmen.

SQL> SELECT ename, sal FROM EMP
     UNION ALL
     SELECT ename, sal FROM EMP_HIST;

ENAME             SAL
---------- ----------
SMITH             800
ALLEN            1600
WARD             1250
JONES            2975
MARTIN           1250
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
TURNER           1500
JAMES             950
MILLER           1300
MARTIN           1125
BLAKE            2565
CLARK            2205
SCOTT            3000
KING             5000
TURNER           1500
ADAMS            1100
JAMES             950
FORD             3000
MILLER           1300

22 Zeilen ausgewählt.

Beispiel 2 (UNION):
Hier wird sortiert, und doppelte Einträge werden nicht ausgegeben.

SQL>  SELECT ename, sal FROM EMP
      UNION
      SELECT ename, sal FROM EMP_HIST;

ENAME             SAL
---------- ----------
ADAMS            1100
ALLEN            1600
BLAKE            2565
BLAKE            2850
CLARK            2205
CLARK            2450
FORD             3000
JAMES             950
JONES            2975
KING             5000
MARTIN           1125
MARTIN           1250
MILLER           1300
SCOTT            3000
SMITH             800
TURNER           1500
WARD             1250

17 Zeilen ausgewählt.

Fragestellung 3:  Was ist nur in Tabelle A, aber nicht in Tabelle B?

Diese Fragestellung kann mit MINUS beantwortet werden. Während bei den übrigen SET-Operatoren die Reihenfolge der Abragen irrelevant ist, spielt sie bei MINUS sehr wohl eine Rolle. Auch MINUS unterdrückt die Ausgabe doppelter Einträge.

Beispiele:

SQL> SELECT ename, sal FROM EMP
     MINUS
     SELECT ename, sal FROM EMP_HIST;

ENAME             SAL
---------- ----------
ALLEN            1600
BLAKE            2850
CLARK            2450
JONES            2975
MARTIN           1250
SMITH             800
WARD             1250
7 Zeilen ausgewählt.

SQL> SELECT ename, sal FROM EMP_HIST
     MINUS
     SELECT ename, sal FROM EMP;


ENAME             SAL
---------- ----------
ADAMS            1100
BLAKE            2565
CLARK            2205
FORD             3000
MARTIN           1125

Fragestellung 4:  Was ist nur in je einer der beiden Tabellen, aber nicht in der anderen?

Hier wird die Lösung etwas komplexer, da mehr als zwei Abfragen miteinander verknüpft werden müssen. Dabei ist darauf zu achten, dass  die SET-Operatoren gleichberechtigt sind; die Abarbeitung erfolgt von links nach rechts. Eine andere Reihenfolge kann durch Klammern erreicht werden.

Lösung 1:

SQL>  (SELECT ename, sal FROM EMP_HIST
      MINUS
      SELECT ename, sal FROM EMP)
      UNION
      (SELECT ename, sal FROM EMP
      MINUS
      SELECT ename, sal FROM EMP_HIST);

ENAME             SAL
---------- ----------
ADAMS            1100
ALLEN            1600
BLAKE            2565
BLAKE            2850
CLARK            2205
CLARK            2450
FORD             3000
JONES            2975
MARTIN           1125
MARTIN           1250
SMITH             800
WARD             1250

12 Zeilen ausgewählt.

Lösung 2:

SQL> (SELECT ename, sal FROM EMP_HIST
     UNION
     SELECT ename, sal FROM EMP)
     MINUS
     (SELECT ename, sal FROM EMP
     INTERSECT
     SELECT ename, sal FROM EMP_HIST);

ENAME             SAL
---------- ----------
ADAMS            1100
ALLEN            1600
BLAKE            2565
BLAKE            2850
CLARK            2205
CLARK            2450
FORD             3000
JONES            2975
MARTIN           1125
MARTIN           1250
SMITH             800
WARD             1250

12 Zeilen ausgewählt.

Sonstige Fragestellungen:

Wollen Sie nur schnell (z.B. über eine Prozedur) untersuchen, OB bestimmte Tabellen unterschiedlich sind, so können Sie beispielsweise die Anzahl unterschiedlicher Einträge zählen:

CREATE OR REPLACE PROCEDURE diff_test IS
       TYPE t_deltas IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
       v_deltas t_deltas;
BEGIN      
   SELECT COUNT (*) FROM (
   SELECT * FROM table1
   MINUS SELECT * FROM table1_hist)' into v_deltas(1);

   SELECT COUNT (*) FROM (
   SELECT * FROM table2
   MINUS SELECT * FROM table2_hist)' into v_deltas(2);
   
   SELECT COUNT (*) FROM (
   SELECT * FROM table3
   MINUS SELECT * FROM table3_hist)' into v_deltas(3);

   -- Gegenprobe:
  
   SELECT COUNT (*) FROM (
   SELECT * FROM table1_hist
   MINUS SELECT * FROM table1)' into v_deltas(4);

   SELECT COUNT (*) FROM (
   SELECT * FROM table2_hist
   MINUS SELECT * FROM table2)' into v_deltas(5);
   
   SELECT COUNT (*) FROM (
   SELECT * FROM table3_hist
   MINUS SELECT * FROM table3)' into v_deltas(6);
   
   FOR i IN v_deltas.FIRST..v_deltas.LAST LOOP
      IF v_deltas(i) <> 0 THEN    
         DBMS_OUTPUT.PUT_LINE('Delta für Index '||i
                      ||', Differenz: '||v_deltas(i));
      ELSE
         DBMS_OUTPUT.PUT_LINE'---- ');
      END IF;
   END LOOP;
     
END;
/

Wenn Sie die Tabellen nicht nur vergleichen wollen, sondern auch in eine der Tabellen die fehlenden Einträge einfügen wollen, so bietet sich ein INSERT mit SELECT-Klausel an. Voraussetzung für einen vollständigen Abgleich ist eine eindeutige Spalte, da MINUS ja doppelte Einträge unterdrückt:

SQL> INSERT INTO EMP_HIST
     (SELECT * FROM EMP
     MINUS
     SELECT * FROM EMP_HIST);

Weitere Fragestellungen, bei denen der Einsatz von SET-Operatoren sinnvoll ist, bleiben Ihrer Phantasie überlassen...

Der Monatstipp 05/2009 behandelt eine weitere Möglichkeit Tabellen, bzw. ganze Schemata miteinander zu vergleichen. Ein eigens für diesen Zweck entwickeltes Package (für Oracle Versionen 10g und 11g) steht dort zum Download bereit.

Suche