Thomas Wiedmann https://twiedmann.de
• Sie befinden sich hier: Startseite > SQL-Backstube > Tabellen-/inhalte vergleichen

Die SQL-Backstube

Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.

28.08.2010: Tabellen-/inhalte vergleichen

Problemstellung

Diesmal sollen Inhalte zweier Tabellen miteinander verglichen werden. Beide Tabellen müssen die selbe oder eine ähnliche Struktur besitzen, also identische Spalten und Datentypen. Beispielsweise bei einem automatischen Programmtest kann es vorkommen, dass der komplette Tabelleninhalt verglichen werden muss. Die Daten können nicht über eine ID allein verglichen werden (z. B. mit Hilfe eines JOIN), da die Reihenfolge der Einträge nicht sichergestellt ist und damit die ID nicht in der identischen Reihenfolge vergeben worden sind.

Getestet mit folgenden Datenbanken:

* IBM DB2 9.1
* MySQL 5.1.30
* ORACLE 10g 10.2 EE

Datenbank-Tabellen

Die Datenbank-Tabellen hierfür sehen wie folgt aus:

Listing 1:

  1. /**
  2. * Bücher - Version: MySQL, DB2
  3. */
  4. CREATE TABLE buch_test1 (
  5. id INT NOT NULL,
  6. titel VARCHAR(100) NOT NULL,
  7. preis DEC(10,2) NOT NULL,
  9. PRIMARY KEY (id)
  10. );
  12. CREATE TABLE buch_test2 (
  13. id INT NOT NULL,
  14. titel VARCHAR(100) NOT NULL,
  15. preis DEC(10,2) NOT NULL,
  17. PRIMARY KEY (id)
  18. );
  20. /**
  21. * Bücher - Version: ORACLE
  22. */
  23. CREATE TABLE buch_test1 (
  24. id INT NOT NULL,
  25. titel VARCHAR2(100) NOT NULL,
  26. preis DEC(10,2) NOT NULL,
  28. PRIMARY KEY (id)
  29. );
  31. CREATE TABLE buch_test2 (
  32. id INT NOT NULL,
  33. titel VARCHAR2(100) NOT NULL,
  34. preis DEC(10,2) NOT NULL,
  36. PRIMARY KEY (id)
  37. );

Testdaten

Nun noch die Testdaten für die nachfolgenden SQL-Abfragen. Bitte beachten Sie, wie hier die Dezimalzahlen an die Datenbank übergeben werden. Anstatt Komma wird ein Punkt verwendet, also das Defaultzeichen (für den Dezimalanteil) bei amerikanischer Zahlendarstellung). Dies ist ein typisches NLS (National Language Support) Problem, das diesmal nicht näher thematisiert werden soll.

Listing 2:

  1. /**
  2. * Bücherdaten aus Testlauf-1
  3. */
  4. INSERT INTO buch_test1 VALUES ( 1, 'Titel-1', 20 );
  5. INSERT INTO buch_test1 VALUES ( 2, 'Titel-2', 29.90 );
  6. INSERT INTO buch_test1 VALUES ( 3, 'Titel-3', 21.90 );
  7. INSERT INTO buch_test1 VALUES ( 4, 'Titel-5', 0.90 );
  8. INSERT INTO buch_test1 VALUES ( 5, 'Titel-4', 0 );
  10. /**
  11. * Bücherdaten aus Testlauf-2
  12. */
  13. INSERT INTO buch_test2 VALUES ( 1, 'Titel-1', 20 );
  14. INSERT INTO buch_test2 VALUES ( 2, 'Titel-2', 29.90 );
  15. INSERT INTO buch_test2 VALUES ( 3, 'Titel-3', 0 );
  16. INSERT INTO buch_test2 VALUES ( 4, 'Titel-4', 0.90 );
  17. INSERT INTO buch_test2 VALUES ( 5, 'Titel-5', 999.90 );
  18. INSERT INTO buch_test2 VALUES ( 6, 'Titel-6', 19.90 );

SQL Anforderungen

- Sind die Tabelleninhalte (Ausprägungen) vollkommen identisch?
- Welche Spalten unterscheiden sich?
- Welche Zeilen unterscheiden sich?


Lösung mit ORACLE und "MINUS"

Listing 3:

  1. /**
  2. * ORACLE MINUS - Die Spalten "id, titel, preis" werden verglichen
  3. * #1 Unterschiede (ORACLE)
  4. */
  5. SELECT id, titel, preis FROM buch_test1
  7. MINUS
  9. SELECT id, titel, preis FROM buch_test2;
  11. ID TITEL PREIS
  12. --------------------
  13. 3 Titel-3 21,90
  14. 4 Titel-5 0,90
  15. 5 Titel-4 0,00

Listing 3: zeigt die Unterschiede, bei denen in der Tabelle buch_test1 ein Unterschied zu buch_test2 existiert. Egal in welcher Zeile oder Spalte.

Listing 4:

  1. /**
  2. * ORACLE MINUS - Die Spalten "id, titel, preis" werden verglichen
  3. * #2 Unterschiede (ORACLE)
  4. */
  5. SELECT id, titel, preis FROM buch_test2
  7. MINUS
  9. SELECT id, titel, preis FROM buch_test1;
  11. ID TITEL PREIS
  12. --------------------
  13. 3 Titel-3 0,00
  14. 4 Titel-4 0,90
  15. 5 Titel-5 999,90
  16. 6 Titel-6 19,90

