Beispiel 1:

Es soll ermittelt werden, was alles mit einer Magnetkarte geschah.
Betrachten wir zunächst nur die Tabelle magnetkarten für eine beliebige Kartennummer:

select
 magnetkarten_wann as wann,
 magnetkarten_karte as karte,
 magnetkarten_version as version,
 magnetkarten_status_id as status_id,
 person_nr
from magnetkarten
where magnetkarten_karte=101;

+---------------------+-------+---------+-----------+-----------+
| wann                | karte | version | status_id | person_nr |
+---------------------+-------+---------+-----------+-----------+
| 1990-01-01 00:00:00 |   101 |       0 |         2 |         0 |
| 1994-12-08 09:54:00 |   101 |       0 |         4 |       978 |
| 2000-09-08 11:59:00 |   101 |       0 |         7 |       978 |
| 2000-09-08 11:59:00 |   101 |      10 |        15 |         0 |
| 2000-09-25 12:59:50 |   101 |      10 |         4 |     16935 |
+---------------------+-------+---------+-----------+-----------+
5 rows in set (0.00 sec)

• zur Magnetkarte 101 sind 5 Ereignisse festgehalten
• status_id und person_nr sind wenig aussagekräftig
• Fremdschlüssel --> Verknüpfung mit weiteren Tabellen (magnetkarten_status und person)

select
 magnetkarten_wann as wann,
 magnetkarten_karte as karte,
 magnetkarten_version as version,
 magnetkarten_status_name as status_name,
 person_name
from magnetkarten,magnetkarten_status,person
where magnetkarten_karte=101
  and magnetkarten.magnetkarten_status_id = magnetkarten_status.magnetkarten_status_id
  and magnetkarten.person_nr = person.person_nr;

+---------------------+-------+---------+-------------+-------------+
| wann                | karte | version | status_name | person_name |
+---------------------+-------+---------+-------------+-------------+
| 1994-12-08 09:54:00 |   101 |       0 | Ausgabe     | Kaiser      |
| 2000-09-08 11:59:00 |   101 |       0 | Verlust     | Kaiser      |
| 2000-09-25 12:59:50 |   101 |      10 | Ausgabe     | Härtel      |
+---------------------+-------+---------+-------------+-------------+

• status_id und person_nr sind durch aussagekräftige Angaben ersetzt
• dafür fehlen aber zwei Zeilen
• Grund: keine Person mit der person_nr = 0
• Nur jene Datensätze werden angezeigt, die in beiden Tabellen vorkommen (INNER JOIN)

select
 magnetkarten_wann as wann,
 magnetkarten_karte as karte,
 magnetkarten_version as version,
 magnetkarten_status_name as status_name,
 person_name
from magnetkarten,magnetkarten_status
 left outer join person on magnetkarten.person_nr = person.person_nr
where magnetkarten_karte=101
 and magnetkarten.magnetkarten_status_id = magnetkarten_status.magnetkarten_status_id;

+---------------------+-------+---------+-------------------+-------------+
| wann                | karte | version | status_name       | person_name |
+---------------------+-------+---------+-------------------+-------------+
| 1990-01-01 00:00:00 |   101 |       0 | Cronos-verfügbar  | NULL        |
| 1994-12-08 09:54:00 |   101 |       0 | Ausgabe           | Kaiser      |
| 2000-09-08 11:59:00 |   101 |       0 | Verlust           | Kaiser      |
| 2000-09-08 11:59:00 |   101 |      10 | TU-Card-verfügbar | NULL        |
| 2000-09-25 12:59:50 |   101 |      10 | Ausgabe           | Härtel      |
+---------------------+-------+---------+-------------------+-------------+

• beim Left Outer Join wird eine Tabelle (die linke) zur übergeordneten Tabelle erklärt
• auch jene Zeilen, die in der verbundenen Tabelle keine Entsprechung haben, erscheinen in der Ergebnistabelle
• Felder, die in der rechten Tabelle fehlen, werden mit NULL aufgefüllt

Beispiel 2:

SELECT
  nutzerkennzeichen_nkz as nkz,
  person_name as name,
  person_vorname as vorname,
  personentyp_name as personentyp,
  einrichtung_name as einrichtung,
  struktur_nr as struktur
FROM nutzerkennzeichen, person,person_personentyp, person_einrichtung, personentyp, einrichtung
  LEFT OUTER JOIN person_struktur ON person.person_nr=person_struktur.person_nr
  LEFT OUTER JOIN struktur ON person_struktur.struktur_id=struktur.struktur_id
