MySQL: Komplexe Abfragen mit Joins meistern

Wer für eine bestimmte Aufgabe nacheinander mehrere Datenbankabfragen an einen SQL-Server stellt, sollte stutzig werden. Denn das weist fast immer auf un-optimierte Abfragen hin, deren Einzeloperationen sich zu einer einzigen Abfrage zusammenfassen lassen. Das bringt dramatische Performance-Gewinne, egal, ob Daten geändert oder lediglich aus der Datenbank geholt werden sollen.

Ein Beispiel: Sie holen zuerst Artikelnummern aus einem Bestellsystem. Dann fragen Sie in einer Schleife Detailinformationen zu den Nummern aus einer anderen Tabelle ab. Statt diese Aufgabe mit zwei Abfragen zu erledigen, können Sie das auch in ein Statement packen. Joins machen es möglich.

Die Grundform: Der Cross-Join

Der so genannte Cross-Join ist die Grundform aller Tabellenverknüpfungen in SQL. Alle weiteren Arten sind lediglich Verfeinerungen oder Erweiterungen davon. Betrachten Sie folgendes Statement:

Der Equi-Join

Den am häufigsten verwendeten Join-Typ haben Sie wahrscheinlich schon selbst einmal eingesetzt. In unserem Beispiel gibt er Details zu den Artikeln einer Bestellung aus. In der Tabelle posten ist jeder bestellte Artikel mit Menge und Artikelnummer sowie der Bestellnummer aufgeführt, während die Tabelle artikel Detailinformationen zu jedem Artikel enthält.

SELECT p.bestellnr, p.menge, p.artikelnr, a.bezeichnung

FROM posten p, artikel a

WHERE p.artikelnr = a.artikelnr

Diese Abfrage nennt man einen Equi-Join. Er ist im Prinzip ein Cross-Join, bringt aber nur die Kombinationen, bei denen die Artikelnummer in posten mit der Artikelnummer in artikel identisch ist. Verpatzt man die WHERE-Bedingung, so dass die gewünschte Einschränkung nicht erfolgt, weist das Ergebnis zu viele Datensätze auf. Sie bekommen dann im Beispiel schlimmstenfalls zu jedem Posten zig Datensätze, die ihn mit jedem der Artikeldetails kombiniert. So ein Phänomen ist immer ein Hinweis, dass Sie die Join-Bedingung noch mal ansehen sollten.

Der Left-Join

Der Left-Join ergänzt die Ergebnismenge eines Equi-Join, um die Datensätze aus der ersten Tabelle, die keinen einzigen passenden Eintrag in der zweiten Tabelle haben. Erkennbar sind diese Datensätze daran, dass alle Feldwerte, die sich auf die zweite Tabelle beziehen, den Sonderwert NULL haben. Um aus der obigen Abfrage einen Left-Join zu machen, formulieren Sie um:

SELECT p.bestellnr, p.menge, p.artikelnr, a.bezeichnung FROM posten p

LEFT JOIN artikel a ON p.artikelnr = a.artikelnr

Ist in Ihrem Bestellsystem die Datenintegrität gegeben, wird das Ergebnis identisch zur vorigen Abfrage sein. Wurde aber versehentlich der Artikel mit der Nummer 4711 gelöscht, dann erhalten Sie ein Ergebnis wie

Ein Left-Join

Weil MySQL die verloren gegangene Artikelnummer nicht finden kann, wird das Feld bezeichnung, das als einziges aus der Artikeltabelle kommt, mit dem Wert NULL gefüllt. Dieser Wert steht in SQL für “nicht besetzt” und hat nichts mit der Ziffer 0 zu tun. Den NULL-Wert könnten Sie in der Auswertung dieser Abfrage für eine Meldung des Problems nutzen. Bei einer Verwendung des normalen EquiJoins wäre die zweite Zeile einfach unterdrückt worden, da eben der Bezug zu artikel hier nicht gegeben ist. Wie jede andere Abfrage auch lassen sich Joins gruppieren oder weiter einschränken. Um beispielsweise nur die Bestellungen herauszufinden, die fehlende Artikelbezüge enthalten, prüfen Sie das Ergebnis auf einen NULL-Wert in einer der Spalten der Tabelle rechts vom Ausdruck

LEFT JOIN: SELECT p.bestellnr, p.menge, p.artikelnr, a.bezeichnung FROM posten p LEFT JOIN artikel a ON p.artikelnr = a.artikelnr WHERE a.bezeichnung IS NULL

Selbstreferenz: Der Self-Join

