ON-Verbindungen in SQL  (MySQL )

Vorbereitung

Zum Nachvollziehen der folgenden Beispiele kann das folgende Skript in einen MySQL-Klienten kopiert werden.

Skript (MySQL )

DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE ZAHL ( ZIFFERN VARCHAR ( 255 ), NAME VARCHAR ( 255 ));
INSERT INTO ZAHL ( ZIFFERN, NAME ) VALUES ( '0', 'Null' );
INSERT INTO ZAHL ( ZIFFERN, NAME ) VALUES ( '1', 'Eins' );
CREATE TABLE NUMBER ( DIGITS VARCHAR ( 255 ), NAME VARCHAR( 255 ));
INSERT INTO NUMBER ( DIGITS, NAME ) VALUES ( '0', 'zero' );
INSERT INTO NUMBER ( DIGITS, Name ) VALUES ( '1', 'one'  );

INNER-JOIN-Verbindungen

Wir hatten die Vorgehensweise zum Erstellen von Äquijoins wie im folgenden Beispiel schon kennengelernt.

Konsole

SELECT * FROM ZAHL, NUMBER WHERE ZIFFERN = DIGITS;

+---------+------+--------+------+
| ZIFFERN | NAME | DIGITS | NAME |
+---------+------+--------+------+
| 0       | Null | 0      | zero |
| 1       | Eins | 1      | one  |
+---------+------+--------+------+

An Stelle eines Kommas können auch die beiden Wörter »INNER« und »JOIN« geschrieben werden.

Konsole

SELECT * FROM ZAHL INNER JOIN NUMBER WHERE ZIFFERN = DIGITS;

+---------+------+--------+------+
| ZIFFERN | NAME | DIGITS | NAME |
+---------+------+--------+------+
| 0       | Null | 0      | zero |
| 1       | Eins | 1      | one  |
+---------+------+--------+------+

Auch ein einfaches kartesisches Produkt ohne WHERE-Klausel kann so geschrieben werden.

Konsole

SELECT * FROM ZAHL INNER JOIN NUMBER;

+---------+------+--------+------+
| ZIFFERN | NAME | DIGITS | NAME |
+---------+------+--------+------+
| 0       | Null | 0      | zero |
| 1       | Eins | 0      | zero |
| 0       | Null | 1      | one  |
| 1       | Eins | 1      | one  |
+---------+------+--------+------+

Ein normales kartesisches Produkt zweier Tabellen (Join) wird im Englischen auch als “inner join ” bezeichnet.

Die beiden Wörter »INNER« und »JOIN« sollen betonen, daß eine „innere Verbindung“ erstellt wird. Aber auch die Schreibweise mit »WHERE« ist eine innere Verbindung.

Einige ältere Datenbanken können die Schreibweise mit »INNER« und »JOIN« aber nicht verarbeiten, so daß die Schreibweise mit »WHERE« portabler ist.

ON-Bedingung

Wenn die beiden Wörter »INNER« und »JOIN« verwendet werden, kann die Bedingung für die Restriktion auch mit »ON« statt mit »WHERE« geschrieben werden.

Konsole

SELECT * FROM ZAHL INNER JOIN NUMBER ON ZIFFERN = DIGITS;

+---------+------+--------+------+
| ZIFFERN | NAME | DIGITS | NAME |
+---------+------+--------+------+
| 0       | Null | 0      | zero |
| 1       | Eins | 1      | one  |
+---------+------+--------+------+

Technisch gibt es keinen Unterschied dazwischen, ob eine Bedingung bei einer inneren Verbindung mit einer ON-Klausel oder mit einer WHERE-Klausel angegeben wird.

Bei Verwendung des Wortes »ON« ist es aber auch möglich, eine zusätzliche Bedingung  mit WHERE anzugeben. Es werden dann nur die Zeilen angezeigt, die beide  Bedingungen erfüllen.

Konsole

SELECT * FROM ZAHL INNER JOIN NUMBER ON ZIFFERN = DIGITS WHERE ZIFFERN = '0';

+---------+------+--------+------+
| ZIFFERN | NAME | DIGITS | NAME |
+---------+------+--------+------+
| 0       | Null | 0      | zero |
+---------+------+--------+------+

Aussprachehinweis

on ɑn

Beispiel zur ON-Schreibweise

Vorbereitung

DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE LAND ( LAND VARCHAR ( 255 ));
INSERT INTO LAND ( LAND ) VALUES ( 'Bayern'  );
INSERT INTO LAND ( LAND ) VALUES ( 'Berlin'  );
INSERT INTO LAND ( LAND ) VALUES ( 'Hamburg' );
INSERT INTO LAND ( LAND ) VALUES ( 'Sachsen' );
CREATE TABLE STADT ( STADT VARCHAR ( 255 ));
INSERT INTO STADT ( STADT ) VALUES ( 'Berlin'  );
INSERT INTO STADT ( STADT ) VALUES ( 'Dresden' );
INSERT INTO STADT ( STADT ) VALUES ( 'Hamburg' );
INSERT INTO STADT ( STADT ) VALUES ( 'Erfurt'  );
SELECT * FROM LAND;
SELECT * FROM STADT;

