JOIN - Normalisierte Tabellen für eine Abfrage wieder zusammenfassen

• » Home
• »Sql-Tutorial
• » Join

Vorbemerkung

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

1. 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.

2. 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.

3. 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.
4. 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.
5. 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.
6. 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

1. 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.
2. 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.
3. 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.