SQL Subqueries – Wiederholung

Eine Subquery ist eine Abfrage innerhalb einer anderen Abfrage (= verschachtelte Abfrage, nested query).

Sie liefert ein Zwischenergebnis, das von der äußeren Abfrage weiterverarbeitet wird.

Typische Einsatzbereiche:

• in WHERE als Filter
• in SELECT für berechnete Spalten (Skalar-Subqueries)
• in FROM als virtuelle Tabelle (derived tables)
• in HAVING um Gruppenergebnisse zu vergleichen

Subquery in WHERE

Beispiel: Finde alle Produkte, deren Preis über dem Durchschnittspreis liegt.

SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice > (SELECT AVG(UnitPrice) FROM Products);

• Die Subquery (SELECT AVG(UnitPrice) …) berechnet den Durchschnittspreis.
• Die äußere Query filtert Produkte, die teurer sind.

In WHERE oder auch in HAVING kann man Subqueries auch mit anderen Operatoren verknüfen, wie z.B. IN, ALL, ANY, EXISTS etc.

Beispiel: Zeige alle Bestellungen von Kunden aus Deutschland.

SELECT OrderID, CustomerID, OrderDate
FROM Orders
WHERE CustomerID IN (
    SELECT CustomerID
    FROM Customers
    WHERE Country = 'Germany'
);

• Die Subquery liefert alle deutschen Kunden.
• Die äußere Query zeigt deren Bestellungen.

Subquery in SELECT

Beispiel: Zeige Produkte mit Anzahl der Bestellungen.

SELECT ProductName,
       (SELECT COUNT(*)
        FROM [Order Details] od
        WHERE od.ProductID = p.ProductID) AS Bestellungen
FROM Products p;

• Pro Produkt wird in der Subquery gezählt, wie oft es in Bestellungen vorkommt.

Subquery in FROM (Derived Table)

Beispiel: Durchschnittspreis pro Kategorie.

Schritt 1: Bestimme den Durschnittspreis einer bestimmten Kategorie.

SELECT CategoryID, AVG(UnitPrice) AS Durchschnittspreis
FROM Products
GROUP BY CategoryID;

Schritt 2: Dieses Ergebnis kann als Subquery wiederverwendet werden. Dazu wird eine virtuelle Tabelle t erstellt, die z.B. für einen Join weiterverwendet wird.

SELECT c.CategoryName, t.Durchschnittspreis
FROM (
    SELECT CategoryID, AVG(UnitPrice) AS Durchschnittspreis
    FROM Products
    GROUP BY CategoryID
) t
JOIN Categories c ON t.CategoryID = c.CategoryID;

• Die Subquery liefert eine tempärere Zwischentabelle t.
• Dann erfolgt der Join mit Categories.

Subquery in HAVING

Beispiel: Zeige alle Kategorien, deren durchschnittlicher Produktpreis höher ist als der Durchschnittspreis aller Produkte. (Also eine Liste von Kategorien, die „teurer“ sind als der Gesamtdurchschnitt.)

SELECT CategoryID, AVG(UnitPrice) AS AvgPrice
FROM Products
GROUP BY CategoryID
HAVING AVG(UnitPrice) > (
    SELECT AVG(UnitPrice)
    FROM Products
);

• GROUP BY CategoryID gruppiert die Produkte pro Kategorie.

• AVG(UnitPrice) berechnet den Durchschnittspreis je Kategorie.

• Die Subquery (SELECT AVG(UnitPrice) FROM Products) liefert den Gesamtdurchschnitt über alle Produkte.

Hier noch ein etwas komplexeres Beipiel:

Zeige Kunden, deren gesamte Frachtkosten über dem Durchschnitt aller Kunden liegt:

Schritt 1: Bestimmte die gesamten Frachtkosten pro Kunde:

SELECT SUM(Freight) AS CustomerTotal
FROM Orders
GROUP BY CustomerID

Schritt 2: Ermittle den Durchschnitt aller Kunden-Gesamtsummen mit einer temporären Tabelle:

SELECT AVG(CustomerTotal)
FROM (
    SELECT SUM(Freight) AS CustomerTotal
    FROM Orders
    GROUP BY CustomerID
) t

Schritt 3: Filtere nur die Kunden, deren Summe größer ist als dieser Durchschnitt:

SELECT CustomerID, SUM(Freight) AS TotalFreight
FROM Orders
GROUP BY CustomerID
HAVING SUM(Freight) > (
    SELECT AVG(CustomerTotal)
    FROM (
        SELECT SUM(Freight) AS CustomerTotal
        FROM Orders
        GROUP BY CustomerID
    ) t
);

Tipps & Stolperfallen

• Verwende IN für Mengenvergleiche, = für einen einzelnen Wert.
• Prüfe, ob eine Aggregation (MAX, AVG, COUNT) innerhalb der Subquery sinnvoll ist.
• Subqueries können langsam sein und sollten oft besser mit JOIN ersetzt werden. Aber dazu gleich mehr.

Subqueries vs. Joins

Subqueries sind „Abfragen in Abfragen“. Sie helfen, komplexe Bedingungen zu formulieren, können aber oft auch durch Joins ersetzt werden.

Beispiel: Gesucht sind alle Bestellungen von Kunden aus Deutschland.

Subquery (IN):

SELECT o.OrderID, o.CustomerID, o.OrderDate
FROM Orders o
WHERE o.CustomerID IN (
  SELECT c.CustomerID
  FROM Customers c
  WHERE c.Country = 'Germany'
);

