Äußere Verbindungen in SQL 

Beispiele und Erläuterungen

Bei einer äußeren Verbindung  wird das Ergebnis einer Verbindung um zusätzliche Zeilen  erweitert.

Die Bezeichnung „äußeren Verbindung“ könnte daher stammen, daß jene zusätzlichen Zeilen außerhalb  des Ergebnisses eines normalen Joins liegen.

Protokoll (Beginn)

WARNINGS; SET sql_mode = 'ANSI,TRADITIONAL';
DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE L ( L VARCHAR( 255 ));
INSERT INTO L ( L ) VALUES ( 'I' );
INSERT INTO L ( L ) VALUES ( 'L' );
CREATE TABLE R ( R VARCHAR( 255 ));
INSERT INTO R ( R ) VALUES ( 'I' );
INSERT INTO R ( R ) VALUES ( 'R' );

Die linke Verbindung

Protokoll (Fortsetzung)

SELECT * FROM L INNER JOIN R ON L = R;
+------+------+
| L    | R    |
+------+------+
| I    | I    |
+------+------+

In der inneren Verbindung  sehen wir die Zeilen mit gleichen Werten in den beiden gleichgesetzten Spalten.

Protokoll (Fortsetzung)

SELECT * FROM L LEFT JOIN R ON L = R;
+------+------+
| L    | R    |
+------+------+
| I    | I    |
| L    | NULL |
+------+------+

Die linke Verbindung  wurde um alle Zeilen der linken Seite  des Produkts (der Tabelle »L«) aus den angegebenen Spalten erweitert, die noch nicht mindestens einmal im inneren Produkt vorkommen. Eine solche linke Verbindung wird auch als „linke äußere  Verbindung“ bezeichnet.

Die linke Verbindung unter Ausschluß der inneren Verbindung

Durch eine Selektion  können wir gezielt anzeigen lassen, welche Zeilen der linken Tabelle nicht  im inneren Produkt vorkommen („welcher Mitarbeiter hat noch nie eine Schulung besucht?“).

Protokoll (Fortsetzung)

SELECT L FROM L LEFT JOIN R ON L = R WHERE R IS NULL;
+------+
| L    |
+------+
| L    |
+------+

Die rechte Verbindung

Die rechte Verbindung  wurde um alle Zeilen der rechten Seite  des Produkts (der Tabelle »R«) aus den angegebenen Spalten erweitert, die noch nicht mindestens einmal im inneren Produkt vorkommen. Eine solche rechte Verbindung wird auch als „rechte äußere  Verbindung“ bezeichnet.

Protokoll (Fortsetzung)

SELECT * FROM L RIGHT JOIN R ON L = R;
+------+------+
| L    | R    |
+------+------+
| I    | I    |
| NULL | R    |
+------+------+

Die rechte Verbindung unter Ausschluß der inneren Verbindung

Durch eine Selektion  können wir gezielt anzeigen lassen, welche Zeilen der rechten Tabelle nicht  im inneren Produkt vorkommen („welche Schulung wurde noch nie von jemandem besucht?“).

Protokoll (Fortsetzung)

SELECT R FROM L RIGHT JOIN R ON L = R WHERE L IS NULL;
+------+
| R    |
+------+
| R    |
+------+

Die äußere Verbindung

Wir sehen linke und rechte Verbindungen als äußere Verbindungen an.

Unter „der äußeren Verbindung “ versteht man aber normalerweise die Vereinigung  von linker und rechter Verbindung.

Da es in MySQL keine spezielle Unterstützung für die Ermittlung der äußeren Verbindungen gibt, muß man sie dort durch eine Vereinigung einer linken mit einer rechten Verbindung nachbilden.

Protokoll (Fortsetzung)

SELECT * FROM L LEFT  JOIN R ON L = R 
UNION
SELECT * FROM L RIGHT JOIN R ON L = R;
+------+------+
| L    | R    |
+------+------+
| I    | I    |
| L    | NULL |
| NULL | R    |
+------+------+

Anforderungen

Bei einer äußeren Verbindung muß eine ON-Klausel angegeben werden.

Anwendungsbeispiele

Integrierte Darstellung von Erweiterungsdatensätzen

Im folgenden Beispiel gibt es eine Tabelle von Personen. Für diejenigen Personen, welche Angestellte sind, gibt es eine Erweiterungstabelle mit der Abteilungs- und Rufnummer.

Die linke Verbindung gestattet es uns, alle Personen zusammen mit etwaigen Erweiterungsdatensätzen anzuzeigen (siehe letzte abgeleitete Tabelle).

main.sql

