Kartesische Produkte in SQL  (MySQL )

Mögliche Kombinationen von Farbe und Geschmack

Vorbereitung

Skript (MySQL )

DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE S.FARBE ( FARBNAME VARCHAR ( 255 ));
INSERT INTO  S.FARBE ( FARBNAME ) VALUES ( 'Rot' );
INSERT INTO  S.FARBE ( FARBNAME ) VALUES ( 'Gelb' );
CREATE TABLE S.GESCHMACK ( RICHTUNG VARCHAR ( 255 ));
INSERT INTO  S.GESCHMACK ( RICHTUNG ) VALUES ( 'Kirsch' );
INSERT INTO  S.GESCHMACK ( RICHTUNG ) VALUES ( 'Zimt' );
SELECT * FROM S.FARBE; 
SELECT * FROM S.GESCHMACK;

Mögliche Kombinationen von Farbe und Geschmack

Die Tabelle »FARBE« enthält mögliche Farben für Bonbons (deutsche Aussprache von „Bonbon“: /bɔŋˈbɔŋ/, /ŋ/ wie in „Ding“ /ˈdɪŋ/.).

Konsole (MySQL, E92, C99, C03)

SELECT * FROM FARBE;

+----------+
| FARBNAME |
+----------+
| Rot      |
| Gelb     |
+----------+

Die Tabelle »GESCHMACK« enthält mögliche Geschmäcke für Bonbons.

Konsole (MySQL, E92, C99, C03)

SELECT * FROM GESCHMACK;

+----------+
| RICHTUNG |
+----------+
| Kirsch   |
| Zimt     |
+----------+

Gesucht sind nun alle möglichen Kombinationen  einer Farbe mit einem Geschmack.

Diese gesuchten Kombination erhält man, indem man beiden Tabellen gemeinsam durch ein Komma getrennt hinter »FROM« angibt.

Konsole (MySQL, E92, C99, C03)

SELECT * FROM FARBE, GESCHMACK;

+----------+----------+
| FARBNAME | RICHTUNG |
+----------+----------+
| Rot      | Kirsch   |
| Gelb     | Kirsch   |
| Rot      | Zimt     |
| Gelb     | Zimt     |
+----------+----------+

Das kartesische Produkt zweier Mengen

Eine Tabelle mit einer Spalte ist praktisch eine Menge, nämlich die Menge der Werte in jener Spalte.

Die Menge aller möglichen Kombinationen mit dem ersten Teil aus einer Menge A und dem zweiten Teil aus einer Menge B nennt man auch das kartesische Produkt  der beiden Mengen.

Das kartesische Produkt zweier Tabellen

Da eine Tabelle eine Menge ist, kann man die mengentheoretischen Begriffe ohne weiteres auf Tabellen übertragen und vom kartesischen Produkt zweier Tabellen  sprechen, dies wird im Englischen auch als “cross join ” bezeichnet.

In SQL  wird ein kartesisches Produkt zweier Tabellen erstellt (als Ausgangstabelle einer Abfrage verwendet), indem die Namen dieser beiden Tabellen mit einem Komma getrennt hinter »FROM« geschrieben werden.

Anlegen einer Ansicht mit dem Produkt

Das folgenden Beispiel zeigt noch das Anlegen eines Views mit dem kartesischen Produkt.

Konsole (MySQL, E92, C99, C03)

CREATE VIEW KOMBINATION AS
SELECT * FROM FARBE, GESCHMACK;
SELECT * FROM KOMBINATION;

+----------+----------+
| FARBNAME | RICHTUNG |
+----------+----------+
| Rot      | Kirsch   |
| Gelb     | Kirsch   |
| Rot      | Zimt     |
| Gelb     | Zimt     |
+----------+----------+

Solch ein View ist eine neue Tabelle, die eine Verbindung  zweier schon vorhandener Basistabellen darstellt.

Nebeneinandersetzen zweier Tabellen

Vorbereitung

Skript (MySQL )

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

Nebeneinandersetzen zweier Tabellen

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.

Konsole

SELECT * FROM VORNAME;

+---------+
| VORNAME |
+---------+
| Hans    |
| Lotte   |
+---------+

Konsole

SELECT * FROM NACHNAME;

+----------+
| NACHNAME |
+----------+
| Fischer  |
| Mayer    |
+----------+

Wie kann man nun aus den beiden Tabellen eine Tabelle mit den beiden Namen gewinnen?

Dazu sollten die beiden Tabellen „nebeneinandergestellt“ werden, so daß sich in etwa das Folgende ergibt.

Tabelle