Kombination mit Äquiselektion zur Ermittlung einer Schnittmenge zweier Tabellen

Die Tabelle »LAND« enthält die Namen von vier Ländern.

Konsole (MySQL, E92, C99, C03)

SELECT * FROM LAND;

+---------+
| LAND    |
+---------+
| Bayern  |
| Berlin  |
| Hamburg |
| Sachsen |
+---------+

Die Tabelle »STADT« enthält die Namen von vier Städten.

Konsole (MySQL, E92, C99, C03)

SELECT * FROM STADT;

+---------+
| STADT   |
+---------+
| Berlin  |
| Dresden |
| Hamburg |
| Erfurt  |
+---------+

Gesucht seien die Namen, welche in beiden jener Tabellen vorkommen.

Um jene Namen zu finden, können wir zunächst alle möglichen Kombinationen  aus einem Namen aus der einen Tabelle mit einem Namen aus der anderen Tabelle bilden.

Konsole

SELECT * FROM LAND INNER JOIN STADT;

+---------+---------+
| LAND    | STADT   |
+---------+---------+
| Bayern  | Berlin  |
| Berlin  | Berlin  |
| Hamburg | Berlin  |
| Sachsen | Berlin  |
| Bayern  | Dresden |
| Berlin  | Dresden |
| Hamburg | Dresden |
| Sachsen | Dresden |
| Bayern  | Hamburg |
| Berlin  | Hamburg |
| Hamburg | Hamburg |
| Sachsen | Hamburg |
| Bayern  | Erfurt  |
| Berlin  | Erfurt  |
| Hamburg | Erfurt  |
| Sachsen | Erfurt  |
+---------+---------+

Wenn es Namen gibt, die in beiden Tabellen vorkommen, dann müßte es in der Tabelle mit allen möglichen Kombinationen Zeilen geben, in denen derselbe Name  sowohl in der Spalte »LAND« als auch in der Spalte »STADT« vorkommt. Solche Zeilen können wir nun aber mit einer Äquiselektion finden.

Konsole

SELECT * FROM LAND INNER JOIN STADT ON LAND = STADT;

+---------+---------+
| LAND    | STADT   |
+---------+---------+
| Berlin  | Berlin  |
| Hamburg | Hamburg |
+---------+---------+

Da es nun ausreicht, wenn jeder doppelte Name einmal genannt wird, reicht es nur eine der beiden Spalten anzuzeigen. So zeigt uns also die folgende Abfrage diejenigen Namen, welche in beiden Tabellen vorkommen.

Konsole

SELECT STADT FROM LAND INNER JOIN STADT ON LAND = STADT;

+---------+
| STADT   |
+---------+
| Berlin  |
| Hamburg |
+---------+

Beispiel zur ON-Schreibweise (1)

Skript (MySQL )

DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE VORNAME ( PERSON VARCHAR ( 255 ), VORNAME VARCHAR ( 255 ));
INSERT INTO  VORNAME ( PERSON, VORNAME ) VALUES ( 1, 'Hans' );
INSERT INTO  VORNAME ( PERSON, VORNAME ) VALUES ( 2, 'Lotte' );
CREATE TABLE NACHNAME ( PERSON1 VARCHAR ( 255 ), NACHNAME VARCHAR ( 255 ));
INSERT INTO  NACHNAME ( PERSON1, NACHNAME ) VALUES ( 1, 'Fischer' );
INSERT INTO  NACHNAME ( PERSON1, NACHNAME ) VALUES ( 2, 'Mayer' );

Die beiden Personennamen »Hans Fischer« und »Lotte Mayer« wurden auf zwei Tabellen aufgeteilt. Eine Tabelle für die Vornamen und eine Tabelle für die Nachnamen.

Anders als bei einem ähnlichen Beispiel in einer früheren Lektion wurde aber diesmal zu jedem Vornamen und jedem Nachnamen auch noch die Person abgespeichert, zu welcher jener Vorname beziehungsweise Nachname gehört.

Konsole

SELECT * FROM VORNAME;

+--------+---------+
| PERSON | VORNAME |
+--------+---------+
| 1      | Hans    |
| 2      | Lotte   |
+--------+---------+

Konsole

SELECT * FROM NACHNAME;

+---------+----------+
| PERSON1 | NACHNAME |
+---------+----------+
| 1       | Fischer  |
| 2       | Mayer    |
+---------+----------+

Erstellt man das kartesische Produkt der beiden Tabellen, so findet man zunächst noch alle möglichen Zuordnungen zwischen Vornamen und Nachnamen.

Konsole

SELECT * FROM VORNAME INNER JOIN NACHNAME;

+--------+---------+---------+----------+
| PERSON | VORNAME | PERSON1 | NACHNAME |
+--------+---------+---------+----------+
| 1      | Hans    | 1       | Fischer  |
| 2      | Lotte   | 1       | Fischer  |
| 1      | Hans    | 2       | Mayer    |
| 2      | Lotte   | 2       | Mayer    |
+--------+---------+---------+----------+

Auf das kartesische Produkt kann nun aber eine Äquiselektion angewendet werden, welche nur die Kombinationen von Vornamen und Nachnamen ergibt, welche zur selben Person gehören.

