Ich musste erstmal die Tabellenstruktur nacharbeiten, aber jetzt geht’s dann weiter mit Beispielen für die Winzen-Datenbank zur Masterarbeit von @derguenther. Habt Ihr die unterschiedlichen Abfragen noch drauf? Wenn nicht, lest noch einmal schnell nach: Blogartikel zu den SQL-Basics. Und wer das Ganze mit den gleichen Daten nachspielen will, kann in meinem Bitbucket-Repo die SQL-Rohdaten auschecken oder herunterladen und in seinem mySQL-Server laden.
Primärschlüssel und Sekundärschlüssel
Zwei zentrale Begriffe in der Datenbanksprache sind “Primärschlüssel” und “Sekundär-/ Fremdschlüssel”, Primary Key und Foreign Key.
Das ist an dieser Stelle ein wichtiger neuer Begriff, weil wir gleich mit den beiden Daten wieder zusammenführen.
Der Primärschlüssel ist das Merkmal, an dem wir einen Datensatz eindeutig erkennen.
Wir können diesen Wert nun auf anderen Datensätzen verwenden, um auf diesen Datensatz zu verweisen: Der Verweis heißt Sekundärschlüssel. Einfach erstmal so hinnehmen, ihr seht gleich wie das gemeint ist.
Select
Ein einfacher Select erlaubt uns die Abfrage von Werten aus der Datenbank:
SELECT FirstName, LastName FROM Person LIMIT 10
Die Treffermenge sieht wie folgt aus (beschnitten auf 10 Resultate mittels LIMIT 10):
| FirstName | LastName |
|---|---|
| unbekannt | |
| unbekannt | |
| ? | Geredien |
| ? | Göpfert |
| Albert | Becker |
| Alfred | Otting |
| Anne | Grunow |
| August | Kister |
| August | Lücke |
| August | Winzen |
Passt soweit. Aber wer von denen ist denn überhaupt im späteren Verlauf festgenommen worden?
Subselect
Übertragen in die Tabellenstruktur heißt das: Wer aus der Tabelle Person taucht auch in der Tabelle LegalAction mit seiner PersonId auf.
Ein Subselect erlaubt es uns, das Ergebnis einer Abfrage in die Suchkriterien der nächsten Abfrage zu leiten. Das geht so:
SELECT FirstName, LastName FROM Person WHERE PersonID IN (SELECT DISTINCT PersonId FROM LegalAction WHERE ActionType = "Festnahme") ORDER BY LastName ASC LIMIT 10
Und das liefert uns:
| FirstName | LastName |
|---|---|
| Albert | Becker |
| Franz | Becker |
| Becker | Bernhardine |
| Siegfried | Drupp |
| Rudolf | Dunker |
| Werner | Engel |
| Walter | Gawehn |
| Hermann | Härtel |
| Bruno | Hüsing |
| Eugen | Jungmann |
Jetzt sollen Daten über Verhaftungen dazu. Die habe ich eine zweite Tabelle namens LegalAction gebracht, um damit später eine Zeitleiste zeichnen zu können. Also – Personen in Tabelle 1, Exekutivmaßahmen in Tabelle 2 – geht einfach?
Kreuzprodukt
SELECT p.FirstName, p.LastName, l.ActionType, l.ActionDate
FROM Person p, LegalAction l
LIMIT 10
Ok – hier kommen zwei Dinge ins Spiel: Zum einen verwende ich einen Tabellen-Alias. Das ist hier zwar nicht unbedingt notwendig, weil jede Spalte nur einmal vorkommt, aber übersichtlicher ist es dennoch. “Person p” bedeutet: Die Tabelle Person soll den Alias p tragen. p.FirstName sagt dann aus: Die Spalte FirstName aus Tabelle p, deren eigentlicher Name Person ist. Person.FirstName würde auch funktionieren.
Aber das Ergebnis ist eine Katastophe:
| FirstName | LastName | ActionType | ActionDate |
|---|---|---|---|
| unbekannt | Festnahme | 1940-08-15 | |
| unbekannt | Festnahme | 1940-06-27 | |
| unbekannt | Festnahme | 1940-06-27 | |
| unbekannt | Festnahme | 1940-08-16 | |
| unbekannt | Festnahme | 1940-06-27 | |
| unbekannt | Festnahme | 1940-07-09 | |
| unbekannt | Festnahme | 1940-07-13 | |
| unbekannt | Festnahme | 1936-03-05 | |
| unbekannt | Festnahme | 1940-06-27 | |
| unbekannt | Festnahme | 1940-06-28 | |
Was ist da schiefgelaufen? Kreuzprodukte paaren JEDE Zeile der 1. Tabelle mit jeder Zeile der 2. Tabelle. Das müssen wir also besser lösen.
Ich korrigere also wie folgt:
SELECT p.FirstName, p.LastName, l.ActionType, l.ActionDate
FROM Person p, LegalAction l
WHERE p.PersonId = l.PersonId
LIMIT 10
Jetzt macht der Tabellenalias auch Sinn, denn ich weise an, nur ein Kreuzprodukt zu bilden, falls die PersonId in der Tabelle l (also “LegalAction”) übereinstimmt mit PersonId in der Tabelle Person.
Das Ergebnis ist wie folgt:
| FirstName | LastName | ActionType | ActionDate |
|---|---|---|---|
| Albert | Becker | Festnahme | 1940-08-15 |
| Franz | Becker | Festnahme | 1940-06-27 |
| Becker | Bernhardine | Festnahme | 1940-06-27 |
| Siegfried | Drupp | Festnahme | 1940-08-16 |
| Rudolf | Dunker | Festnahme | 1940-06-27 |
| Werner | Engel | Festnahme | 1940-07-09 |
| Walter | Gawehn | Festnahme | 1940-07-13 |
| Hermann | Härtel | Festnahme | 1936-03-05 |
| Bruno | Hüsing | Festnahme | 1940-06-27 |
| Eugen | Jungmann | Festnahme | 1940-06-28 |
Damit kann man jetzt schon ganz gut leben. So, wie es geschrieben ist, arbeite ich hier mit einem gefilterten Kreuzprodukt. Aber das ist nur die halbe Wahrheit, denn die Datenbank erkennt, was ich hier mache und nimmt einen INNER JOIN vor (gleich mehr dazu).
Inner Join
Zur Erinnerung – Zwei Tabellen anhand eines Kriteriums zusammenzuführen nennt man JOIN. Und Inner Join sagt man, wenn man alle Werte aus Tabelle A mit einer oder mehr Entsprechungen in Tabelle B sucht.
SELECT p.FirstName, p.LastName, l.ActionType, l.ActionDate
FROM Person p
JOIN LegalAction l ON p.PersonId = l.PersonId
LIMIT 10
Und hier treten dann die Schlüssel in Aktion: p.PersonId ist die eindeutige Bezeichnung für eine Person. Also ein Primärschlüssel – er bezeichnet den Datensatz, auf dem er steht (in der Syntax: Das Subjekt. Die Person “PersonId” heißt mit Vornamen Albert)
l.PersonId bezeichnet die Person, auf die von dem Datensatz verwiesen wird (im Satz das Objekt: Die Polizei hat “PersonId” verhaftet).
Das Ergebnis kennt ihr – jetzt haben wir einen expliziten JOIN gemacht und bekommen wieder :
| FirstName | LastName | ActionType | ActionDate |
|---|---|---|---|
| Albert | Becker | Festnahme | 1940-08-15 |
| Franz | Becker | Festnahme | 1940-06-27 |
| Becker | Bernhardine | Festnahme | 1940-06-27 |
| Siegfried | Drupp | Festnahme | 1940-08-16 |
| Rudolf | Dunker | Festnahme | 1940-06-27 |
| Werner | Engel | Festnahme | 1940-07-09 |
| Walter | Gawehn | Festnahme | 1940-07-13 |
| Hermann | Härtel | Festnahme | 1936-03-05 |
| Bruno | Hüsing | Festnahme | 1940-06-27 |
| Eugen | Jungmann | Festnahme | 1940-06-28 |
Left Outer Join
Manchmal will man ganz gerne auch wissen, wenn es Datensätze gibt, die in der 2. Tabelle keine Entsprechung haben. Hier z.B. wäre eine Liste inklusive Angaben, ob und wann eine Person verhaftet wurde gefragt. Das lösen wir mit einem “Outer Join”, also einem Join, der auch fehlende Entsprechungen auf einer Seite berücksichtig. Die Abfrage lautet:
SELECT p.FirstName, p.LastName, l.ActionType, l.ActionDate
FROM Person p
LEFT OUTER JOIN LegalAction l ON
p.PersonId = l.PersonId
LIMIT 10
Und das Ergebnis ist:
| FirstName | LastName | ActionType | ActionDate |
|---|---|---|---|
| unbekannt | NULL | NULL | |
| Albert | Becker | Festnahme | 1940-08-15 |
| Franz | Becker | Festnahme | 1940-06-27 |
| Becker | Bernhardine | Festnahme | 1940-06-27 |
| Erich | Blome | NULL | NULL |
| Walter | Chilla | NULL | NULL |
| Karl-Heinz | Dahms | NULL | NULL |
| Ernst | Domrös | NULL | NULL |
| Otto | Dorn | NULL | NULL |
| Siegfried | Drupp | Festnahme | 1940-08-16 |
Moment – müsste die Zelle denn nicht leer sein, wenn jemand nicht festgenommen wurde? Stattdessen kommt NULL? Denkt dran: NULL ist kein Wert – NULL bedeutet: Es existiert hier nichts (nämlich: Kein Datensatz in Tabelle LegalAction). Und wenn der Datensatz selbst NULL ist, geben auch alle Felder NULL zurück.
Right Outer Join
Mit dem Right Outer Join könnten wir klären, ob wir zu jeder Festnahme auch die Person kennen. Das Ergebnis wäre mit den Musterdaten hier identisch mit dem Inner Join (warum? weil @derguenther eben halt nicht alle Festnahmen des Jahres 1940 aufgenommen hat, sondern nur die Winzengruppe-relevanten.