Ein Self-Join, auch Auto-Join genannt, ist eine Abfrage, bei der eine Tabelle mit sich selbst verknüpft wird. Das ist immer dann nützlich, wenn in einer Tabelle hierarchische Daten abgelegt sind. So gibt es beispielsweise im CMS Typo3 eine Tabelle pages, die die Seiten eines Webangebots speichert. Egal, ob es sich um die Eingangsseite handelt oder den allerletzten Beitrag, irgendwo tief im Webangebot: Jede Seite ist hier eingetragen. Die Beziehung untereinander wird durch ein Feld pid hergestellt, das die Parent-Id vorhält, also die Seite, die der aktuellen übergeordnet ist. Jede Seite hat ein Autoincrement-Feld uid, das eine eindeutige Identifizierung erlaubt. Die Startseite erkennt man daran, dass ihre pid den Wert 0 hat, also keine übergeordnete Seite (“Vater”) existiert.

Angenommen, Sie möchten alle Seiten auf der zweiten Ebene unterhalb der Startseite ermitteln. Dazu fragen Sie MySQL, welche Seiten als Vater Seiten haben, deren Vater wiederum die pid 0 besitzt. In SQL formuliert sieht die Abfrage so aus:

SELECT p2.uid, p2.title FROM pages AS p1, pages AS p2

WHERE p1.pid =0 AND p2.pid = p1.uid

Die Tabelle pages kommt also in der Abfrage doppelt vor. Darum muss jede pages durch einen Alias gekennzeichnet werden, um sie gezielt ansprechen zu können. Hier haben wir p1 für die Seiten der Ebene direkt unter der Root-Page verwendet und p2 für die Seiten auf der nächst tieferen Ebene.

Joins bei UPDATE und DELETE

Manchmal braucht man bei einem UPDATE oder DELETE eine Bedingung, die sich nicht auf die Tabelle selbst bezieht, sondern sich durch eine Beziehung zu einer anderen Tabelle ausdrückt. In einer Buchdatenbank möchten Sie zum Beispiel alle Bücher um zehn Prozent verteuern, die von einem ausländischen Verlag herausgegeben werden. So etwas lässt sich mit einer Abfrage erledigen:

UPDATE buecher b, verlage v SET b.preis= b.preis*1.1 WHERE b.verlagsid=v.verlagsid AND v.inland='N'

Um dagegen alle ausländischen Bücher zu entfernen, schreiben Sie:

DELETE b FROM buecher b, verlage v WHERE b.verlagsid= v.verlagsid AND v.inland='N'

Entscheidend dafür, wo gelöscht wird, ist der Alias hinter dem Schlüsselwort DELETE. Um beispielsweise gleich mit den Büchern auch die ausländischen Verlage aus der Datenbank zu entfernen, schreiben Sie folgende SQL-Abfrage:

DELETE b,v FROM buecher b, verlage v WHERE b.verlagsid= v.verlagsid AND v.inland='N'

Dieses Verfahren funktioniert mit allen Join-Typen und benötigt eine MySQLVersion ab 4.0.4.

Select statt Join

Mit den ab MySQL 4.1 eingeführten Subselects können Joins anders formuliert werden. Um herauszubekommen, welche Kunden noch nie bestellt haben, schreiben Sie als Join:

SELECT k.*,b.kundennr FROM kunden k LEFT JOIN bestellung b

ON b.kundennr=k.kundennr WHERE b.kundennr IS NULL

Mit einem Subselect sieht die Abfrage dagegen so aus:

SELECT * FROM kunden k

WHERE NOT EXISTS (SELECT * FROM bestellung b WHERE b.kundennr= k.kundennr)

Das ist verständlicher, heißt es doch: “Gib mir jeden Kunden, für den es keine Bestellung gibt, die seine Kundennummer trägt.”

Weitere Operatoren für Subselects sind Vergleiche von Werten mit Ergebnisabfragen, wie in

SELECT * FROM posten WHERE artikelnr=

(SELECT artikelnr FROM artikel WHERE bezeichnung='Thermostat')

Aber Achtung: Ermittelt die zweite Select-Abfrage mit “=” mehr als ein Ergebnis, gibt es einen Fehler. Um etwa alle Posten zu erhalten, die einen Artikel über einer Preisgrenze beinhalten, verwenden Sie besser den IN-Operator:

SELECT * FROM posten WHERE artikelnr IN

(SELECT artikelnr FROM artikel WHERE preis> 1000)

Noch eine Warnung: Subselects sind zwar verständlicher, aber der MySQL-Optimizer kommt mit Joins besser zurecht.

Ähnliche Beiträge