SQL - JOIN, left, right, implizit

Alles zum SQL-Dialekt

Moderator: Moderatoren

Antworten
georg
Der Entwickler von "Deep Thought"
Der Entwickler von "Deep Thought"
Beiträge: 2823
Registriert: Fr, 08. Feb 2008 21:29
Hat sich bedankt: 95 Mal
Danksagung erhalten: 13 Mal

SQL - JOIN, left, right, implizit

Beitrag von georg »

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:

Code: Alles auswählen

mysql> create database kennel;
Query OK, 1 row affected (0.00 sec)

mysql> use kennel;
Database changed
Jetzt wird eine Tabelle für die Hunde angelegt, und einige Sätze eingefügt:

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)
Jetzt werden die Tricks definiert, welche die Hunde lernen sollen:

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)
Als drittes Element brauchen wir eine Tabelle, die den jeweiligen Hund mit dem jeweiligen Trick verknüpft:

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)
Was können unsere Hunde?

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)
Wenn wir jetzt wissen wollen, wie der jeweilige Trick benannt ist, muss der SELECT erweitert werden:

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)
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):

Code: Alles auswählen

mysql> insert into dogs values(5, 'Schläfer', '2012-07-06');
Query OK, 1 row affected (0.03 sec)
Wenn wir dogs separat betrachten, erhalten wir alle Sätze:

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)
Aber schon bei der Verknüpfung mit learnedtricks "verschwindet" unser Neuzugang:

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)
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:

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)
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

Code: Alles auswählen

SELECT * FROM dogs JOIN learnedtricks ON hundid = lthundid
dargestellt werden. Dies ist der sogenannte ANSI-Style.

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)
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:

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)
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
Liebe Grüsse aus der Eifel,

Georg S. Lorrig
Redakteur der Wiki des Deutschprachigen Xbase-Entwickler e.V.
Benutzeravatar
brandelh
Foren-Moderator
Foren-Moderator
Beiträge: 15689
Registriert: Mo, 23. Jan 2006 20:54
Wohnort: Germersheim
Hat sich bedankt: 65 Mal
Danksagung erhalten: 33 Mal
Kontaktdaten:

Re: SQL - JOIN, left, right, implizit

Beitrag von brandelh »

=D> =D> =D>
Klasse Beispiel, gut erklärt und unterhaltsam :D
Gruß
Hubert
UliTs
Der Entwickler von "Deep Thought"
Der Entwickler von "Deep Thought"
Beiträge: 2828
Registriert: Fr, 10. Feb 2006 9:51
Wohnort: Aachen
Hat sich bedankt: 259 Mal
Danksagung erhalten: 12 Mal
Kontaktdaten:

Re: SQL - JOIN, left, right, implizit

Beitrag von UliTs »

Hallo Georg,

Du kannst wirklich hervorragend Wissen vermitteln!

Uli
-------
Mitglied XuG Cologne
Mitglied XuG Osnabrück
Benutzeravatar
AUGE_OHR
Marvin
Marvin
Beiträge: 12903
Registriert: Do, 16. Mär 2006 7:55
Wohnort: Hamburg
Hat sich bedankt: 19 Mal
Danksagung erhalten: 44 Mal

Re: SQL - JOIN, left, right, implizit

Beitrag von AUGE_OHR »

absolut TOP !!! :thumbright:
danke
gruss by OHR
Jimmy
Benutzeravatar
AUGE_OHR
Marvin
Marvin
Beiträge: 12903
Registriert: Do, 16. Mär 2006 7:55
Wohnort: Hamburg
Hat sich bedankt: 19 Mal
Danksagung erhalten: 44 Mal

Re: SQL - JOIN, left, right, implizit

Beitrag von AUGE_OHR »

hi georg,

die in deinen Demo verwendeten "Fields" habe ja alle unterschiedliche Namen.

Code: Alles auswählen

WHERE hundid = lthundid;
nun habe ich in Xbase (alle Versionen) oft gleiche Feldnamen speziell bei "RELATION" verwendet.
Newbie Frage : wie lautet dann die Syntax ?

Code: Alles auswählen

WHERE Hund1.hundid = Hund2.hundid;
gruss by OHR
Jimmy
georg
Der Entwickler von "Deep Thought"
Der Entwickler von "Deep Thought"
Beiträge: 2823
Registriert: Fr, 08. Feb 2008 21:29
Hat sich bedankt: 95 Mal
Danksagung erhalten: 13 Mal

Re: SQL - JOIN, left, right, implizit

Beitrag von georg »

Hallo, Jimmy -


nehmen wir dieses Beispiel:

Code: Alles auswählen

SELECT * FROM dogs, learnedtricks WHERE hundid = lthundid;
Es gibt das Token AS, das hier verwendet wird:

Code: Alles auswählen

SELECT * FROM dogs AS A, learnedtricks AS B WHERE a.hundid = b.lthundid;
Alternativ geht auch folgende Anweisung:

Code: Alles auswählen

SELECT * FROM dogs AS A, learnedtricks AS B WHERE a.hundid = lthundid;
Der Alias ist also nicht zwingend erforderlich, wenn das Feld eindeutig ist.

Normalerweise solltest Du dann bei Feldern das A oder B (Du kannst aber auch Wörter oder als Namen zugelassene Kombinationen von Buchstaben und Zahlen verwenden) vorstellen. Nicht erforderlich ist es dann, wenn eines der Felder eindeutig ist, d.h. es in keiner anderen Tabelle ein Feld dieses Namens gibt.


Gruss,

Georg

P.S.: Wie immer gilt, jeder SQL Server mag eigene Auffassungen davon haben, wie das eine oder andere umzusetzen ist.
Liebe Grüsse aus der Eifel,

Georg S. Lorrig
Redakteur der Wiki des Deutschprachigen Xbase-Entwickler e.V.
Antworten