Join:

SELECT o.OrderID, o.CustomerID, o.OrderDate
FROM Orders o
JOIN Customers c ON c.CustomerID = o.CustomerID
WHERE c.Country = 'Germany';

Beide Lösungen liefern das gleiche Ergebnis, aber der Join erscheint intuitiver bzw. logischer - vor allem, wenn Spalten beider beteiligten Tabellen selektiert werden würden. Aber: Der SQL-Optimizer der Datenbank formt meist eine Subquery sowieso zu einem Join um, da dieser schneller ausgeführt werden kann. Somit sind Subqueries (die automatisch zu Joins umgeformt werden) schlussendlich nicht wirklich langsamer als Joins.

Hinweis

Eine Query nennt man eine korrelierte Subquery, wenn für jede Zeile aus der äußeren Tabelle die innere Query neu ausgeführt werden muss.

Das klingt ziemlich ineffizient:

Bei 10.000 Kunden → 10.000 Subquery-Ausführungen.

In der Praxis würde das extrem langsam sein. Deshalb optimieren auch in diesem Fall fast alle modernen Datenbanken solche Abfragen automatisch zu einem Join um.

Ein weiteres Beispiel: Finde alle Kunden, die keine Bestellungen haben.

1. Lösung mit Subquery

SELECT c.CustomerID, c.CompanyName
FROM Customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.CustomerID = c.CustomerID
);

Diese Lösung ist klar und direkt: „Nimm alle Kunden, für die es keine Bestellung gibt.“

2. Lösung mit JOIN

SELECT c.CustomerID, c.CompanyName
FROM Customers c
LEFT JOIN Orders o ON o.CustomerID = c.CustomerID
GROUP BY c.CustomerID, c.CompanyName
HAVING COUNT(o.OrderID) = 0;

Diese Lösung ist umständlicher, weil mit Joins etwas “um die Ecke gedacht” werden muss:

1. Zuerst wird gejoint, auch wenn keine Bestellung existiert (LEFT JOIN).

2. Dann wird pro Kunde gezählt, wie viele Orders es gibt. Mit COUNT(o.OrderID) = 0 werden alle Kunden die keine Bestellungen hatten gefunden.

Beide Lösungen liefern das gleiche Ergebnis, aber besser lesbar ist in diesem Fall die Subquery.

Weitere Arten von Subqueries / Joins

Anti-Join

Eine Query, die dazu dient, Datensätze aus einer Tabelle zu finden, die keine passenden Zeilen in einer anderen Tabelle haben, nennt man einen Anti-Join. Das Beispiel von oben “Finde alle Kunden, die keine Bestellungen haben.” ist ein Anti-Join. Ein Anti-Join wird typischerweise umgesetzt mit NOT EXISTS Subqueries oder LEFT JOIN … IS NULL.

Semi-Join

Ein Semi-Join (=halber Join), funktioniert wie ein normaler Join, enthält aber nur „die Hälfte“ (=die linke Seite) im Ergebnis. Oder anders gesagt: Ein Semi-Join zweier Tabellen A und B enthält nur Spalten der Tabelle A aber keine Spalten der Tabelle B.

SQL kennt keinen eigenen SEMI JOIN-Operator, aber ein Semi-Join kann mit

• EXISTS (…) bzw. IN (…) Subqueries

• oder JOIN + DISTINCT

erstellt werden.

Beispiel: Welche Kunden haben Bestellungen?

SELECT c.CustomerID, c.CompanyName
FROM Customers c
WHERE EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.CustomerID = c.CustomerID
);

Ein Semi-Join ist quasi das logische Gegenteil vom Anti-Join:

Semi-Join = „gib mir alle mit Beziehung“

Anti-Join = „gib mir alle ohne Beziehung“

Self-Join

Join einer Tabelle mit sich selbst, um Beziehungen innerhalb der Tabelle/Entität selbst zu untersuchen - typischerweise für Hierarchien oder Vergleich von Zeilen innerhalb derselben Tabelle.

Beispiel: Welche Angestellten sind Manager?

Ein Manager ist ein Angestellter, der von anderen Angestellten über “ReportsTo” referenziert wird.

SELECT e1.EmployeeID, e1.FirstName, e2.FirstName AS Manager
FROM Employees e1
JOIN Employees e2 ON e1.ReportsTo = e2.EmployeeID;

Zusammenfassung

Der Optimizer der Datenbank macht meist aus einer Subquery einen Join, sodass wir uns um die Performance vorerst keine Sorgen machen müssen. Details werden wir jedoch beim Thema Datenbank-Tuning kennenlernen.

Wir werden noch im Detail kennenlernen, ob für kronkrete Anwendungsfälle eine Subqueries oder ein Joins besser geeignet sind, aber generell kann gesagt werden:

• Joins: Sind gut für das „Zusammenführen“ von Tabellen, v. a. wenn man Mengen verknüpfen will.

• Subqueries: Sind gut für „Bedingungen“ und „Berechnungen“, die nicht direkt joinbar sind.

• EXISTS/NOT EXISTS: ist mit Subquery besser/schöner als ein Join mit IS NULL bzw. IS NOT NULL und COUNT 0, wenn es nur um Existenz/Nicht-Existenz geht.

• Skalar-Subqueries (für berechnete Spalten) sind nur als Subquery sinnvoll, Join wäre unnötig kompliziert.