JOINS – alle Varianten, Self-Join Demo I¶

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

Quelle(n):

https://www.postgresql.org/docs/9.6/static/queries-table-expressions.html
http://wiki.selfhtml.org/wiki/Datenbank/Einführung_in_Joins#Joins_allgemein

Datenbank¶

Übungsdatenbank anlegen und/oder nutzen.

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');

Kurzfilm

CROSS JOIN¶

Jedes Tupel aus t1 wird mit jedem Tupel aus t2 verknüpft
Synonym: Kreuztabelle

# ohne where...
SELECT * FROM t1, t2;
# explizit ...
SELECT * FROM t1 CROSS JOIN t2;

Kurzfilm

INNER JOIN¶

Es werden alle Tupel selektiert die in t1 und t2 im gleichnamigen Attribut eine Übereinstimmung haben.

SELECT * FROM t1, t2 where t1.num = t2.num;
SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;
SELECT * FROM t1 INNER JOIN t2 USING (num);

Kurzfilm

NATURAL INNER JOIN¶

Spezialfall des Inner Join
die passenden Spalten werden automatisch zugeordnet

SELECT * FROM t1 NATURAL INNER JOIN t2;

Kurzfilm

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

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';

Kurzfilm

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

SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;

Kurzfilm

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;

SELECT * FROM t1 NATURAL FULL JOIN t2;

Kurzfilm

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 hierarchische Strukturen (Eltern/Kind-Beziehungen) in einer Tabelle abbilden 1.

Beachte die Nachteile:

Änderungen sind schwierig zu realisieren
verschachtelte Hierarchien 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)

Wozu ist ein Self-Join gut II?¶

« Numerierung für die Ergebnis-Liste

1: http://www.webmasterpro.de/coding/article/mysql-hirarchische-tabellen.html
2: https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL

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)