JOIN – Verbund von Tabellen

Full-Outer-Join-3-TabellenUm eine Abfrage über mehrere Tabellen hinweg ausführen zu können, müssen die Tabellen logisch miteinander verbunden werden. Hierin liegt eine der Stärken von relationalen Datenbanken. Als Verknüpfung dienen dabei einzigartige Attribute der jeweiligen Tabelle, die PRIMARY KEYS (PK). Verweist ein PK auf den PK einer anderen Tabelle, so wird der entfernte Schlüssen FOREIGN KEY (FK) genannt.

Es gibt verschiedene JOIN Varianten. Der umfangreichsteund zugleich unbrauchbarste Verbund ist das kartesische Produkt bzw. Cross-Join. Hierbei werden alle Daten der ersten mit allen Daten der zweiten Tabelle verknüpft. Es kann unter Umständen auch etwas länger dauern…

Diese Tabellen dienen als Beispiel für die nun folgenden JOIN Variationen

Tabelle: ma

NR NAME VORNAME ABTNR
1 Naumann Karl 1
2 Baumann Beate 7
3 Hartmann Heike 10
4 Naumann Jens 33
5 Blaumann Jahn

 

Tabelle: abt

Nr Abteilung
1 Einkauf
7 Personal
10 EDV

 

Tabelle: abt_ext

NR ABTEILUNG
10 EDV
33 Kasse
18 Pforte

Cross-Join

Es werden alle Daten der ersten mit allen Daten der zweiten Tabelle verknüpft.

Cross-Join

SELECT *
  FROM ma CROSS JOIN abt;

NR NAME VORNAME ABTNR NR_1 ABTEILUNG
1 Naumann Karl 1 1 Einkauf
2 Baumann Beate 7 1 Einkauf
3 Hartmann Heike 10 1 Einkauf
4 Naumann Jens 33 1 Einkauf
5 Blaumann Jahn 1 Einkauf
1 Naumann Karl 1 7 Personal
2 Baumann Beate 7 7 Personal
3 Hartmann Heike 10 7 Personal
4 Naumann Jens 33 7 Personal
5 Blaumann Jahn 7 Personal
1 Naumann Karl 1 10 EDV
2 Baumann Beate 7 10 EDV
3 Hartmann Heike 10 10 EDV
4 Naumann Jens 33 10 EDV
5 Blaumann Jahn 10 EDV

Theta-Join

Ausgehend vom Cross-Join, kann man noch eine Bedingung an den Select anfügen und das Ergebnis einschränken. Dazu wird eine Spalte der ersten mit einer Spalte der zweiten Tabelle über eine logische Operation verglichen: a.Nr < b.Nr

Equi-Join

SELECT *
  FROM ma a CROSS JOIN abt b
 WHERE a.Nr < b.Nr;

NR NAME VORNAME ABTNR NR_1 ABTEILUNG
5 Blaumann Jahn 10 EDV
4 Naumann Jens 33 10 EDV
3 Hartmann Heike 10 10 EDV
2 Baumann Beate 7 10 EDV
1 Naumann Karl 1 10 EDV
5 Blaumann Jahn 7 Personal
4 Naumann Jens 33 7 Personal
3 Hartmann Heike 10 7 Personal
2 Baumann Beate 7 7 Personal
1 Naumann Karl 1 7 Personal

Equi-Join / Inner-Join

Mach man aus dem „kleiner als…“ ein „gleich“, so ergibt sich ein Inner-Join – der wahrscheinlich meißtgenutzte Join:

Equi-Join

SELECT *
  FROM ma a CROSS JOIN abt b
 WHERE a.Nr = b.Nr;

NR NAME VORNAME ABTNR NR_1 ABTEILUNG
1 Naumann Karl 1 1 Einkauf

 

Natural-Join

Beim Natural-Join werden gleiche Spalten entfernt. Es müssen in beiden Tabellen die selben Spalten vorhanden sein, denn eine WHERE Klausel oder ON Bedingung gibt es nicht. Es gibt den Natural-Join auch als RIGHT- und LEFT-Natural-Join.

Natural-Join

SELECT *
  FROM abt NATURAL JOIN abt_ext;

NR ABTEILUNG
10 EDV

 

Outer-Join