+---------+----------+
| VORNAME | NACHNAME |
+---------+----------+
| Hans    | Fischer  |
| Lotte   | Mayer    |
+---------+----------+

Dies ist aber im relationalen Modell nicht möglich, weil die Zeilen dort ja keine bestimmte Position oder Reihenfolge haben! (Auch, wenn es bei einer Ausgabe  einer Tabelle eine Reihenfolge gibt.)

Genausogut wäre also die folgende Kombination möglich.

Tabelle

+---------+----------+
| VORNAME | NACHNAME |
+---------+----------+
| Hans    | Mayer    |
| Lotte   | Fischer  |
+---------+----------+

Die Datenbank kann nicht wissen, welcher Nachname zu welchem Vornamen gehören soll!

Verlangt man also von der Datenbank, die beiden Tabellen nebeneinanderzusetzen, so besteht die einzig neutrale („kanonische“) Möglichkeit, welche keine mögliche Kombination willkürlich wegläßt, darin, jede  Zeile der einen Tabelle mit jeder  Zeile der anderen Tabelle zu kombinieren. So kommt man wieder zum kartesischen Produkt.

Konsole

SELECT * FROM VORNAME, NACHNAME;
+---------+----------+
| VORNAME | NACHNAME |
+---------+----------+
| Hans    | Fischer  |
| Lotte   | Fischer  |
| Hans    | Mayer    |
| Lotte   | Mayer    |
+---------+----------+

Würde die Datenbank daraus die folgenden beiden Zeilen auswählen, so hätte sie willkürlich neue Informationen erfunden  – man drückt dies auch dadurch aus, daß man sagt, daß sie ein „Vorurteil“ habe oder nennt das Ergebnis „nicht-kanonisch“.

Tabelle

+---------+----------+
| VORNAME | NACHNAME |
+---------+----------+
| Hans    | Fischer  |
| Lotte   | Mayer    |
+---------+----------+

Kartesisches Produkt von Tabellen mit mehreren Spalten

Bei der Bildung des kartesischen Produktes von Zeilen mit mehreren Spalten, werden alle Zeilen der einen Tabelle mit allen Zeilen der anderen kombiniert.

Skript (MySQL )

DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE PERSON ( VORNAME VARCHAR ( 255 ), NACHNAME VARCHAR ( 255 ));
INSERT INTO  PERSON ( VORNAME, NACHNAME ) VALUES ( 'Rainer', 'Ziegler' );
INSERT INTO  PERSON ( VORNAME, NACHNAME ) VALUES ( 'Liese',  'Graf'    );
CREATE TABLE GERICHT ( BEZEICHNUNG VARCHAR ( 255 ), PREIS VARCHAR ( 255 ));
INSERT INTO  GERICHT ( BEZEICHNUNG, PREIS ) VALUES ( 'Hummersuppe', '12,50 €' );
INSERT INTO  GERICHT ( BEZEICHNUNG, PREIS ) VALUES ( 'Kürbissuppe',  '4,50 €' );
SELECT * FROM PERSON;
SELECT * FROM GERICHT;
SELECT * FROM PERSON, GERICHT;

+---------+----------+--------------+-----------+
| VORNAME | NACHNAME | BEZEICHNUNG  | PREIS     |
+---------+----------+--------------+-----------+
| Rainer  | Ziegler  | Hummersuppe  | 12,50 €   |
| Liese   | Graf     | Hummersuppe  | 12,50 €   |
| Rainer  | Ziegler  | Kürbissuppe  | 4,50 €    |
| Liese   | Graf     | Kürbissuppe  | 4,50 €    |
+---------+----------+--------------+-----------+

Projektionen eines kartesischen Produktes

Es ist möglich, die Bildung eines kartesischen Produktes mit einer Projektion auf bestimmte Spalten zu kombinieren. Dabei wird dann zuerst das kartesische Produkt gebildet und dann werden die Spalten aus dessem Ergebnis ausgewählt.

Konsole

SELECT VORNAME, BEZEICHNUNG FROM PERSON, GERICHT;

+---------+--------------+
| VORNAME | BEZEICHNUNG  |
+---------+--------------+
| Rainer  | Hummersuppe  |
| Liese   | Hummersuppe  |
| Rainer  | Kürbissuppe  |
| Liese   | Kürbissuppe  |
+---------+--------------+

Selektionen eines kartesischen Produktes

Die Bildung eines kartesischen Produktes kann auch mit einer Selektion bestimmter Spalten kombiniert werden. Dabei wird dann zuerst das kartesische Produkt gebildet und dann werden die Zeilen aus dessem Ergebnis ausgewählt.

