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');
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;
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);
NATURAL INNER JOIN¶
Spezialfall des Inner Join
die passenden Spalten werden automatisch zugeordnet

SELECT * FROM t1 NATURAL INNER JOIN t2;
LEFT (OUTER) JOIN¶
OUTER kann weggelassen werden
werden alle Werte der erstgenannten Relation ausgewählt
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';
RIGHT JOIN¶
OUTER kann weggelassen werden
werden alle Werte der zweiten Relation ausgewählt
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;
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;
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)