13 Mengenoperationen

Mengenoperationen in SQL ermöglichen es, die Ergebnisse mehrerer SELECT-Anweisungen auf verschiedene Arten zu kombinieren. Die drei wichtigsten Mengenoperationen sind UNION (Vereinigung), INTERSECT (Schnittmenge) und EXCEPT (Differenz).

13.1 Die Vereinigung mit UNION

UNION kombiniert die Ergebnisse mehrerer SELECT-Anweisungen zu einer einzigen Ergebnismenge. Dabei werden Duplikate entfernt, so dass jede Zeile nur einmal im Ergebnis vorkommt.

-- Grundlegende UNION
SELECT name, email FROM mitarbeiter
UNION
SELECT name, email FROM kunden;

-- Mit UNION ALL (behält Duplikate)
SELECT name, email FROM mitarbeiter
UNION ALL
SELECT name, email FROM kunden;

-- Komplexeres Beispiel
SELECT 
    'Mitarbeiter' AS typ,
    name,
    email 
FROM mitarbeiter
UNION
SELECT 
    'Kunde' AS typ,
    name,
    email 
FROM kunden
ORDER BY name;

-- Mit Filterung
SELECT name, email 
FROM mitarbeiter 
WHERE abteilung = 'Vertrieb'
UNION
SELECT name, email 
FROM kunden 
WHERE land = 'DE'
ORDER BY name;

Im ersten Beispiel werden die Namen und E-Mail-Adressen aller Mitarbeiter und Kunden kombiniert. Dabei werden Duplikate entfernt, d.h. Personen, die sowohl Mitarbeiter als auch Kunde sind, erscheinen nur einmal im Ergebnis.

Mit UNION ALL werden Duplikate beibehalten. Dies kann sinnvoll sein, wenn man weiß, dass es keine Überschneidungen zwischen den Tabellen gibt, oder wenn man die Duplikate bewusst im Ergebnis haben möchte.

Im komplexeren Beispiel wird zusätzlich eine Spalte typ hinzugefügt, die angibt, ob es sich um einen Mitarbeiter oder einen Kunden handelt. Die Ergebnismenge wird nach dem Namen sortiert.

Im letzten Beispiel werden die Ergebnismengen vor der Vereinigung gefiltert. Es werden nur Mitarbeiter aus der Vertriebsabteilung und Kunden aus Deutschland kombiniert.

Wichtige Regeln für UNION:

• Die SELECT-Anweisungen müssen die gleiche Anzahl von Spalten zurückgeben.
• Die Datentypen der Spalten müssen kompatibel sein, d.h. sie müssen in der gleichen Reihenfolge angegeben werden und implizit ineinander konvertierbar sein.
• Die Spaltennamen im Ergebnis entsprechen den Spaltennamen der ersten SELECT-Anweisung.
• Ein ORDER BY kann nur am Ende der gesamten UNION-Anweisung stehen und bezieht sich auf die Spalten der ersten SELECT-Anweisung.

13.2 Die Schnittmenge

Die Schnittmenge zweier Mengen enthält nur die Elemente, die in beiden Mengen vorkommen.

13.2.1 Mit INTERSECT

INTERSECT ist ein SQL-Operator, der nur die Zeilen zurückgibt, die in beiden SELECT-Anweisungen vorkommen.

-- Kunden, die auch Mitarbeiter sind
SELECT name, email FROM kunden
INTERSECT
SELECT name, email FROM mitarbeiter;

-- Mit zusätzlichen Bedingungen
SELECT name FROM kunden 
WHERE land = 'DE'
INTERSECT
SELECT name FROM mitarbeiter 
WHERE abteilung = 'Vertrieb';

Im ersten Beispiel werden die Namen und E-Mail-Adressen von Personen ermittelt, die sowohl Kunde als auch Mitarbeiter sind.

Das zweite Beispiel zeigt, wie man zusätzliche Bedingungen in die einzelnen SELECT-Anweisungen einbauen kann. Hier werden die Namen von Kunden aus Deutschland ermittelt, die gleichzeitig Mitarbeiter in der Vertriebsabteilung sind.

13.2.2 Mit Unterabfragen

Da MySQL den INTERSECT-Operator nicht direkt unterstützt, kann man stattdessen Unterabfragen verwenden, um die Schnittmenge zu ermitteln.

-- Alternative zu INTERSECT
SELECT name, email 
FROM kunden
WHERE (name, email) IN (
    SELECT name, email 
    FROM mitarbeiter
);

-- Mit EXISTS
SELECT k.*
FROM kunden k
WHERE EXISTS (
    SELECT 1
    FROM mitarbeiter m
    WHERE m.email = k.email
);

Im ersten Beispiel wird eine Unterabfrage mit IN verwendet. Hier werden die Namen und E-Mail-Adressen von Kunden ermittelt, die auch in der Tabelle der Mitarbeiter vorkommen.

