SQL in Alteryx - JOIN-Varianten übersetzen | Tipps Tuesday #30

[RolandSchubert]

Warum sollte man eine SQL-Abfrage mit Alteryx abbilden? Zum Beispiel, um die Bereitstellung von Daten einfacher, übersichtlicher und für den "Nicht-Experten" transparenter zu gestalten. Stellt sich natürlich die Frage, wie man von SQL in Alteryx "übersetzt" - das schauen wir uns heute mal näher an.

In sehr vielen SQL-Statements taucht neben SELECT, FROM und WHERE auch JOIN auf. Während SELECT und FROM sich über das INPUT DATA Tool abbilden lassen, WHERE gewissermaßen dem FILTER Tool entspricht, ist es bei JOIN nicht ganz so einfach. 

Aber - was genau ist eigentlich ein JOIN?

Mit einem JOIN kann man Daten aus mehreren Tabellen verbinden und in ein gemeinsames Ergebnis erzeugen. Die Verbindung wird dabei über gleiche Werte in einem oder mehreren Feldern hergestellt. Das ist zum Beispiel dann nützlich, wenn es eine Tabelle gibt, die Umsätze für einzelne Artikel enthält, eine zweite Tabelle dann die zugehörigen Artikelbezeichnungen liefert. Die Verbindung kann dann in der Regel über die Artikelnummer hergestellt werden, im Ergebnis können wir dann die Artikel im Klartext ausgeben. Der Excel-Anwender wird bei dieser Beschreibung vermutlich sofort an den SVERWEIS denken, der dem JOIN zumindest sehr ähnlich ist.

Bis hierhin klingt das eigentlich ganz einfach. Und sollte doch mit dem entsprechenden Tool in Alteryx auch ganz einfach umzusetzen sein. Es gibt sogar zwei  verschiedene Tools, die für die Verknüpfung von zwei (Join Tool) oder mehr (Multiple Join Tool) Tabellen vorgesehen sind. 

 

In der Konfiguration muss man dann nur noch die Felder auswählen, über die eine Verbindung hergestellt werden soll, sowie die Felder auswählen, die im Ergebnis enthalten sein sollen - fertig.

Das Join Tool hat drei Output Anchor, über die jeweils die entsprechenden Daten ausgegeben werden:

Aber - es gibt doch ganz verschiedene Typen von JOIN, wird jetzt vielleicht der "SQL-Profi" einwenden. Wo kann ich denn einstellen, ob ich einen INNER, OUTER, LEFT, RIGHT oder FULL JOIN möchte?

Was bitte ist ein FULL JOIN und wie unterscheidet er sich vom INNER JOIN, könnte dagegen der SQL-Neuling fragen.

Keine Sorge, beide Fragen werden beantwortet! Wir schauen uns die verschiedenen Typen noch genauer an, dabei lässt sich auch gleich zeigen, wie der entsprechende JOIN-Typ in Alteryx abgebildet werden kann. Dabei nutze ich zwei kleine Tabellen, die Verknüpfung wird jeweils über das Feld "Key"  hergestellt. Das Feld ist in beiden Tabellen so benannt, die Feldnamen spielen aber für die Verknüpfung keine Rolle.   

1. Der INNER JOIN

Der "intuitivste" Typ des JOIN ist der INNER JOIN. Wenn man als Kernaufgabe eines JOIN sieht, Daten aus zwei Tabellen zu verknüpfen, wenn der Inhalt des oder der gewählten Schlüsselfelder (Felder, die zur Verknüpfung ausgewählt wurden) der Tabellen übereinstimmt, dann tut der INNER JOIN genau das - er liefert die Datensätze, für die diese Bedingung zutrifft.

. 

Das Ergebnis eines INNER JOIN ist also die "Schnittmenge" der beiden Tabellen (natürlich nur auf das Feld bezogen, über das die Verbindung hergestellt wird). 

In Alteryx bedeutet das, einfach die Werte abzugreifen, die über den "J" Output Anchor ausgegeben werden. Das entsprechende SQL-Statement habe ich zur Orientierung gleich danebengestellt.  

