Aufgabe der JOIN-Verknüpfung: Die Daten wurden normalisiert, also auf verschiedene Tabellen aufgeteilt.
So sind die Daten zu einer Bestellung (Artikel, Datum, Menge, Vertreter) in der Beispiel-Datenbank nicht
in einer Tabelle abgelegt, sondern wurden auf drei Tabellen verteilt: Artikel und Vertreter umfassen die
Stammdaten, die Tabelle Umsatz enthält die eigentlichen Bewegungsdaten. Pro Zeile wird lediglich ein
Verweis auf die zur einzelnen Bestellung gehörenden Datensätze abgelegt. Sollen alle Daten einer
Bestellung als ein Datensatz ausgegeben werden, so müssen die Einträge aus den verschiedenen Tabellen
einander zugeordnet werden. Dies leistet die JOIN-Verknüpfung. Ein JOIN fügt zwei Tabellen zu einer
neuen, virtuellen Tabelle zusammen, die anschließend mit einem weiteren JOIN sowie der nächsten Tabelle
verknüpft werden kann.
Folglich genügt es, einen JOIN zwischen zwei Tabellen zu behandeln.
Syntax
Komma-Version:
FROM <Tabelle oder Abfrage>, <Tabelle oder Abfrage>
Einfachste Form des INNER JOIN
FROM <Tabelle oder Abfrage> INNER JOIN <Tabelle oder Abfrage>
ON <Spalte1> OPERATOR <Spalte2>
Mehrfache Verknüpfung:
FROM <Tabelle oder Abfrage> INNER JOIN <Tabelle oder Abfrage>
ON <Spalte1> OPERATOR <Spalte2>
AND
<Spalte3> OPERATOR <Spalte4>
Statt INNER kann auch eines der folgenden Schlüsselwörter verwendet werden:
LEFT, RIGHT, OUTER
Statt dem AND-Operator kann auch OR verwendet werden, ferner können mehr als zwei Vergleichsausdrücke
angegeben sowie AND und OR kombiniert werden.
Eine Tabelle kann auch zweimal angegeben werden, man spricht von einer Selbstverknüpfung,
da Zeilen der Tabelle zu anderen Zeilen der Tabelle in Beziehung gesetzt werden. Abgesehen von der Komma-Version ist mindestens
ein Alias-Name für eine Tabelle Pflicht, um die Spalten eindeutig zu beschreiben.
Beispiele
SELECT A.*, U.*
FROM ARTIKEL As A, UMSATZ As U
Diese Komma-Version kombiniert jede Zeile der Tabelle ARTIKEL mit jeder Zeile der Tabelle UMSATZ und
gibt das Ergebnis aus 4 * 9 = 36 Zeilen vollständig aus. Vergleicht man die beiden Spalten
A.A_NR und U.A_NR, so fällt auf, daß den Umsätzen in den meisten Fällen Artikel zugeordnet sind, welche
von dem im Umsatz erwähnten Artikel verschieden sind. Diese (36 - 9) = 27 Ergebnisse sind also sinnlos,
Durch die in der ON-Klausel festgelegte Filterbedingung in der folgenden JOIN-Klausel werden diese
Datensätze entfernt.
SELECT A.A_NR,
A.A_NAME,
A.A_PREIS,
U.DATUM,
U.A_STUECK,
A.A_PREIS * U.A_STUECK AS Preis
FROM UMSATZ AS U INNER JOIN ARTIKEL AS A
ON U.A_NR = A.A_NR
Dies ist die einfachste und am häufigsten genutzte Form des JOIN. Die Spalte A.A_NR
ist gleichzeitig der Primärschlüssel der Tabelle ARTIKEL, zu einem
Artikel kann es mehrere Bestellungen, also Zeilen in der Tabelle UMSATZ
geben. Aus der oben angegebenen Komma-Version werden all jene Zeilen entfernt, bei welchen die beiden Spalten
A.A_NR und U.A_NR nicht übereinstimmen.
Ersetzt man in diesem Ausdruck den INNER JOIN durch die Komma-Version, so wird deutlich: Hier ist die
Komma-Version unsinnig. Denn eine Mengenangabe, die sich auf einen Artikel bezieht, wird kombiniert
mit dem Preis eines anderen Artikels.
SELECT A.A_NR,
A.A_NAME,
A.A_PREIS,
V.V_NR,
V.V_NAME,
U.DATUM,
A.A_PREIS * U.A_STUECK AS Preis
FROM (ARTIKEL AS A INNER JOIN UMSATZ AS U
ON A.A_NR = U.A_NR) INNER JOIN VERTRETER AS V
ON U.V_NR = V.V_NR
Dies ist die Version für die Verknüpfung von drei Tabellen. In der ersten Klammer werden zunächst jene neun
Ergebniszeilen gefiltert, bei denen der Umsatz zum Artikel gehört. Zu dieser neuen Tabelle werden jene
Zeilen hinzugenommen, bei denen die Vertreter-Id übereinstimmt. Die Komma-Version würde (4 * 9) * 3 = 108
Zeilen liefern, der doppelte Join liefert korrekt die neun Zeilen aus der Tabelle UMSATZ, ergänzt um die
Informationen aus den beiden anderen Tabellen.
Manche Datenbank-Systeme, etwa Access, fordern, daß bei einem solchen Ausdruck ein Join geklammert wird.
Beim MS-SqlServer kann auf die Klammern verzichtet werden.
Ausdrücke mit Operatoren werden beim INNER JOIN nur dann ausgewertet, wenn die einzelnen Ausdrücke nicht
Null sind. Ist ein Ausdruck ein Spaltenname, so werden bsp. bei einer Prüfung auf Gleichheit alle Zeilen
ignoriert, in welchen die zugehörige Zelle leer ist. Damit liefert der INNER JOIN immer nur Informationen
über tatsächliche Vorgänge, etwa über Verkäufe von Artikeln. Es sind damit jedoch zunächst keine Informationen
über nicht verkaufte Artikel ermittelbar, der normale JOIN erzeugt keine Informationen zu einem
Nicht-Ereignis. Sollen hierüber Aussagen getroffen werden, gelingt
dies mit LEFT bzw. RIGHT JOIN:
SELECT A.A_NR,
U.DATUM
FROM ARTIKEL AS A LEFT JOIN UMSATZ AS U
ON A.A_NR = U.A_NR
Fügen Sie Ihrer Tabelle ARTIKEL einen neuen Artikel hinzu. Als Befehl:
INSERT INTO ARTIKEL(A_NR, A_NAME, A_PREIS)
VALUES (25, 'Mantel', 149.00)
Da es für diesen Artikel keine passende Zeile in der Tabelle UMSATZ gibt, wird dieser Artikel bei einer
INNER-JOIN-Verknüpfung ignoriert. Der obige LEFT JOIN gibt zunächst dasselbe wie ein INNER JOIN aus.
Zusätzlich fügt er alle Datensätze aus der links (left) stehenden Tabelle hinzu, die im Ergebnis bislang
noch fehlen. Werden Spalten aus der rechts stehenden Tabelle mit ausgegeben, so werden deren Werte als
NULL-Werte ausgegeben. In Kombination mit einer WHERE-Abfrage lassen sich die niemals verkauften Artikel
ermitteln:
SELECT A.A_NR,
A.A_NAME
FROM ARTIKEL AS A LEFT JOIN UMSATZ AS U
ON A.A_NR = U.A_NR
WHERE U.UMSATZ_NR IS NULL
Beachten Sie, daß Sie mit dieser Abfrage nicht ermitteln, welcher Artikel bloß an bestimmten Tagen nicht
verkauft wurde. Wurde ein Artikel ein einziges Mal verkauft, so erscheint er nicht mehr in dieser Liste.
Ermittlung der an einzelnen Tagen nicht verkauften Artikel. Betrachten Sie zunächst die folgende Abfrage:
SELECT C.DATUM, D.A_NR
FROM
(SELECT Distinct A.DATUM
FROM UMSATZ As A
Where A.DATUM Is Not Null) As C,
(SELECT B.A_NR
FROM ARTIKEL As B) As D
Die beiden inneren Abfragen liefern Ihnen eine Liste aller Datumsangaben, an welchen etwas verkauft wurde
sowie eine Liste aller Artikel-Nummern. Beide Tabellen werden durch die Komma-Version kombiniert, so
daß die Ergebnistabelle jede Kombination aus Datum und Artikel umfaßt. Kombinieren Sie diese neue, nur
temporär existierende Tabelle per LEFT JOIN mit den tatsächlichen Umsätzen:
SELECT E.DATUM, E.A_NR,
F.DATUM, F.A_NR
FROM
(SELECT C.DATUM, D.A_NR
FROM
(SELECT Distinct A.DATUM
FROM UMSATZ As A
Where A.DATUM Is Not Null) As C,
(SELECT B.A_NR
FROM ARTIKEL As B) As D) As E
LEFT JOIN UMSATZ AS F
ON E.DATUM = F.DATUM AND E.A_NR = F.A_NR
Die Ausgabe ergibt, daß am 25.6 bei den Artikeln 12 und 13 die Spalten F.DATUM und F.A_NR leer sind, da
für beide Artikel an diesem Tag kein Umsatz vorliegt. Entfernen Sie F.DATUM und F.A_NR in der Liste der
Ausgabespalten und ergänzen Sie die Abfrage um die folgende Bedingung:
WHERE F.DATUM IS NULL
Dann liefert Ihnen diese Abfrage sämtliche Kombinationen von Tagen mit den an diesen Tagen nicht verkauften
Artikeln.
Selbstverknüpfung einer Tabelle:
SELECT DISTINCT U1.DATUM, U1.A_NR
FROM UMSATZ As U1 INNER JOIN UMSATZ As U2
On (U1.DATUM = U2.DATUM) And
(U1.A_NR = U2.A_NR) And
(U1.A_STUECK <> U2.A_STUECK)
Datum
A_NR
24.06.1999
12
24.06.1999
13
Diese Abfrage ordnet zunächst all jene Zeilen paarweise einander zu, die sich auf dasselbe Datum und denselben
Artikel beziehen. Da als dritte Bedingung die Verschiedenartigkeit der A_STUECK-Spalte geprüft wird, werden zunächst alle
Kombinationen aus Datum und Artikel entfernt, die sich auf dieselbe Zeile beziehen. Dasselbe würde jedoch die Prüfung
U1.UMSATZ_NR <> U1.UMSATZ_NR erfüllen. Die tatsächlich verwendete Prüfung entfernt jedoch zusätzlich alle Kombinationen aus
Datum und Artikel, bei welchen der Artikel mehrfach mit derselben Stückzahl verkauft worden ist, nur jene Kombinationen
werden ausgegeben, bei welchen der Artikel an einem Tag zu mindestens zwei verschiedenen Stückzahlen verkauft worden ist. Da
jede diese Bedingungen erfüllende Kombination von Zeilen auch erfüllt ist, wenn die beiden Zeilen vertauscht sind, kann man die
Bedingung <> auch ersetzen durch < und mit DISTINCT alle weiteren Zeilen dieser Kombination entfernen.
Sollen dagegen jene Kombinationen aus Tagen und Artikeln bestimmt werden, für die an einem Tag nur eine Stückzahl, jedoch mehrere
Verkäufe durchgeführt wurden, so genügt es nicht, nur den Unterschieds-Operator durch den Gleichheits-Operator zu ersetzen. Denn
in diesem Fall werden auch jene Tage/Artikel ausgegeben, die genau einmal verkauft worden sind. Wird diese Abfrage ergänzt um
die Bedingung U1.Umsatz_Nr <> U2.Umsatz_Nr, so wird nur der Artikel 11 am 25.06.1999 ausgegeben, der zweimal zwanzigmal
verkauft wurde. Insgesamt:
SELECT DISTINCT U1.DATUM, U1.A_NR
FROM UMSATZ As U1 INNER JOIN UMSATZ As U2
On (U1.DATUM = U2.DATUM) And
(U1.A_NR = U2.A_NR) And
(U1.A_STUECK = U2.A_STUECK) And
(U1.UMSATZ_NR <> U2.UMSATZ_NR)
Bemerkungen
Falls Sie Tabellen verknüpfen, so sollten Sie im Regelfall eine der JOIN-Methoden verwenden. Insbesondere ist die
'alte' Technik, welche die Klammerversion nutzt und die ON-Klausel in die WHERE-Bedingung verschiebt, zu vermeiden.
Vergleichen Sie die beiden folgenden Darstellungen:
SELECT A.*, U.*
FROM ARTIKEL As A, UMSATZ As U
WHERE A.A_NR = U.A_NR
AND A.A_NR < 13
mit
SELECT A.*, U.*
FROM ARTIKEL As A INNER JOIN UMSATZ As U
On A.A_NR = U.A_NR
WHERE A.A_NR < 13
Bei der ersten, nicht zu verwendenden Version ist die Bedingung zur Verknüpfung der beiden Tabellen gemischt mit der Bedingung,
welche wenige Zeilen auswählt. Die zweite Version trennt dagegen die Verknüpfung der Tabellen von der Reduktion der Spalten.
In der Regel werden zunächst alle WHERE-Bedingungen ausgeführt, welche die Zahl der Zeilen reduzieren, erst über die verbleibende
Restmenge der Zeilen wird der JOIN gebildet.
LEFT und RIGHT Join sind zueinander symmetrisch. Die beiden folgenden Anweisungen zur Ermittlung der niemals
verkauften Artikel sollten deshalb denselben Ablaufplan erzeugen (fügen Sie wie oben einen niemals verkauften Artikel hinzu):
SELECT A.*
FROM ARTIKEL As A LEFT JOIN UMSATZ As U
ON A.A_NR = U.A_NR
WHERE U.A_NR Is null
sowie
SELECT A.*
FROM UMSATZ As U RIGHT JOIN ARTIKEL As A
ON U.A_NR = A.A_NR
WHERE U.A_NR Is null
OUTER JOIN kombiniert LEFT/RIGHT und akzeptiert sowohl übereinstimmende Zeilen als auch Nullwerte beider Tabellen.
Left- bzw. Right-Join-Konstrukte sind zwingend, falls eine Tabelle eine Verknüpfung zu einer optionalen Randtabelle enthält.
Ein klassisches Beispiel ordnet Personen Titel zu, die Titel (Dr., Dr.med, Prof.Dr.) sind in einer Randtabelle gespeichert, die
Personentabelle enthält eine Spalte mit der ID der Titel-Tabelle. Gibt es Personen ohne Titel, so kann entweder in der Randtabelle
ein Eintrag ohne Text erzeugt und ein INNER JOIN zur Verknüpfung genutzt werden oder es fehlt ein solcher Eintrag. Damit ist
die entsprechende Zelle in der Personentabelle leer, so daß ein Left/Right-Join von der Haupt- auf die Randtabelle gebildet werden
muß, falls alle Personen gefunden werden sollen.
Ein Beispiel für den MS-SqlServer:
SELECT A.Nachname + ', ' +
A.Vorname + CoalEsce(' ' + B.Titel, '')
As Name
From Personen As A Left Join PersonenTitel As B
On A.TitelId = B.TitelId
CoalEsce liefert den ersten Ausdruck in der Liste, der von Null verschieden ist. Existiert
ein Titel, so wird dieser mit einem Leerzeichen als Trenner ausgegeben, ansonsten wird ein Leerstring zur bisherigen Ausgabe
aus Nachname, Komma und Vorname hinzugefügt.
Link zur hiesigen Seite als QR-Code
Kontaktformular:
Schreiben Sie mir und wir bauen gemeinsam Ihre neue Web-Datenbank!
Mit dem Klick auf den Button stimmen Sie zu, daß Cookies in Ihrem Browser gespeichert werden. Informationen zu den gespeicherten Cookies finden Sie unter Datenschutz#Cookies.Bei Fragen zur Technik wenden Sie sich bitte an Server-Daten - Web-Datenbank-Lösungen