Das zweite Beispiel verwendet EXISTS, um zu prüfen, ob es für jeden Kunden einen Mitarbeiter mit der gleichen E-Mail-Adresse gibt. Dies ist eine effizientere Variante, da die Unterabfrage für jeden Kunden nur prüfen muss, ob ein entsprechender Mitarbeiter existiert, ohne die tatsächlichen Werte zu vergleichen.

13.3 Die Differenzmenge

Die Differenz zweier Mengen A und B enthält alle Elemente, die in A, aber nicht in B vorkommen.

13.3.1 Mit EXCEPT

EXCEPT ist ein SQL-Operator, der nur die Zeilen zurückgibt, die in der ersten, aber nicht in der zweiten SELECT-Anweisung vorkommen.

-- Kunden, die keine Mitarbeiter sind
SELECT name, email FROM kunden
EXCEPT
SELECT name, email FROM mitarbeiter;

-- Mit zusätzlichen Bedingungen
SELECT name FROM kunden 
WHERE land = 'DE'
EXCEPT
SELECT name FROM mitarbeiter 
WHERE abteilung = 'Vertrieb';

Im ersten Beispiel werden die Namen und E-Mail-Adressen von Kunden ermittelt, die keine Mitarbeiter sind.

Das zweite Beispiel zeigt, wie man zusätzliche Bedingungen verwendet. Hier werden die Namen von Kunden aus Deutschland ermittelt, die nicht gleichzeitig Mitarbeiter in der Vertriebsabteilung sind.

13.3.2 Mit Unterabfragen

Da MySQL den EXCEPT-Operator nicht direkt unterstützt, kann man auch hier Unterabfragen verwenden.

-- Alternative zu EXCEPT
SELECT name, email 
FROM kunden
WHERE (name, email) NOT IN (
    SELECT name, email 
    FROM mitarbeiter
);

-- Mit NOT EXISTS
SELECT k.*
FROM kunden k
WHERE NOT EXISTS (
    SELECT 1
    FROM mitarbeiter m
    WHERE m.email = k.email
);

Im ersten Beispiel wird eine Unterabfrage mit NOT IN verwendet. Hier werden die Namen und E-Mail-Adressen von Kunden ermittelt, die nicht in der Tabelle der Mitarbeiter vorkommen.

Das zweite Beispiel verwendet NOT EXISTS, um zu prüfen, ob es für jeden Kunden keinen Mitarbeiter mit der gleichen E-Mail-Adresse gibt. Auch hier ist die Variante mit EXISTS bzw. NOT EXISTS effizienter als der Vergleich der tatsächlichen Werte.

13.4 UNION, INTERSECT und EXCEPT verstehen

Ein anschauliches Beispiel mit Mengen kann helfen, die Konzepte zu verstehen:

-- Alle aktiven Konten (UNION)
SELECT kontonummer, 'Giro' AS typ
FROM girokonten 
WHERE status = 'aktiv'
UNION
SELECT kontonummer, 'Spar' AS typ
FROM sparkonten 
WHERE status = 'aktiv';

-- Kunden mit beiden Kontoarten (INTERSECT)
SELECT kundenummer
FROM girokonten
WHERE EXISTS (
    SELECT 1
    FROM sparkonten s
    WHERE s.kundenummer = girokonten.kundenummer
);

-- Kunden nur mit Girokonto (EXCEPT)
SELECT kundenummer
FROM girokonten
WHERE NOT EXISTS (
    SELECT 1
    FROM sparkonten s
    WHERE s.kundenummer = girokonten.kundenummer
);

Im ersten Beispiel werden alle aktiven Konten ermittelt, sowohl Girokonten als auch Sparkonten. Die UNION-Operation kombiniert die Ergebnisse und fügt eine Spalte typ hinzu, die angibt, um welche Art von Konto es sich handelt.

Das zweite Beispiel zeigt, wie man Kunden ermittelt, die sowohl ein Girokonto als auch ein Sparkonto haben. Die Unterabfrage mit EXISTS prüft für jedes Girokonto, ob es ein Sparkonto mit der gleichen Kundennummer gibt.

Im dritten Beispiel werden Kunden ermittelt, die nur ein Girokonto, aber kein Sparkonto haben. Hier wird NOT EXISTS verwendet, um zu prüfen, ob es für jedes Girokonto kein entsprechendes Sparkonto gibt.

Praktische Anwendungsbeispiele verdeutlichen, wie Mengenoperationen in der Praxis eingesetzt werden können:

1. Zusammenführen von Daten aus verschiedenen Quellen:

-- Alle Kontakte
SELECT 
    name,
    email,
    'Mitarbeiter' AS quelle,
    abteilung AS zusatzinfo
FROM mitarbeiter
UNION
SELECT 
    name,
    email,
    'Kunde' AS quelle,
    land AS zusatzinfo
FROM kunden
ORDER BY name;

Hier werden Kontaktdaten aus den Tabellen mitarbeiter und kunden zusammengeführt. Die UNION-Operation kombiniert die Ergebnisse und fügt Spalten hinzu, die angeben, aus welcher Quelle die Daten stammen und welche zusätzlichen Informationen (Abteilung bei Mitarbeitern, Land bei Kunden) verfügbar sind.