WHERE nutzerkennzeichen_nkz="dgr" and
  person.person_nr=nutzerkennzeichen.person_nr and
  person.person_nr=person_personentyp.person_nr and
  person_personentyp.personentyp_id=personentyp.personentyp_id and
  person.person_nr=person_einrichtung.person_nr and
  person_einrichtung.einrichtung_id=einrichtung.einrichtung_id;

+-----+-----------+---------+-----------------+---------------------------------+----------+
| nkz | name      | vorname | personentyp     | einrichtung                     | struktur |
+-----+-----------+---------+-----------------+---------------------------------+----------+
| dgr | Grunewald | Dietmar | Mitarbeiter TUC | Technische Universität Chemnitz |   134300 |
+-----+-----------+---------+-----------------+---------------------------------+----------+

Dieses Beispiel liefert die Zugehörigkeit einer Person (NKZ) zum Personentyp, zur Einrichtung und zu einer Struktureinheit.

Der LEFT OUTER JOIN muss benutzt werden, damit auch bei Personen, die nicht der TU Chemnitz angehören (und somit keiner Struktureinheit zugeordnet sind), ein Ergebnis angezeigt wird.

SELECT
  nutzerkennzeichen_nkz as nkz,
  person_name as name,
  person_vorname as vorname,
  personentyp_name as personentyp,
  einrichtung_name as einrichtung,
  struktur_nr as struktur
FROM nutzerkennzeichen, person,person_personentyp, person_einrichtung, personentyp, einrichtung
  LEFT OUTER JOIN person_struktur ON person.person_nr=person_struktur.person_nr
  LEFT OUTER JOIN struktur ON person_struktur.struktur_id=struktur.struktur_id
WHERE nutzerkennzeichen_nkz="jaksc" and
  person.person_nr=nutzerkennzeichen.person_nr and
  person.person_nr=person_personentyp.person_nr and
  person_personentyp.personentyp_id=personentyp.personentyp_id and
  person.person_nr=person_einrichtung.person_nr and
  person_einrichtung.einrichtung_id=einrichtung.einrichtung_id;

+-------+--------+---------+-------------------------+-----------------------------------------------------+----------+
| nkz   | name   | vorname | personentyp             | einrichtung                                         | struktur |
+-------+--------+---------+-------------------------+-----------------------------------------------------+----------+
| jaksc | Schulz | Jakob   | Mitarbeiter An-Institut | Institut für Konstruktion und Verbundbauweisen e.V. |     NULL |
+-------+--------+---------+-------------------------+-----------------------------------------------------+----------+

Empty set (0.00 sec)

SELECT
  nutzerkennzeichen_nkz as nkz,
  person_name as name,
  person_vorname as vorname,
  personentyp_name as personentyp,
  einrichtung_name as einrichtung,
  struktur_nr as struktur
FROM nutzerkennzeichen, person,person_personentyp, person_einrichtung, personentyp, einrichtung
  LEFT OUTER JOIN person_struktur ON person.person_nr=person_struktur.person_nr
  LEFT OUTER JOIN struktur ON person_struktur.struktur_id=struktur.struktur_id
WHERE nutzerkennzeichen_nkz="stb" and
  person.person_nr=nutzerkennzeichen.person_nr and
  person.person_nr=person_personentyp.person_nr and
  person_personentyp.personentyp_id=personentyp.personentyp_id and
  person.person_nr=person_einrichtung.person_nr and
  person_einrichtung.einrichtung_id=einrichtung.einrichtung_id;

+-----+-------+---------+-----------------+---------------------------------+----------+
| nkz | name  | vorname | personentyp     | einrichtung                     | struktur |
+-----+-------+---------+-----------------+---------------------------------+----------+
| stb | Brose | Steffen | Mitarbeiter TUC | Technische Universität Chemnitz |   134300 |
| stb | Brose | Steffen | Mitarbeiter TUC | Technische Universität Chemnitz |   270000 |
| stb | Brose | Steffen | Student TUC     | Technische Universität Chemnitz |   134300 |
| stb | Brose | Steffen | Student TUC     | Technische Universität Chemnitz |   270000 |
+-----+-------+---------+-----------------+---------------------------------+----------+

Es erscheint nur eine Zeile, indem an die letzte Anweisung dieser Zusatz angefügt wird:

group by nutzerkennzeichen_nkz

Es ist aber Zufall (!!!) welche Angaben in den Spalten personentyp_name und einrichtung_name angezeigt werden.

Dietmar Grunewald
Oktober 2006