Konsole

SELECT * FROM PERSON, GERICHT WHERE BEZEICHNUNG = 'Hummersuppe';

+---------+----------+-------------+-----------+
| VORNAME | NACHNAME | BEZEICHNUNG | PREIS     |
+---------+----------+-------------+-----------+
| Rainer  | Ziegler  | Hummersuppe | 12,50 €   |
| Liese   | Graf     | Hummersuppe | 12,50 €   |
+---------+----------+-------------+-----------+

Die Bildung eines kartesischen Produktes kann auch mit einer Projektion und  einer Selektion kombiniert werden.

Konsole

SELECT VORNAME, BEZEICHNUNG FROM PERSON, GERICHT WHERE BEZEICHNUNG = 'Hummersuppe';

+---------+-------------+
| VORNAME | BEZEICHNUNG |
+---------+-------------+
| Rainer  | Hummersuppe |
| Liese   | Hummersuppe |
+---------+-------------+

Die Reihenfolge der Klauseln im Quelltext lautet: »SELECT…FROM…WHERE…«. Die Reihenfolge bei der Ausführung kann man sich eher so vorstellen, daß zuerst das kartesische Produkt gebildet wird (»FROM…«, dann eine Selektion (»WHERE…«) erfolgt und schließlich noch die Projektion (»SELECT…«) vorgenommen wird.

Tabellen mit gleichen Spaltennamen

Vorbereitung

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 L.WERT FROM L, R;
SELECT R.WERT FROM L, R;

Projektionen eines kartesisches Produkts

Das obenstehende Skript erzeugt zwei Tabellen »L« und »R«, die beide eine Spalte namens »Wert« enthalten.

Konsole

SELECT * FROM L;

+------+
| WERT |
+------+
| 1    |
| 2    |
+------+

SELECT * FROM R;

+------+
| WERT |
+------+
| 1    |
| 3    |
+------+

Das kartesische Produkt enthält nun zwei Spalten mit dem gleichen Namen »WERT«.

Konsole

SELECT * FROM L, R;

+------+------+
| WERT | WERT |
+------+------+
| 1    | 1    |
| 2    | 1    |
| 1    | 3    |
| 2    | 3    |
+------+------+

Eine Projektion auf eine der beiden Spalten ist damit nicht mehr ohne Qualifikation möglich.

Konsole

SELECT WERT FROM L, R;

ERROR 1052 (23000): Column 'WERT' in field list is ambiguous

Die ausführlichen (qualifizierten) Spaltennamen enthalten allerdings noch den Namen der Ursprungstabelle.

kartesisches Produkt mit qualifizierten Spaltennamen.

 S.L.WERT | S.R.WERT
---------------------
 1        | 1
 2        | 1
 1        | 3
 2        | 3

Daher können die einzelnen Spalten doch ausgegeben werden, und zwar unter Verwendung des qualifizierten Spaltennamens, der in diesem Fall zwar nicht immer das Schema, aber zumindest die Ursprungstabellen enthalten muß.

Konsole

SELECT L.WERT FROM L, R;

+------+
| WERT |
+------+
| 1    |
| 2    |
| 1    |
| 2    |
+------+

Konsole

SELECT R.WERT FROM L, R;

+------+
| WERT |
+------+
| 1    |
| 1    |
| 3    |
| 3    |
+------+

Konsole

SELECT L.WERT, R.WERT FROM L, R;

+------+------+
| WERT | WERT |
+------+------+
| 1    | 1    |
| 2    | 1    |
| 1    | 3    |
| 2    | 3    |
+------+------+

Genauso muß gegebenenfalls auch bei einer Selektion  ein qualifizierter Spaltenname verwendet werden. Je nach dem angegebenen Tabellennamen erhält man unterschiedliche Ergebnisse.

Konsole

SELECT * FROM L, R WHERE L.WERT = '1';

+------+------+
| WERT | WERT |
+------+------+
| 1    | 1    |
| 1    | 3    |
+------+------+

Konsole

SELECT * FROM L, R WHERE R.WERT = '1';

+------+------+
| WERT | WERT |
+------+------+
| 1    | 1    |
| 2    | 1    |
+------+------+

Felder eines Schachbretts

Vorbereitung

DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE SPALTE ( SPALTENNAME VARCHAR ( 255 ));
INSERT INTO  SPALTE ( SPALTENNAME ) VALUES ( 'A' );
INSERT INTO  SPALTE ( SPALTENNAME ) VALUES ( 'B' );
INSERT INTO  SPALTE ( SPALTENNAME ) VALUES ( 'C' );
INSERT INTO  SPALTE ( SPALTENNAME ) VALUES ( 'D' );
INSERT INTO  SPALTE ( SPALTENNAME ) VALUES ( 'E' );
INSERT INTO  SPALTE ( SPALTENNAME ) VALUES ( 'F' );
INSERT INTO  SPALTE ( SPALTENNAME ) VALUES ( 'G' );
INSERT INTO  SPALTE ( SPALTENNAME ) VALUES ( 'H' );
CREATE TABLE ZEILE ( ZEILENNAME VARCHAR ( 255 ));
INSERT INTO  ZEILE ( ZEILENNAME ) VALUES ( '1' );
INSERT INTO  ZEILE ( ZEILENNAME ) VALUES ( '2' );
INSERT INTO  ZEILE ( ZEILENNAME ) VALUES ( '3' );
INSERT INTO  ZEILE ( ZEILENNAME ) VALUES ( '4' );
INSERT INTO  ZEILE ( ZEILENNAME ) VALUES ( '5' );
INSERT INTO  ZEILE ( ZEILENNAME ) VALUES ( '6' );
INSERT INTO  ZEILE ( ZEILENNAME ) VALUES ( '7' );
INSERT INTO  ZEILE ( ZEILENNAME ) VALUES ( '8' );

Felder eines Schachbretts

Als ein weiteres Beispiel für ein kartesisches Produkt zeigen wir hier, wie eine Tabelle mit alle 64 Feldern eines Schachbrettes aus den Namen der Spalten und den Namen der Zeilen erzeugt wird, indem diese beiden Tabellen kartesisch miteinander multipliziert werden.

Ein Schachbrett

   A  B  C  D  E  F  G  H
8  A8 B8 C8 D8 D8 F8 G8 H8
7  A7 B7 C7 D7 D7 F7 G7 H7
6  A6 B6 C6 D6 D6 F6 G6 H6
5  A5 B5 C5 D5 D5 F5 G5 H5
4  A4 B4 C4 D4 D4 F4 G4 H4
3  A3 B3 C3 D3 D3 F3 G3 H3
2  A2 B2 C2 D2 D2 F2 G2 H2
1  A1 B1 C1 D1 D1 F1 G1 H1

Konsole (MySQL, E92, C99, C03)

SELECT * FROM SPALTE, ZEILE;

+-------------+-------------+
| SPALTENNAME | ZEILENNAME  |
+-------------+-------------+
| A           | 1           |
| B           | 1           |

...

| G           | 8           |
| H           | 8           |
+-------------+-------------+

Übungsfragen

?   Kartesisches Produkt ermitteln

Es gebe zwei Tabellen »A« und »N« gemäß der folgenden Auflistung.

Wie lauten die Zeilen des kartesischen Produktes »SELECT * FROM A, N;«?

Wie lauten die Zeilen des kartesischen Produktes »SELECT * FROM N, A;«?

Konsole

SELECT * FROM A;

+------+
| A    |
+------+
| X    |
| Y    |
+------+

SELECT * FROM N;

+------+
| N    |
+------+
| 1    |
+------+

Übungsaufgaben

/   Turnierpaarungen ausgeben

Bei einem Turnier soll jedes Mitglied der Mannschaft B gegen jedes Mitglied der Mannschaft C einmal antreten. Die Mitglieder der beiden Mannschaften finden sich nach der Eingabe des folgenden Skripts in den gleichnamigen Tabellen. Erstellen Sie eine abgeleitete Tabelle, die alle Paarungen enthält.

Skript (MySQL )

DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE B ( BEZEICHNUNG VARCHAR ( 255 ));
INSERT INTO  B ( BEZEICHNUNG ) VALUES ( 'Brigitte Weiss' );
INSERT INTO  B ( BEZEICHNUNG ) VALUES ( 'Emil Bonke' );
INSERT INTO  B ( BEZEICHNUNG ) VALUES ( 'Reinhard Gruber' );
CREATE TABLE C ( BEZEICHNUNG VARCHAR ( 255 ));
INSERT INTO  C ( BEZEICHNUNG ) VALUES ( 'Ilka Holtkamp' );
INSERT INTO  C ( BEZEICHNUNG ) VALUES ( 'Karin Kinstowski' );

/   Spalten ausgeben

Schreiben Sie jeweils eine Abfrage zur Ausgabe der linken  beziehungsweise der rechten Spalte  der alle Paarungen enthaltenden Tabelle aus der vorigen Übungsaufgabe.