SQL - JOIN, left, right, implizit
Verfasst: Fr, 06. Jul 2012 7:58
Hallo,
nachdem das Thema SQL immer weitere Kreise zieht, und manche Frage aufkommt, halte ich es für sinnvoll, zum Thema "Relation" bzw. "Join" etwas zu schreiben.
Für den SET RELATION in Xbase gibt es keine SQL-Entsprechung. Der SET RELATION Befehl bleibt bis zur expliziten Aufhebung (z.B. durch das Ende des Programms) gültig und wird bei jedem Zugriff (Veränderung der Satzposition - recno()) auf die führende Datei der RELATION im Hintergrund aktiv.
In SQL muss eine solche RELATION jedesmal explizit angefordert werden. Wenn ich einen SELECT ausführe, der mehrere Tabellen verknüpft, dann muss auch ein UPDATE (der diese Auswahl verändern soll) die entsprechenden Verknüpfungen besitzen.
Hierzu ein Beispiel in MySQL. Zuerst erstelle ich die Database und weise sie meinem Job zu:
Jetzt wird eine Tabelle für die Hunde angelegt, und einige Sätze eingefügt:
Jetzt werden die Tricks definiert, welche die Hunde lernen sollen:
Als drittes Element brauchen wir eine Tabelle, die den jeweiligen Hund mit dem jeweiligen Trick verknüpft:
Was können unsere Hunde?
Wenn wir jetzt wissen wollen, wie der jeweilige Trick benannt ist, muss der SELECT erweitert werden:
Während der erste SELECT sechs Ergebniszeilen erbrachte, erhalten wir beim zweiten SELECT nur noch fünf. Der Grund ist die zweite Zeile in learnedtricks: {1, 21}. Es gibt keinen Trick mit der ID 21, daher läuft die zweite Zeile aus dem ersten SELECT ins Leere und wird nicht im Ergebnis ausgewiesen.
Das gleiche geschieht, wenn wir einen weiteren Hund anlegen, der noch gar nichts kann (ausser schlafen):
Wenn wir dogs separat betrachten, erhalten wir alle Sätze:
Aber schon bei der Verknüpfung mit learnedtricks "verschwindet" unser Neuzugang:
Dies ist der "Normalzustand", bzw. der sogenannte Inner Join. Alle Sätze, die keine Entsprechung in jeder der anderen Tabellen haben, fallen unter den Tisch.
Wenn ich alle Sätze der linken Tabelle haben will, ändert sich die Syntax leicht, und wir verwenden einen LEFT JOIN:
Da für Schläfer kein Eintrag vorhanden ist, erhalten die Felder aus learnedtricks den Wert NULL (für "nicht vorhanden"). Der Begriff LEFT JOIN bezieht sich darauf, dass alle Sätze aus der am weitesten links stehenden Tabelle in der SQL-Anweisung verwendet werden, auch wenn keine Entsprechung in anderen Tabellen vorhanden ist.
Zurück zur anderen Syntax: der Inner Join kann auch als
dargestellt werden. Dies ist der sogenannte ANSI-Style.
Weitere JOIN-Varianten:
Der CROSS JOIN erzeugt ein karthesisches Produkt aller Sätze aller Tabellen:
5 Zeilen aus dogs * 6 Zeilen aus learnedtricks ergibt 30 Ergebniszeilen. Wenn wir noch tricks hinzufügen, erhalten wir 150 Ergebniszeilen.
Verbleibt noch der Right (Outer) Join, bei dem alle Sätze aus der rechts im Ausdrucken stehenden Tabelle zurückgeliefert werden, auch wenn es keine Entsprechung in der/den anderen Tabellen gibt:
Dies soll als erster Einstieg mal genügen.
Anzumerken bleibt, dass JOINs komplexe, geschachtelte Schleifen sehr gut abbilden können, und man mit einem Statement enorm viel bewegen kann.
Gruss,
Georg
nachdem das Thema SQL immer weitere Kreise zieht, und manche Frage aufkommt, halte ich es für sinnvoll, zum Thema "Relation" bzw. "Join" etwas zu schreiben.
Für den SET RELATION in Xbase gibt es keine SQL-Entsprechung. Der SET RELATION Befehl bleibt bis zur expliziten Aufhebung (z.B. durch das Ende des Programms) gültig und wird bei jedem Zugriff (Veränderung der Satzposition - recno()) auf die führende Datei der RELATION im Hintergrund aktiv.
In SQL muss eine solche RELATION jedesmal explizit angefordert werden. Wenn ich einen SELECT ausführe, der mehrere Tabellen verknüpft, dann muss auch ein UPDATE (der diese Auswahl verändern soll) die entsprechenden Verknüpfungen besitzen.
Hierzu ein Beispiel in MySQL. Zuerst erstelle ich die Database und weise sie meinem Job zu:
Code: Alles auswählen
mysql> create database kennel;
Query OK, 1 row affected (0.00 sec)
mysql> use kennel;
Database changed
Code: Alles auswählen
mysql> create table dogs (hundid int, hundename char(32), welped date);
Query OK, 0 rows affected (0.11 sec)
mysql> insert into dogs values(1, 'Billy', '2012-03-14');
Query OK, 1 row affected (0.05 sec)
mysql> insert into dogs values(2, 'Willy', '2012-03-14');
Query OK, 1 row affected (0.05 sec)
mysql> insert into dogs values(3, 'Snoopeye', '2012-03-14');
Query OK, 1 row affected (0.05 sec)
mysql> insert into dogs values(4, 'Wilder', '2012-03-15');
Query OK, 1 row affected (0.05 sec)
Code: Alles auswählen
mysql> create table tricks (trickid int, trickname char(32));
Query OK, 0 rows affected (0.09 sec)
mysql> insert into tricks values(1, "Sitz");
Query OK, 1 row affected (0.05 sec)
mysql> insert into tricks values(2, "Friss");
Query OK, 1 row affected (0.03 sec)
mysql> insert into tricks values(3, "Platz (nur einmalig verwendbar!)");
Query OK, 1 row affected (0.05 sec)
mysql> insert into tricks values(4, "Komm her");
Query OK, 1 row affected (0.05 sec)
mysql> insert into tricks values(5, "Zeitung holen");
Query OK, 1 row affected (0.03 sec)
Code: Alles auswählen
mysql> create table learnedtricks (lthundid int, lttrickic int);
Query OK, 0 rows affected (0.09 sec)
mysql> insert into learnedtricks values(1,1);
Query OK, 1 row affected (0.05 sec)
mysql> insert into learnedtricks values(1,21);
Query OK, 1 row affected (0.05 sec)
mysql> insert into learnedtricks values(1,2);
Query OK, 1 row affected (0.06 sec)
mysql> insert into learnedtricks values(2,2);
Query OK, 1 row affected (0.05 sec)
mysql> insert into learnedtricks values(3,2);
Query OK, 1 row affected (0.03 sec)
mysql> insert into learnedtricks values(4,1);
Query OK, 1 row affected (0.03 sec)
Code: Alles auswählen
mysql> SELECT * FROM dogs, learnedtricks WHERE hundid = lthundid;
+--------+-----------+------------+----------+-----------+
| hundid | hundename | welped | lthundid | lttrickic |
+--------+-----------+------------+----------+-----------+
| 1 | Billy | 2012-03-14 | 1 | 1 |
| 1 | Billy | 2012-03-14 | 1 | 21 |
| 1 | Billy | 2012-03-14 | 1 | 2 |
| 2 | Willy | 2012-03-14 | 2 | 2 |
| 3 | Snoopeye | 2012-03-14 | 3 | 2 |
| 4 | Wilder | 2012-03-15 | 4 | 1 |
+--------+-----------+------------+----------+-----------+
6 rows in set (0.05 sec)
Code: Alles auswählen
mysql> SELECT * FROM dogs, learnedtricks, tricks WHERE hundid = lthundid AND lttrickic = trickid;
+--------+-----------+------------+----------+-----------+---------+-----------+
| hundid | hundename | welped | lthundid | lttrickic | trickid | trickname |
+--------+-----------+------------+----------+-----------+---------+-----------+
| 1 | Billy | 2012-03-14 | 1 | 1 | 1 | Sitz |
| 1 | Billy | 2012-03-14 | 1 | 2 | 2 | Friss |
| 2 | Willy | 2012-03-14 | 2 | 2 | 2 | Friss |
| 3 | Snoopeye | 2012-03-14 | 3 | 2 | 2 | Friss |
| 4 | Wilder | 2012-03-15 | 4 | 1 | 1 | Sitz |
+--------+-----------+------------+----------+-----------+---------+-----------+
5 rows in set (0.00 sec)
Das gleiche geschieht, wenn wir einen weiteren Hund anlegen, der noch gar nichts kann (ausser schlafen):
Code: Alles auswählen
mysql> insert into dogs values(5, 'Schläfer', '2012-07-06');
Query OK, 1 row affected (0.03 sec)
Code: Alles auswählen
mysql> select * from dogs;
+--------+-----------+------------+
| hundid | hundename | welped |
+--------+-----------+------------+
| 1 | Billy | 2012-03-14 |
| 2 | Willy | 2012-03-14 |
| 3 | Snoopeye | 2012-03-14 |
| 4 | Wilder | 2012-03-15 |
| 5 | Schläfer | 2012-07-06 |
+--------+-----------+------------+
5 rows in set (0.00 sec)
Code: Alles auswählen
mysql> SELECT * FROM dogs, learnedtricks WHERE hundid = lthundid;
+--------+-----------+------------+----------+-----------+
| hundid | hundename | welped | lthundid | lttrickic |
+--------+-----------+------------+----------+-----------+
| 1 | Billy | 2012-03-14 | 1 | 1 |
| 1 | Billy | 2012-03-14 | 1 | 21 |
| 1 | Billy | 2012-03-14 | 1 | 2 |
| 2 | Willy | 2012-03-14 | 2 | 2 |
| 3 | Snoopeye | 2012-03-14 | 3 | 2 |
| 4 | Wilder | 2012-03-15 | 4 | 1 |
+--------+-----------+------------+----------+-----------+
6 rows in set (0.00 sec)
Wenn ich alle Sätze der linken Tabelle haben will, ändert sich die Syntax leicht, und wir verwenden einen LEFT JOIN:
Code: Alles auswählen
mysql> SELECT * FROM dogs LEFT JOIN learnedtricks ON hundid = lthundid;
+--------+-----------+------------+----------+-----------+
| hundid | hundename | welped | lthundid | lttrickic |
+--------+-----------+------------+----------+-----------+
| 1 | Billy | 2012-03-14 | 1 | 1 |
| 1 | Billy | 2012-03-14 | 1 | 21 |
| 1 | Billy | 2012-03-14 | 1 | 2 |
| 2 | Willy | 2012-03-14 | 2 | 2 |
| 3 | Snoopeye | 2012-03-14 | 3 | 2 |
| 4 | Wilder | 2012-03-15 | 4 | 1 |
| 5 | Schläfer | 2012-07-06 | NULL | NULL |
+--------+-----------+------------+----------+-----------+
7 rows in set (0.02 sec)
Zurück zur anderen Syntax: der Inner Join kann auch als
Code: Alles auswählen
SELECT * FROM dogs JOIN learnedtricks ON hundid = lthundid
Weitere JOIN-Varianten:
Der CROSS JOIN erzeugt ein karthesisches Produkt aller Sätze aller Tabellen:
Code: Alles auswählen
mysql> select * from dogs CROSS JOIN learnedtricks;
+--------+-----------+------------+----------+-----------+
| hundid | hundename | welped | lthundid | lttrickic |
+--------+-----------+------------+----------+-----------+
| 1 | Billy | 2012-03-14 | 1 | 1 |
| 2 | Willy | 2012-03-14 | 1 | 1 |
| 3 | Snoopeye | 2012-03-14 | 1 | 1 |
| 4 | Wilder | 2012-03-15 | 1 | 1 |
| 5 | Schläfer | 2012-07-06 | 1 | 1 |
| 1 | Billy | 2012-03-14 | 1 | 21 |
| 2 | Willy | 2012-03-14 | 1 | 21 |
| 3 | Snoopeye | 2012-03-14 | 1 | 21 |
| 4 | Wilder | 2012-03-15 | 1 | 21 |
| 5 | Schläfer | 2012-07-06 | 1 | 21 |
| 1 | Billy | 2012-03-14 | 1 | 2 |
| 2 | Willy | 2012-03-14 | 1 | 2 |
| 3 | Snoopeye | 2012-03-14 | 1 | 2 |
| 4 | Wilder | 2012-03-15 | 1 | 2 |
| 5 | Schläfer | 2012-07-06 | 1 | 2 |
| 1 | Billy | 2012-03-14 | 2 | 2 |
| 2 | Willy | 2012-03-14 | 2 | 2 |
| 3 | Snoopeye | 2012-03-14 | 2 | 2 |
| 4 | Wilder | 2012-03-15 | 2 | 2 |
| 5 | Schläfer | 2012-07-06 | 2 | 2 |
| 1 | Billy | 2012-03-14 | 3 | 2 |
| 2 | Willy | 2012-03-14 | 3 | 2 |
| 3 | Snoopeye | 2012-03-14 | 3 | 2 |
| 4 | Wilder | 2012-03-15 | 3 | 2 |
| 5 | Schläfer | 2012-07-06 | 3 | 2 |
| 1 | Billy | 2012-03-14 | 4 | 1 |
| 2 | Willy | 2012-03-14 | 4 | 1 |
| 3 | Snoopeye | 2012-03-14 | 4 | 1 |
| 4 | Wilder | 2012-03-15 | 4 | 1 |
| 5 | Schläfer | 2012-07-06 | 4 | 1 |
+--------+-----------+------------+----------+-----------+
30 rows in set (0.00 sec)
Verbleibt noch der Right (Outer) Join, bei dem alle Sätze aus der rechts im Ausdrucken stehenden Tabelle zurückgeliefert werden, auch wenn es keine Entsprechung in der/den anderen Tabellen gibt:
Code: Alles auswählen
mysql> select * from learnedtricks RIGHT JOIN tricks ON trickid = lttrickic;
+----------+-----------+---------+----------------------------------+
| lthundid | lttrickic | trickid | trickname |
+----------+-----------+---------+----------------------------------+
| 1 | 1 | 1 | Sitz |
| 4 | 1 | 1 | Sitz |
| 1 | 2 | 2 | Friss |
| 2 | 2 | 2 | Friss |
| 3 | 2 | 2 | Friss |
| NULL | NULL | 3 | Platz (nur einmalig verwendbar!) |
| NULL | NULL | 4 | Komm her |
| NULL | NULL | 5 | Zeitung holen |
+----------+-----------+---------+----------------------------------+
8 rows in set (0.02 sec)
Anzumerken bleibt, dass JOINs komplexe, geschachtelte Schleifen sehr gut abbilden können, und man mit einem Statement enorm viel bewegen kann.
Gruss,
Georg