Einen INNER JOIN braucht man immer dann, wenn man die zueinander passenden Datensätze aus zwei Tabellen sucht, also wie im oben bereits genannten Beispiel zu Umsatzdaten je Artikelnummer die Artikelbezeichnung hinzufügen will.

Was aber, wenn es bei manchen Artikeln keine Bezeichnung gibt, die aber natürlich trotzdem im Bericht (dann eben nur mit Artikelnummmer) aufgeführt werden sollen? Dann kommt der LEFT JOIN zum Einsatz.

2. Der LEFT JOIN

 Der LEFT JOIN (oder auch LEFT OUTER JOIN) nimmt ins Ergebnis alle Datensätze der "linken" (zuerst genannten) Tabelle auf, ganz gleich, ob es in der "rechten" (zweiten) Tabelle einen passenden Datensatz gibt..

 

In Alteryx müssen wir hier die Daten, die vom "J" und vom "L" Output Anchor ausgegeben werden, zusammenführen. Das geschieht nicht über eine Einstellung im JOIN Tool (es gibt also keinen Schalter für "LEFT JOIN"), sondern hierzu wird einfach ein UNION Tool benutzt:

  

Klar - die Felder, die aus der zweiten Tabelle hinzugefügt werden, fehlen im Ergebnis (im Beispiel sind es die Werte in Record 4 und 5, bei denen der Wert von [Value_B] fehlt) !

Was aber, wenn es zwar für jede Artikelnummer eine Bezeichnung gibt, es nicht immer für jeden Artikel Umsätze gibt, das Ergebnis aber immer alle Artikel enthalten soll? Nun, hier brauchen wir dann den RIGHT JOIN. 

3. Der RIGHT JOIN

 Der RIGHT JOIN (oder auch RIGHT OUTER JOIN) nimmt ins Ergebnis alle Datensätze der "rechten" (zuletzt genannten) Tabelle auf, ganz gleich, ob es in der "linken" (ersten) Tabelle einen passenden Datensatz gibt..

In Alteryx müssen wir hier die Daten, die vom "J" und vom "R" Output Anchor ausgegeben werden, zusammenführen. Das geschieht nicht über eine Einstellung im JOIN Tool (es gibt natürlich auch keinen Schalter für "RIGHT JOIN"), sondern wir benutzen wieder ein UNION Tool:

Und natürlich fehlen diesmal im Ergebnis die Felder, die aus der ersten Tabelle kommens (im Beispiel sind es die Werte in Record 1 und 2, bei denen der Wert von [Value_A] fehlt) !

Und natürlich gibt es auch die Kombination beider Fällen (es gibt also Artikel mit Umsatz, für die keine Bezeichnung existiert, die aber im Ergebnis enthalten sein sollen, und auch Artikel, für die keine Umsätze gebucht wurden). Und dazu gibt es den FULL JOIN.

4. Der FULL JOIN

 Der FULL JOIN (FULL OUTER JOIN) nimmt ins Ergebnis alle Datensätze auf, die in einer der beiden Tabellen enthalten sind. Wenn sie sich verbinden lassen, werden sie verbunden, ansonsten enthält das Ergebnis eben nur die Felder der Tabelle, aus der die Datensätze kommen.

Diesmal müssen wir in Alteryx alle drei Output Anchor abgreifen, als "L", "J" und "R". zusammenführen. Erneut hilft das  UNION Tool:

Damit gibt es nun Datensätze, in denen die Felder aus Tabelle 1 fehlen, anderen fehlen die Felder aus Tabelle 2, wieder andere sind vollständig.

Die verbreitetesten Typen des JOIN haben wir damit erfasst. Es gibt allerdings noch ein paar weitere Formen, die auch nützlich sein können.

5. Der EXCLUSIVE FULL JOIN

 Der EXCLUSIVE FULL JOIN (FULL OUTER JOIN) nimmt ins Ergebnis alle Datensätze auf, die nur in einer der beiden Tabellen enthalten sind. Es gibt also keine "Treffer", genau die werden ausgeschlossen. Im Prinzip ist das ein FULL OUTER JOIN ohne die Datensätze des INNER JOIN.

In Alteryx liefern uns "L" und "R" Output Anchor die gewünschten Ergebnisse, mit dem UNION Tool bilden wir das Ergebnis:

Damit gibt es nun Datensätze, in denen die Felder aus Tabelle 1 fehlen, anderen fehlen die Felder aus Tabelle 2 - Datensätze, die Felder aus beiden Tabellen enthalten, gibt es nicht (in Satz 1 und 2 fehlen die Werte aus Tabelle 1, bei 3 und 4 fehlen die aus Tabelle 2).

 

Das war es aber immer noch nicht so ganz, es gibt ja noch den ...

6. Der EXCLUSIVE LEFT JOIN

 Der EXCLUSIVE LEFT JOIN  nimmt ins Ergebnis alle Datensätze auf, die in Tabelle 1, aber nicht in Tabelle 2  enthalten sind. Es gibt also wieder keine "Treffer".

Diesmal brauchen wir nur den "L" Output Anchor - damit ist auch kein UNION Tool notwendig:

Das Ergebnis sind die Datensätze aus Tabelle 1, für die es keinen "Partner" in Tabelle 2 gibt (also zum Beispiel Artikel ohne Bezeichnung).

Natürlich darf die umgekehrte Variante nicht fehlen, der ...

7. Der EXCLUSIVE RIGHT JOIN

Beim EXCLUSIVE RIGHT JOIN  landen im Ergebnis nur Datensätze die in Tabelle 2, aber nicht in Tabelle 1  enthalten sind. Und auch hier keine "Treffer".

Diesmal können wir unser Ergebnis direkt vom "R" Output Anchor abgreifen:

Das Ergebnis sind die Datensätze aus Tabelle 2, für die es keinen "Partner" in Tabelle 1 gibt (also zum Beispiel Artikel ohne Umsatz).

Das waren sie nun aber endlich, die JOIN Typen, Einer Erweiterung des klassischen SQL verdanken wir eine weitere JON-Variante, die in Alteryx aber nicht mit dem JOIN Tool abgebildet wird.

8. Der CROSS JOIN

Der CROSS JOIN verfolgt einen völlig anderen Ansatz; während es bei allen bisher genannten JOIN-Typen stets ein Feld gibt, über das eine Verbindung hergestellt wird, erzeugt der CROSS JOIn ein kartesisches Produkt - er kombiniert jeden Datensatz von Tabelle 1 mit jedem Datensatz von Tabelle 2:

 

Ein JOIN im klassischen Sinne ist das nicht - in SQL gibt es die Variante erst seit SQL92. Besondere Bedeutung hat der CROSS JOIN übrigens in MDX, dem SQL-Pendant für multidimensionale Datenbanken.

Entsprechend wird in Alteryx hier auch nicht das JOIN Tool genutzt, sonders das APPEND FIELDS Tool:

Im Ergebnis sehen wir alle möglichen Kombinationen der Datensätze von Tabelle 1 und Tabelle 2.

Einen weiteren Sonderfall gibt es noch:

9. Der SELF JOIN

Eigentlich ist das kein weiterer Typ des JOIN, sondern es geht hier eher um die beteiligten Tabellen - hier ist es nämlich nur eine, die mit sich selbst verknpüft wird.

Mit sich selbst? Was soll das denn? Nun, denken wir einmal an eine Liste von Rechnungen und Zahlungseingängen. Wir wollen zu jeder Rechnung den entsprechenden Zahlungseingang finden - beide stehen in der gleichen Tabelle und sind über die Rechnungsnummer zu identifizieren (zu einer Rechnung kann es mehrere Zahlungseingänge geben).

In Alteryx sieht das dann etwa so aus:

 

Wie gesagt, ein eigenständiger JOIN-Typ ist das nicht - hier geht es nur darum, dass für einen JOIN - gleich welcher Art - nicht unbedingt zwei verschiedenen Tabellen erforderlich sind.

Das war es jetzt aber wirklich - und vielleicht hilft es ja dabei, das eine oder andere SQL-Statement in eine übersichtlichere, transparentere Form zu bringen.

Weitere Tipps Tuesday Beiträge

Dieser Eintrag ist Teil der Tipps Tuesday-Serie, alle Einträge dieser Serie findest du in unserem Index aufgelistet.