WARNINGS; SET sql_mode = 'ANSI,TRADITIONAL';
DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE PERSON ( PERSON VARCHAR( 255 ), VORNAME VARCHAR( 255 ), NACHNAME VARCHAR( 255 )); 
INSERT INTO PERSON ( PERSON, VORNAME, NACHNAME )VALUES ( '0', 'Laura', 'Pfeiffer' );
INSERT INTO PERSON ( PERSON, VORNAME, NACHNAME )VALUES ( '1', 'Liam', 'Wolff' );
INSERT INTO PERSON ( PERSON, VORNAME, NACHNAME )VALUES ( '2', 'Lina', 'Arnold' );
INSERT INTO PERSON ( PERSON, VORNAME, NACHNAME )VALUES ( '3', 'Elias', 'Sauer' );
INSERT INTO PERSON ( PERSON, VORNAME, NACHNAME )VALUES ( '4', 'Levi', 'Vogr' );
INSERT INTO PERSON ( PERSON, VORNAME, NACHNAME )VALUES ( '5', 'Julia', 'Thomas' );
CREATE TABLE ANGESTELLTER ( PERSON VARCHAR( 255 ), ABTEILUNG VARCHAR( 255 ), RUFNUMMER VARCHAR( 255 )); 
INSERT INTO ANGESTELLTER ( PERSON, ABTEILUNG, RUFNUMMER ) VALUES ( '0', 'I', '5631' );
INSERT INTO ANGESTELLTER ( PERSON, ABTEILUNG, RUFNUMMER ) VALUES ( '2', 'I', '4312' );
INSERT INTO ANGESTELLTER ( PERSON, ABTEILUNG, RUFNUMMER ) VALUES ( '3', 'II', '8491' );
INSERT INTO ANGESTELLTER ( PERSON, ABTEILUNG, RUFNUMMER ) VALUES ( '5', 'IV', '6112' );
SELECT PERSON, VORNAME, NACHNAME FROM PERSON;
SELECT PERSON, ABTEILUNG, RUFNUMMER FROM ANGESTELLTER;
SELECT PERSON.PERSON AS PERSON, VORNAME, NACHNAME, ABTEILUNG, RUFNUMMER FROM PERSON INNER JOIN ANGESTELLTER ON PERSON.PERSON = ANGESTELLTER.PERSON;
SELECT PERSON.PERSON AS PERSON, VORNAME, NACHNAME, ABTEILUNG, RUFNUMMER FROM PERSON LEFT JOIN ANGESTELLTER ON PERSON.PERSON = ANGESTELLTER.PERSON;

Protokoll

+--------+---------+----------+
| PERSON | VORNAME | NACHNAME |
+--------+---------+----------+
| 0      | Laura   | Pfeiffer |
| 1      | Liam    | Wolff    |
| 2      | Lina    | Arnold   |
| 3      | Elias   | Sauer    |
| 4      | Levi    | Vogr     |
| 5      | Julia   | Thomas   |
+--------+---------+----------+
+--------+-----------+-----------+
| PERSON | ABTEILUNG | RUFNUMMER |
+--------+-----------+-----------+
| 0      | I         | 5631      |
| 2      | I         | 4312      |
| 3      | II        | 8491      |
| 5      | IV        | 6112      |
+--------+-----------+-----------+
+--------+---------+----------+-----------+-----------+
| PERSON | VORNAME | NACHNAME | ABTEILUNG | RUFNUMMER |
+--------+---------+----------+-----------+-----------+
| 0      | Laura   | Pfeiffer | I         | 5631      |
| 2      | Lina    | Arnold   | I         | 4312      |
| 3      | Elias   | Sauer    | II        | 8491      |
| 5      | Julia   | Thomas   | IV        | 6112      |
+--------+---------+----------+-----------+-----------+
+--------+---------+----------+-----------+-----------+
| PERSON | VORNAME | NACHNAME | ABTEILUNG | RUFNUMMER |
+--------+---------+----------+-----------+-----------+
| 0      | Laura   | Pfeiffer | I         | 5631      |
| 2      | Lina    | Arnold   | I         | 4312      |
| 3      | Elias   | Sauer    | II        | 8491      |
| 5      | Julia   | Thomas   | IV        | 6112      |
| 1      | Liam    | Wolff    | NULL      | NULL      |
| 4      | Levi    | Vogr     | NULL      | NULL      |
+--------+---------+----------+-----------+-----------+

Artikel ohne Stammdatensatz

Solche äußeren Verbindungen werden oft verwendet, um das Fehlen oder Nichtvorkommen eines Eintrags zu entdecken.

Das folgende Beispiel zeigt die Ermittlung neuer Datensätze, zu deren Artikelnummern es noch keinen Stammdatensatz gibt.

main.sql

WARNINGS; SET sql_mode = 'ANSI,TRADITIONAL';
DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE STAMMDATEN ( ARTIKEL VARCHAR( 255 ), NAME VARCHAR( 255 ), INFO VARCHAR( 255 )); 
INSERT INTO STAMMDATEN ( ARTIKEL, NAME, INFO )VALUES ( '1', 'A', 'Haendische Info A' );
INSERT INTO STAMMDATEN ( ARTIKEL, NAME, INFO )VALUES ( '2', 'B', 'Haendische Info B' );
INSERT INTO STAMMDATEN ( ARTIKEL, NAME, INFO )VALUES ( '3', 'C', 'Haendische Info C' );
CREATE TABLE NEUDATEN ( ARTIKEL VARCHAR( 255 ), NAME VARCHAR( 255 )); 
INSERT INTO NEUDATEN ( ARTIKEL, NAME ) VALUES ( '1', 'A' );
INSERT INTO NEUDATEN ( ARTIKEL, NAME ) VALUES ( '4', 'D' );
SELECT NEUDATEN.ARTIKEL FROM NEUDATEN LEFT JOIN STAMMDATEN 
ON NEUDATEN.ARTIKEL = STAMMDATEN.ARTIKEL WHERE STAMMDATEN.ARTIKEL IS NULL;

Protokoll

+---------+
| ARTIKEL |
+---------+
|       4 |
+---------+

Übungsaufgaben

/   Tabellen anlegen und abfragen

Lassen Sie diejenigen Zeilen aus dem äußeren Produkt von »L« und »R« anzeigen, die mindestens in einer Spalte des äußeren Produkts »NULL« enthalten (also die äußere Verbindung unter Ausschluß der inneren Verbindung).

Skript

WARNINGS; SET sql_mode = 'ANSI,TRADITIONAL';
DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE L ( L VARCHAR( 255 ));
INSERT INTO L ( L ) VALUES ( 'I' );
INSERT INTO L ( L ) VALUES ( 'L' );
CREATE TABLE R ( R VARCHAR( 255 ));
INSERT INTO R ( R ) VALUES ( 'I' );
INSERT INTO R ( R ) VALUES ( 'R' );