SQL Vertiefung: Keys und die Winzen-Relationen in Action

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.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

*