Listing 4: wiederum zeigt die Unterschiede, bei denen in der Tabelle buch_test2 ein Unterschied zu buch_test1 existiert. Egal in welcher Zeile oder Spalte.


Die Ergebnisse von Listing 3: und 4: zeigen sehr schön, welchen Einfluß die Reihenfolge der abgefragten Tabellen hat. Also das die obere Tabelle die "neuen Daten" und die untere Tabelle die "alte Daten" darstellt.


Lösung mit IBM DB2 und "EXCEPT"

Listing 5:

  1. /**
  2. * DB2 EXCEPT - Die Spalten "id, titel, preis" werden verglichen
  3. * #3 Unterschiede (IBM DB2)
  4. */
  5. SELECT id, titel, preis FROM buch_test1
  7. EXCEPT
  9. SELECT id, titel, preis FROM buch_test2
  11. ORDER BY 1;
  13. ID TITEL PREIS
  14. --------------------
  15. 3 Titel-3 21,90
  16. 4 Titel-5 0,90
  17. 5 Titel-4 0,00

Listing 5: zeigt die Unterschiede, bei denen in der Tabelle buch_test1 ein Unterschied zu buch_test2 existiert. Egal in welcher Zeile oder Spalte.

Listing 6:

  1. /**
  2. * DB2 EXCEPT - Die Spalten "id, titel, preis" werden verglichen
  3. * #4 Unterschiede (IBM DB2)
  4. */
  5. SELECT id, titel, preis FROM buch_test2
  7. EXCEPT
  9. SELECT id, titel, preis FROM buch_test1
  11. ORDER BY 1;
  13. ID TITEL PREIS
  14. --------------------
  15. 3 Titel-3 0,00
  16. 4 Titel-4 0,90
  17. 5 Titel-5 999,90
  18. 6 Titel-6 19,90

Listing 6: wiederum zeigt die Unterschiede, bei denen in der Tabelle buch_test2 ein Unterschied zu buch_test1 existiert. Egal in welcher Zeile oder Spalte.


Die Ergebnisse mit EXCEPT von Listing 5: und 6: zeigen identische Ergebnisse zu der oben (siehe Listing 3: und 4:) gezeigten Oracle Lösung mit MINUS.


Lösung mit MySQL (NOT IN)

MySQL unterstützt in der verwendeten Version weder das proprietäre "MINUS" von ORACLE noch die SQL-Standardlösung "EXCEPT", so müssen wir manuell eine Lösung für MySQL nachbauen. Die gefundene NOT IN() Lösung kann sich aber bei grossen Datenmengen schnell als Performancekiller herausstellen.

Listing 7:

  1. /**
  2. * MySQL 5.1 - Die Spalten "id, titel, preis" werden verglichen
  3. * #5 Unterschiede (MySQL) (Vorsicht Performancekiller)
  4. */
  5. SELECT id, titel, preis
  6. FROM buch_test1
  7. WHERE (id, titel, preis ) NOT IN (SELECT id, titel, preis
  8. FROM buch_test2)
  9. ORDER BY id;
  11. +----+---------+-------+
  12. | id | titel | preis |
  13. +----+---------+-------+
  14. | 3 | Titel-3 | 21.90 |
  15. | 4 | Titel-5 | 0.90 |
  16. | 5 | Titel-4 | 0.00 |
  17. +----+---------+-------+
  18. 3 rows in set (0.00 sec)

Listing 7: zeigt die Unterschiede, bei denen in der Tabelle buch_test1 ein Unterschied zu buch_test2 existiert. Egal in welcher Zeile oder Spalte.

Listing 8:

  1. /**
  2. * MySQL 5.1 - Die Spalten "id, titel, preis" werden verglichen
  3. * #6 Unterschiede (MySQL) (Vorsicht Performancekiller)
  4. */
  5. SELECT id, titel, preis
  6. FROM buch_test2
  7. WHERE (id, titel, preis ) NOT IN (SELECT id, titel, preis
  8. FROM buch_test1)
  9. ORDER BY id;
  11. +----+---------+--------+
  12. | id | titel | preis |
  13. +----+---------+--------+
  14. | 3 | Titel-3 | 0.00 |
  15. | 4 | Titel-4 | 0.90 |
  16. | 5 | Titel-5 | 999.90 |
  17. | 6 | Titel-6 | 19.90 |
  18. +----+---------+--------+
  19. 4 rows in set (0.00 sec)

Resumee

Der SQL-Standard bietet mit EXCEPT eine gute Lösung für das Problem. Leider unterstützen nicht alle Datenbanken diesen Befehl, bieten aber proprietäre Alternativen dazu (siehe "MINUS"). Für MySQL wurde eine Lösung mit NOT IN () als Notlösung gefunden, die aber eventuell nicht sehr performant ist.


https://twiedmann.de


Sitemap - Inhaltsverzeichnis

© 2002-2024 by Thomas Wiedmann : (Stand : 11.01.2015).