2. Suche über mehrere Tabellen:

-- Globale Suche
SELECT 'Mitarbeiter' AS typ, name, id
FROM mitarbeiter 
WHERE name LIKE '%Schmidt%'
UNION
SELECT 'Kunde' AS typ, name, id
FROM kunden 
WHERE name LIKE '%Schmidt%'
UNION
SELECT 'Lieferant' AS typ, name, id
FROM lieferanten 
WHERE name LIKE '%Schmidt%';

Dieses Beispiel zeigt, wie man eine Suche über mehrere Tabellen hinweg durchführen kann. Hier werden Mitarbeiter, Kunden und Lieferanten gesucht, deren Name ‘Schmidt’ enthält. Die UNION-Operation kombiniert die Ergebnisse und fügt eine Spalte typ hinzu, die angibt, aus welcher Tabelle der Datensatz stammt.

3. Erstellung von Berichten:

-- Jahresübersicht
SELECT 
    'Januar' AS monat,
    COUNT(*) AS anzahl,
    SUM(betrag) AS umsatz
FROM bestellungen
WHERE MONTH(datum) = 1
UNION ALL
SELECT 
    'Februar',
    COUNT(*),
    SUM(betrag)
FROM bestellungen
WHERE MONTH(datum) = 2
-- ... weitere Monate
ORDER BY 
    FIELD(monat, 
        'Januar', 'Februar', 'März', 
        'April', 'Mai', 'Juni',
        'Juli', 'August', 'September',
        'Oktober', 'November', 'Dezember'
    );

In diesem Beispiel wird ein Jahresüberblick erstellt, der für jeden Monat die Anzahl der Bestellungen und den Umsatz anzeigt. Die einzelnen Monate werden durch separate SELECT-Anweisungen ermittelt und mit UNION ALL kombiniert. Hier wird UNION ALL verwendet, da jeder Monat nur einmal vorkommt und Duplikate erwünscht sind.

Die Ergebnismenge wird mit ORDER BY und der FIELD-Funktion in der richtigen Reihenfolge der Monate sortiert.

Tipps zur Optimierung und Behandlung von NULL-Werten runden das Thema ab:

1. Performance optimieren:

-- Indizes für häufig verwendete Spalten
CREATE INDEX idx_name_email 
ON kunden(name, email);

-- Temporäre Tabellen für Zwischenergebnisse
CREATE TEMPORARY TABLE temp_ergebnis AS
SELECT * FROM (
    -- UNION-Abfrage
) AS union_query;

Um die Performance von Mengenoperationen zu verbessern, können Indizes für häufig verwendete Spalten erstellt werden. Dadurch kann die Datenbank die Daten schneller finden und kombinieren.

Eine weitere Möglichkeit ist die Verwendung von temporären Tabellen, um Zwischenergebnisse zu speichern. Dadurch muss die UNION-Abfrage nicht jedes Mal neu ausgeführt werden, wenn das Ergebnis verwendet wird.

2. Datenqualität sicherstellen:

-- Vor UNION: Daten bereinigen
SELECT 
    TRIM(name) AS name,
    LOWER(email) AS email
FROM mitarbeiter
UNION
SELECT 
    TRIM(name) AS name,
    LOWER(email) AS email
FROM kunden;

Vor der Verwendung von Mengenoperationen ist es wichtig, die Datenqualität sicherzustellen. Hier werden z.B. führende und nachfolgende Leerzeichen mit TRIM entfernt und E-Mail-Adressen mit LOWER in Kleinbuchstaben umgewandelt. Dadurch werden Duplikate vermieden, die durch inkonsistente Schreibweisen entstehen können.

3. Nullwerte behandeln:

-- NULL-Werte durch Standardwerte ersetzen
SELECT 
    COALESCE(name, 'Unbekannt') AS name,
    COALESCE(email, 'keine@email.de') AS email
FROM mitarbeiter
UNION
SELECT 
    COALESCE(name, 'Unbekannt'),
    COALESCE(email, 'keine@email.de')
FROM kunden;

NULL-Werte können bei Mengenoperationen zu unerwartetem Verhalten führen. Mit der COALESCE-Funktion können NULL-Werte durch Standardwerte ersetzt werden. Hier werden fehlende Namen durch ‘Unbekannt’ und fehlende E-Mail-Adressen durch ‘keine@email.de’ ersetzt.

Mengenoperationen sind ein mächtiges Werkzeug, um Daten aus verschiedenen Quellen zu kombinieren und zu vergleichen. Sie ermöglichen es, komplexe Analysen durchzuführen und Erkenntnisse zu gewinnen, die mit einzelnen Abfragen nicht möglich wären.

Um Mengenoperationen effektiv zu nutzen, ist es wichtig, die Datenqualität sicherzustellen, die Performance zu optimieren und NULL-Werte korrekt zu behandeln. Mit der richtigen Vorbereitung und den passenden Techniken können Mengenoperationen dazu beitragen, die Möglichkeiten von SQL voll auszuschöpfen.