« Startseite

JOINS – alle Varianten, eine Demonstration

Hier werden alle Join-Varianten vortestellt. Führen Sie alle Anweisungen aus und überlegen Sie, was die Besonderheit einer jeden Abfrage ist.

Quelle(n):

Datenbank

o--- Grundlagen
|
v
mydb

Tabellen anlegen

CREATE TABLE T1(
NUM INT PRIMARY KEY     NOT NULL,
NAME            TEXT    NOT NULL);

CREATE TABLE T2(
NUM INT PRIMARY KEY     NOT NULL,
VALUE           TEXT);

Datensätze einfügen

INSERT INTO T1 VALUES(1,'a');
INSERT INTO T1 VALUES(2,'b');
INSERT INTO T1 VALUES(3,'c');
INSERT INTO T1 VALUES(4,'d');
INSERT INTO T1 VALUES(5,'e');
INSERT INTO T1 VALUES(6,'e');
INSERT INTO T2 VALUES(1,NULL);
INSERT INTO T2 VALUES(2,'xxx');
INSERT INTO T2 VALUES(3,'yyy');
INSERT INTO T2 VALUES(4,'zzz');

CROSS JOIN

  • Jedes Tupel aus t1 wird mit jedem Tupel aus t2 verknüpft
  • Synonym: Kreuztabelle
../../../_images/crossjoin.png
# ohne where...
SELECT * FROM t1, t2;
# explizit ...
SELECT * FROM t1 CROSS JOIN t2;

INNER JOIN

  • Es werden alle Tupel selektiert die in t1 und t2 im gleichnamigen Attribut eine Übereinstimmung haben.
../../../_images/inner-natural-join.png
SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;
SELECT * FROM t1 INNER JOIN t2 USING (num);

NATURAL INNER JOIN

  • Spezialfall des Inner Join
  • die passenden Spalten werden automatisch zugeordnet
../../../_images/inner-natural-join.png
SELECT * FROM t1 NATURAL INNER JOIN t2;

LEFT (OUTER) JOIN

  • OUTER kann weggelassen werden
    1. werden alle Werte der erstgenannten Relation ausgewählt
    2. wenn dann noch der gleiche Wert in der zweiten Tabelle gefunden wird, kommen auch alle anderen Attribute in die Ergebnis-Relation
../../../_images/left-outer-join.png
SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;
SELECT * FROM t1 LEFT JOIN t2 USING (num);
SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';
SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx';

RIGHT JOIN

  • OUTER kann weggelassen werden
    1. werden alle Werte der zweiten Relation ausgewählt
    2. wenn dann noch der gleiche Wert in der ersten Tabelle gefunden wird, kommen auch alle anderen Attribute in die Ergebnis-Relation
../../../_images/right-outer-join.png
SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;

FULL JOIN

  • realisiert einen left outer join und einen right outer join
  • für fehlende Werte in der einen oder anderen Relation werden NULL-Values eingesetzt
  • Wird nicht von allen DB-Managementsystemen unterstützt
SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;

NATURAL FULL JOIN SELECT * FROM t1 NATURAL FULL JOIN t2;

SELF JOIN

  • Mit zwei Alias-Definitionen die gleiche Tabelle befragen.
select * from t1 as a, t1 as b where a.name=b.name;

Wozu ist ein Self-Join gut I?

Damit lassen sich hirarchische Strukturen (Eltern/Kind-Beziehungen) in einer Tabelle abgebilden [1].

Beachte die Nachteile:

  • Änderungen sind schwierig zu realisieren
  • verschachtelte Hirarchien lassen sich so nicht einfach abfragen
  • Viele DB-Management-System bieten dafür eine Unterstützung durch recursive CTEs [2]
DROP TABLE kategorie;
CREATE TABLE kategorie (
  id INTEGER,
  bezeichnung VARCHAR(45) NOT NULL,
  parent INTEGER NOT NULL
);

INSERT INTO kategorie (id, bezeichnung, parent) VALUES (1,'Opensource',0);
INSERT INTO kategorie (id, bezeichnung, parent) VALUES (2,'Windows',0);
INSERT INTO kategorie (id, bezeichnung, parent) VALUES (3, 'Linux',1);
INSERT INTO kategorie (id, bezeichnung, parent) VALUES (4,'SUSE',3);
INSERT INTO kategorie (id, bezeichnung, parent) VALUES (5,'Knoppix',3);
INSERT INTO kategorie (id, bezeichnung, parent) VALUES (6,'Win XP',2);
INSERT INTO kategorie (id, bezeichnung, parent) VALUES (7,'Vista',2);
INSERT INTO kategorie (id, bezeichnung, parent) VALUES (8,'Ubuntu',3);
INSERT INTO kategorie (id, bezeichnung, parent) VALUES (9,'Macintosh',0);
INSERT INTO kategorie (id, bezeichnung, parent) VALUES (10,'Windows 10',2);
INSERT INTO kategorie (id, bezeichnung, parent) VALUES (11,'CentOS',3);

select distinct t2.bezeichnung as Betriebssystem, t1.bezeichnung as Kategorie
from kategorie t1 join kategorie t2 on t1.id = t2.parent order by Kategorie;

-- Ausgabe
 betriebssystem | kategorie
----------------+------------
 CentOS         | Linux
 Knoppix        | Linux
 SUSE           | Linux
 Ubuntu         | Linux
 Linux          | Opensource
 Vista          | Windows
 Win XP         | Windows
 Windows 10     | Windows
 (8 rows)