• Sie befinden sich hier: Startseite > SQL-Backstube > Tabellen mit UNION verbinden

Die SQL-Backstube

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

07.08.2010: Tabellen mit UNION verbinden

SQL basiert eigentlich immer auf Mengenoperationen. Häufig ist damit ein JOIN gemeint. Damit können Daten mit Hilfe von Fremdschlüsseln quasi "horizontal" verknüpft werden. Es gibt aber eine Reihe von Befehlen, die eine Art von "vertikalen" JOIN durchführen. Dabei wird auch kein Schlüsselfeld benötigt. Gemeint ist hierbei z. B. der UNION Befehl. Damit können typ-gleiche Datenmengen miteinander verglichen und als eine Gesamtergebnismenge dargestellt werden.

Problemstellung

E-Mail Adressen sollen aus verschiedenen Tabellen mit verschiedenen Spaltennamen und verschiedenen Strukturen verglichen werden. Zudem müssen identische E-Mail gefunden und die Datenquelle ermittelt werden, um eventuell Dupletten zu finden.

Getestet mit folgenden Datenbanken:

* MySQL 5.1.30

Datenbank-Tabellen

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

Listing 1:

1. /**
2. * Kunden
3. */
4. CREATE TABLE kunde (
5. id INT NOT NULL,
6. name1 VARCHAR(50) NOT NULL,
7. name2 VARCHAR(50) NOT NULL,
8. strasse VARCHAR(50) NOT NULL,
9. plz INT NOT NULL,
10. email VARCHAR(100) NOT NULL,
12. PRIMARY KEY (id)
13. );
15. /**
16. * Rechnungen
17. */
18. CREATE TABLE rechnung (
19. id INT NOT NULL,
20. kunde_id INT NOT NULL,
21. rechnung_nr INT NOT NULL,
22. betrag DEC(10,2) NOT NULL,
23. datum DATE NOT NULL,
24. email VARCHAR(100) NOT NULL,
26. PRIMARY KEY (id),
28. CONSTRAINT fk_rechnung_01
29. FOREIGN KEY (kunde_id)
30. REFERENCES kunde (id)
31. );
33. /**
34. * Sonstige Adressen
35. */
36. CREATE TABLE adresspool (
37. id INT NOT NULL,
38. anschrift1 VARCHAR(50) NOT NULL,
39. anschrift2 VARCHAR(50),
40. strasse VARCHAR(50),
41. plz VARCHAR(10),
42. email VARCHAR(100) NOT NULL,
44. PRIMARY KEY (id)
45. );

Testdaten

Ein paar Testdaten für die folgenden SQL-Abfragen zur Ermittlung der doppelten E-Mail-Adressen.

Listing 2:

1. /**
2. * Kunden
3. */
4. INSERT INTO kunde VALUES
5. ( 1, 'Frank', 'Muster-1', 'Strasse-1', 1, '[email protected]'),
6. ( 2, 'Frieda', 'Muster-2', 'Strasse-2', 2, '[email protected]'),
7. ( 3, 'Frodo', 'Muster-3', 'Ringstrasse-3', 3, '[email protected]');
9. /**
10. * Rechnungen
11. */
12. INSERT INTO rechnung VALUES
13. ( 1, 1, 10, 100, '2010-08-01', '[email protected]'),
14. ( 2, 1, 11, 80, '2010-08-02', '[email protected]'),
15. ( 3, 3, 12, 42, '2010-08-03', '[email protected]');
17. /**
18. * Sonstige Adressen
19. */
20. INSERT INTO adresspool VALUES
21. ( 1, 'Amigo', 'Adress-1', NULL, 'lala', '[email protected]'),
22. ( 2, 'Alma', 'Adress-2', 'hm', '99', '[email protected]'),
23. ( 3, 'Frida', 'Adress-3', '.', '99', '[email protected]');

SQL - Abfrage

Als erstes möchten wir eine bestimmte E-Mail-Adresse in allen drei Tabellen auf einmal suchen. UNION verbindet die Tabellen zu einem Datenpool, über den dann die Abfrage läuft.

Listing 3:

1. /**
2. * UNION verbindet drei Tabellen
3. */
4. SELECT datenpool.email
5. FROM ( SELECT email FROM kunde
6. UNION
7. SELECT email FROM rechnung
8. UNION
9. SELECT email FROM adresspool
10. ) datenpool
11. WHERE datenpool.email = '[email protected]';
13. +-----------------------+
14. | email |
15. +-----------------------+
16. | [email protected] |
17. +-----------------------+
18. 1 row in set (0.00 sec)

Nun wissen wir zwar, dass die gesuchte E-Mail Adresse in einer der drei Tabellen existiert, aber nicht wie oft und in welcher Tabelle. Ein kurzer Blick auf die Testdaten zeigt weiterhin, dass die gesuchte E-Mail eigentlich mehrfach vorhanden ist. Der UNION Befehl entfernt doppelte Zeilen in der Ergebnismenge ähnlich einem DISTINCT. Mit UNION ALL werden alle Treffer (auch identische) ausgegeben.

Listing 4:

1. /**
2. * UNION ALL verbindet drei Tabellen
3. */
4. SELECT datenpool.email
5. FROM ( SELECT email FROM kunde
6. UNION ALL
7. SELECT email FROM rechnung
8. UNION ALL
9. SELECT email FROM adresspool
10. ) datenpool
11. WHERE datenpool.email = '[email protected]';
13. +-----------------------+
14. | email |
15. +-----------------------+
16. | [email protected] |
17. | [email protected] |
18. +-----------------------+
19. 2 row in set (0.00 sec)

Jetzt werden zwei Treffer gemeldet, aber wir wissen noch nicht, aus welcher Tabelle diese stammen. Die Abfrage muss folglich noch um zusätzliche Informationen erweitert werden.

Listing 5:

1. /**
2. * UNION ALL verbindet drei Tabellen
3. */
4. SELECT datenpool.quelle, datenpool.id,
5. datenpool.name, datenpool.email, datenpool.plz
6. FROM ( SELECT 'K' AS quelle, id, name1 AS name, email, plz FROM kunde
7. UNION ALL
8. SELECT 'R' AS quelle, id, NULL AS name, email, NULL AS plz FROM rechnung
9. UNION ALL
10. SELECT 'A' AS quelle, id, anschrift1 AS name, email, CAST(plz AS UNSIGNED INTEGER ) AS plz FROM adresspool
11. ) datenpool
12. WHERE datenpool.email = '[email protected]';
13. ORDER BY datenpool.quelle;
15. +--------+----+--------+-----------------------+------+
16. | quelle | id | name | email | plz |
17. +--------+----+--------+-----------------------+------+
18. | A | 3 | Frida | [email protected] | 99 |
19. | K | 2 | Frieda | [email protected] | 2 |
20. +--------+----+--------+-----------------------+------+
21. 2 rows in set, 1 warning (0.01 sec)

Die E-Mail [email protected] ist also in der Tabelle A (adresspool) und K (kunde) mit unterschiedlichen Namen vorhanden. Es könnte sich um eine Datenduplette handeln.

Resumee und Hinweise

Der Trick bei diesem UNION über mehrere Tabellen ist, gleichen Informationen (aber unterschiedliche Spaltennamen) identische Alias zu geben und fehlende Spalten mit NULL zu ergänzen (siehe "NULL AS name" bei Tabelle rechnung). Weiterhin kann es notwendig sein, Spaltentypen anzupassen (siehe CAST(plz AS UNSIGNED INTEGER ) für die Tabelle adresspool).

https://twiedmann.de