Mit einem Outer-Join wird die zu verbindende Tabelle rechts bzw. links des Keywords JOIN vollständig angeknüpft. Nicht verknüpfbare Felder werden leer gelassen oder auch mit NULL angezeigt.

Right-Outer-Join

Hier wird die Tabelle, die RECHTS vom Keyword RIGHT OUTER JOIN steht, komplett angezeigt und die zweite Tabelle ggf. mit NULL verknüpft.

Right-Outer-Join

  SELECT ma.vorname, ma.name, ab.abteilung
    FROM abt ab RIGHT OUTER JOIN ma ma ON ab.nr = ma.abtnr
ORDER BY ma.name DESC;
VORNAME NAME ABTEILUNG
Karl Naumann Einkauf
Jens Naumann  
Heike Hartmann EDV
Jahn Blaumann  
Beate Baumann Personal

 

Left-Outer-Join

Hier wird die Tabelle, die LINKS vom Keyword LEFT OUTER JOIN steht, komplett angezeigt und die zweite Tabelle ggf. mit NULL verknüpft.

Left-Outer-Join

  SELECT ma.vorname, ma.name, ab.abteilung
    FROM ma ma LEFT OUTER JOIN abt ab ON ma.abtnr = ab.nr
ORDER BY ma.name DESC;

VORNAME NAME ABTEILUNG
Karl Naumann Einkauf
Jens Naumann  
Heike Hartmann EDV
Jahn Blaumann  
Beate Baumann Personal

 

Wie man erkennt, entspricht das Ergebnis dem des Right-Outer-Join.

Full-Outer-Join / Full-Join

Die Kombination eines Right-Outer-Join und eines Left-Outer-Join ergibt einen Full-Outer-Join. Es werden alle Datensätze von beiden Tabellen als Ergebnismenge ausgegeben. Zusammenpassende Sätze werden miteinander verbunden.

Hier ein Beispiel mit 3 Tabellen. Zuerst wird die Gesamtmenge aus MA und ABT gebildet und dann an diese Ergebnismenge die Tabelle ABT_EXT verbunden.

Full-Outer-Join-3-Tabellen

  SELECT ma.vorname, ma.name
       , ab.abteilung
       , abe.abteilung
    FROM ma ma
         FULL OUTER JOIN abt ab ON ma.abtnr = ab.nr
         FULL OUTER JOIN abt_ext abe ON ma.abtnr = abe.nr
ORDER BY ma.name DESC;

 

VORNAME NAME ABTEILUNG ABTEILUNG_1
Pforte
Karl Naumann Einkauf
Jens Naumann Kasse
Heike Hartmann EDV EDV
Jahn Blaumann
Beate Baumann Personal

 

Semi-Join

Ein Natural-Join mit anschließender Projektion auf die Spalten der ersten Tabelle.

 

Self-Join

Der Self Join verbindet eine Tabelle mit sich selbst und ist technisch gesehen einer von den zuvor genannten Join-Varianten.

Als Beispiel wird die Tabelle MA um eine Spalte erweitert:

NR NAME VORNAME ABTNR CHEF
1 Naumann Karl 1
2 Baumann Beate 7 1
3 Hartmann Heike 10 2
4 Naumann Jens 33 2
5 Blaumann Jahn 3

 

  SELECT *
    FROM ma a LEFT JOIN ma b ON a.chef = b.nr
ORDER BY a.nr;
NR NAME VORNAME ABTNR CHEF NR_1 NAME_1 VORNAME_1 ABTNR_1 CHEF_1
1 Naumann Karl 1
2 Baumann Beate 7 1 1 Naumann Karl 1
3 Hartmann Heike 10 2 2 Baumann Beate 7 1
4 Naumann Jens 33 2 2 Baumann Beate 7 1
5 Blaumann Jahn 3 3 Hartmann Heike 10 2

 

Etwas schöner kann man das natürlich auch darstellen:

  SELECT a.nr, a.name, a.vorname, a.abtnr
       , b.name AS "CHEF"
    FROM ma a LEFT JOIN ma b ON a.chef = b.nr
ORDER BY a.nr;
NR NAME VORNAME ABTNR CHEF
1 Naumann Karl 1
2 Baumann Beate 7 Naumann
3 Hartmann Heike 10 Baumann
4 Naumann Jens 33 Baumann
5 Blaumann Jahn Hartmann

Schreibe einen Kommentar