Konsole

SELECT * FROM VORNAME INNER JOIN NACHNAME ON PERSON = PERSON1;

+--------+---------+---------+----------+
| PERSON | VORNAME | PERSON1 | NACHNAME |
+--------+---------+---------+----------+
| 1      | Hans    | 1       | Fischer  |
| 2      | Lotte   | 2       | Mayer    |
+--------+---------+---------+----------+

Übungsaufgaben

/   Umformulieren

Formulieren Sie die drei Abfragen am Ende des folgenden Skriptes so um, daß sie kein Komma »,« und kein »WHERE« mehr enthalten!

Skript (MySQL )

DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE L ( WERT VARCHAR ( 255 ));
INSERT INTO L ( WERT ) VALUES ( '1' );
INSERT INTO L ( WERT ) VALUES ( '2' );
CREATE TABLE R ( WERT VARCHAR ( 255 ));
INSERT INTO R ( WERT ) VALUES ( '1' );
INSERT INTO R ( WERT ) VALUES ( '3' );
SELECT * FROM L;
SELECT * FROM R;
SELECT * FROM L, R;
SELECT * FROM L, R WHERE L.WERT = R.WERT;
SELECT L.WERT FROM L, R WHERE L.WERT = R.WERT;

/   Verbindung zweier Tabellen erstellen

Das folgende Skript legt zwei Tabellen an.

Konsole (MySQL )

DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE STADT 
( STADT VARCHAR ( 255 ), BEZEICHNUNG VARCHAR ( 255 ), LAND VARCHAR ( 255 ));
INSERT INTO STADT ( STADT, BEZEICHNUNG, LAND ) VALUES ( '1', 'Paris',     '1' );
INSERT INTO STADT ( STADT, BEZEICHNUNG, LAND ) VALUES ( '2', 'Marseille', '1' );
INSERT INTO STADT ( STADT, BEZEICHNUNG, LAND ) VALUES ( '3', 'Lyon',      '1' );
INSERT INTO STADT ( STADT, BEZEICHNUNG, LAND ) VALUES ( '4', 'Rom',       '2' );
INSERT INTO STADT ( STADT, BEZEICHNUNG, LAND ) VALUES ( '5', 'Mailand',   '2' );
INSERT INTO STADT ( STADT, BEZEICHNUNG, LAND ) VALUES ( '6', 'Neapel',    '2' );
INSERT INTO STADT ( STADT, BEZEICHNUNG, LAND ) VALUES ( '7', 'Tokio',     '3' );
INSERT INTO STADT ( STADT, BEZEICHNUNG, LAND ) VALUES ( '8', 'Yokohama',  '3' );
INSERT INTO STADT ( STADT, BEZEICHNUNG, LAND ) VALUES ( '9', 'Osaka',     '3' );
CREATE TABLE LAND ( LAND VARCHAR ( 255 ), BEZEICHNUNG VARCHAR ( 255 ));
INSERT INTO  LAND ( LAND, BEZEICHNUNG ) VALUES ( '1', 'Frankreich' );
INSERT INTO  LAND ( LAND, BEZEICHNUNG ) VALUES ( '2', 'Italien'    );
INSERT INTO  LAND ( LAND, BEZEICHNUNG ) VALUES ( '3', 'Japan'      );

Aussprachehinweis

Lyon /lijɔ̃/

Konsole (MySQL )

SELECT * FROM STADT;

+-------+-------------+------+
| STADT | BEZEICHNUNG | LAND |
+-------+-------------+------+
| 1     | Paris       | 1    |
| 2     | Marseille   | 1    |
| 3     | Lyon        | 1    |
| 4     | Rom         | 2    |
| 5     | Mailand     | 2    |
| 6     | Neapel      | 2    |
| 7     | Tokio       | 3    |
| 8     | Yokohama    | 3    |
| 9     | Osaka       | 3    |
+-------+-------------+------+

Konsole (MySQL )

SELECT * FROM LAND;

+------+-------------+
| LAND | BEZEICHNUNG |
+------+-------------+
| 1    | Frankreich  |
| 2    | Italien     |
| 3    | Japan       |
+------+-------------+

Erstellen Sie eine Äquijoin-Abfrage mit »ON«, welche aus den beiden Tabellen »STADT« und »LAND« die folgende abgeleitete Tabelle erzeugt, in welcher die Spalte »LAND« nur einmal erscheint.

Ausgabe

+-------+-------------+------+-------------+
| STADT | BEZEICHNUNG | LAND | BEZEICHNUNG |
+-------+-------------+------+-------------+
| 1     | Paris       | 1    | Frankreich  |
| 2     | Marseille   | 1    | Frankreich  |
| 3     | Lyon        | 1    | Frankreich  |
| 4     | Rom         | 2    | Italien     |
| 5     | Mailand     | 2    | Italien     |
| 6     | Neapel      | 2    | Italien     |
| 7     | Tokio       | 3    | Japan       |
| 8     | Yokohama    | 3    | Japan       |
| 9     | Osaka       | 3    | Japan       |
+-------+-------------+------+-------------+