📖DQL - Join

Tabellen miteinander verknüpfen

Verknüpfung von zwei Tabellen kann, muss aber nicht über die Primär-/Fremdschlüssel-Beziehung erfolgen. Die meisten Joins sind EQUI JOINS. Darin werden zwei Tabellen über übereinstimmende Spalten verknüpft. Als Join-Operator wird ein = verwendet. Beispiel: Rechnungs- und Kundentabelle werden über die Kundennummer, die in beiden Tabellen enthalten ist, verknüpft.

Die seltene Sonderform eines Joins ist der NONEQUI JOIN. Bei Nonequi Joins gibt es keine direkt aufeinander referenzierenden Spalten. Als Join-Operator wird kein = verwendet, sondern meist kommt ein BETWEEN … AND … zum Einsatz.

⚡️ Ausprobieren

Öffne diese Beispiel-Datenbank in einem neuen Fenster: https://www.w3schools.com/sql/trysqlserver.asp?filename=trysql_func_sqlserver_concat

Hier kannst du direkt im Browser SQL Statements ausprobieren. Probiere die Beispiele hier mit einem ⚡️-Symbol aus.

Die JOIN Arten

Angenommen, du hast die folgenden zwei Tabellen, welche je eine Spalte haben:

A      B
-      -
1      3
2      4
3      5
4      6

Beachte, dass die Einträge 1 und 2 nur bei A, 5 und 6 nur bei B vorhanden sind. 3 und 4 gibt es bei beiden. Folgende Tabelle erklärt, wie die Ausgabe aussehen würde, wenn diese zwei Tabellen mit den verschiedenen JOIN Arten verknüpft werden:

JOIN-Art	Bild	Resultierende Tabelle
INNER JOIN		a | b -----+----- 3 | 3 4 | 4
LEFT OUTER JOIN		a | b -----+----- 1 | null 2 | null 3 | 3 4 | 4
RIGHT OUTER JOIN		a | b -----+----- 3 | 3 4 | 4 null | 5 null | 6
FULL OUTER JOIN		a | b -----+----- 1 | null 2 | null 3 | 3 4 | 4 null | 6 null | 5

INNER JOIN

• JOIN-Bedingung in eigener Klausel

• WHERE-Klausel wird reingehalten

• JOIN-Typ widerspiegelt sich in JOIN-Ausdruck

SELECT tab1.spalte_1, tab2.spalte_2 
FROM tab1 
INNER JOIN tab2 ON tab1.spalte_x = tab2.spalte_y
WHERE . . .

⚡️ Ausprobieren

Aufgabe

1. Erstelle ein SQL-Statement, welches mittels INNER JOIN die Tabellen Orders und Customers über die CustomerID verknüpft. Gib dabei mindestens OrderID, CustomerName und OrderDate aus.

2. Gib Produktname, Preis und den Namen des Herstellers aus von allen Produkten, die teurer als 100$ sind.

Lösung

-- 1. Bestellung und dazugehöriger Kundenname
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Customers.CustomerID = Orders.CustomerID;

-- 2. Hersteller und Preis der teuersten Produkte
SELECT Products.ProductName, Products.Price, Suppliers.SupplierName
FROM Products 
INNER JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID
WHERE Price > 100;

JOIN über WHERE-Klausel

Es ist auch möglich, zwei Tabellen über eine einfache WHERE-Klausel zu verbinden. Dies ist aber weniger performant. Es wird vor allem bei ORACLE eingesetzt, da vor Version 9i nur diese Möglichkeit bestand.

SELECT tab1.spalte_1, tab2.spalte_2 
FROM tab1, tab2 
WHERE tab1.spalte_x = tab2.spalte_y;

Weitere Bedingungen werden mit AND ergänzt Tabellennamen müssen bei Namensgleichheit von Spalten angegeben werden. Tabellennamen sollten vor allen Spaltennamen stehen.

⚡️ Ausprobieren

Aufgabe

Löse die Aufgaben aus INNER JOIN mittels eines einfachen JOIN über WHERE-Klausel.

Lösung

-- 1. Bestellung und dazugehöriger Kundenname
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders, Customers
WHERE Customers.CustomerID = Orders.CustomerID;

-- 2. Hersteller und Preis der teuersten Produkte
SELECT Products.ProductName, Products.Price, Suppliers.SupplierName
FROM Products, Suppliers
WHERE Products.SupplierID = Suppliers.SupplierID
AND Price > 100;

Tabellen-Alias

Wie du in den letzten zwei Beispielen sicher bemerkt hast, muss man so ziemlich viel schreiben. Um das etwas abzukürzen, kann man den Tabellen einen Alias geben, welcher dann in dieser Abfrage verwendet werden kann.

Dadurch kann man einfacher und schneller SQL-Statements schreiben, die übersichtlicher sind und dabei passieren auch noch weniger Schreibfehler.

SELECT a.spalte_1, b.spalte_2 
FROM tab1 AS a, tab2 AS b
WHERE a.spalte_x = b.spalte_y;

-- Variante ohne AS
SELECT a.spalte_1, b.spalte_2 
FROM tab1 a 
INNER JOIN tab2 b ON a.spalte_x = b.spalte_y;

⚡️ Ausprobieren

Passe die vorigen ⚡️Ausprobieren Beispiele an und verwende Tabellen-Aliasse.

OUTER JOIN

Syntaxmässig sind OUTER JOINs gleich wie INNER JOINs. Einfach, dass Statt INNER da LEFT OUTER, RIGHT OUTER oder FULL OUTER steht.

⚡️ Ausprobieren

Passe die vorigen ⚡️Ausprobieren Beispiele an und verwende die verschiedenen OUTER JOIN-Arten.

JOINS mit mehr als zwei Tabellen

⚡️ Ausprobieren

Aufgabe

Liste alle Angestellten (Vorname und Nachname) auf und dazu die Namen aller Kunden, für welche die Angestellten schon einmal eine Bestellung bearbeitet haben.

Lösung

-- Lösung mit JOIN
SELECT DISTINCT Employees.FirstName, Employees.LastName, Customers.ContactName
FROM Employees 
    INNER JOIN (Orders 
        INNER JOIN Customers 
        ON Orders.CustomerID = Customers.CustomerID
    )
    ON Employees.EmployeeID = Orders.EmployeeID
ORDER BY Employees.FirstName, Employees.LastName, Customers.ContactName;


-- Alternative Lösung mit WHERE (KEIN LEFT/RIGHT JOIN möglich)
SELECT DISTINCT 
    Employees.FirstName, Employees.LastName, Customers.ContactName
FROM 
    Employees , Orders, Customers 
WHERE 
    Orders.CustomerID = Customers.CustomerID
    AND Employees.EmployeeID = Orders.EmployeeID
ORDER BY Employees.FirstName, Employees.LastName, Customers.ContactName;

Weitere JOIN Arten

• CROSS JOIN

  • Beim CROSS JOIN handelt es sich um ein Kreuzprodukt (kartesisches Produkt)

  • Es werden alle möglichen Kombinationen gebildet (jeder mit jedem)

  • In der Regel ungewollt!

  • Wird z.B. zum Generieren von Testdaten verwendet

• SELF JOIN (rekursiver JOIN)

  • JOIN einer Tabelle mit sich selbst

  • Dient zur Abbildung von Hierarchien

  • Dieselbe Tabelle zweimal mit unterschiedlichem Alias verwenden

⚡️ Ausprobieren

Aufgabe

1. Zeige mittels rekursivem JOIN alle Mitarbeitenden der Northwind-Datenbank an (ID, Vorname, Nachname) und zu jedem den Namen des Vorgesetzten (ReportsTo)

2. Finde den CEO (die Person, welche keinen weiteren Vorgesetzten hat).

Lösung

-- 1. Mitarbeitende mit Vorgesetzten
SELECT MA.EmployeeID, MA.FirstName, MA.LastName, 
       Chefs.FirstName + ' ' + Chefs.LastName AS Vorgesetzter 
FROM Employees AS MA, Employees AS Chefs 
WHERE MA.ReportsTo = Chefs.EmployeeID;
  
-- 2. Finde den CEO!
SELECT MA.FirstName + ' ' + MA.LastName AS CEO
FROM dbo.Employees AS MA 
LEFT JOIN dbo.Employees AS Chefs 
    ON MA.ReportsTo = Chefs.EmployeeID 
WHERE Chefs.EmployeeID IS NULL;

💡 Eigene Aufgabe

• Überlege dir eine Aufgabe für eine Abfrage in der Northwind Datenbank.

• Gebe die Aufgabe (noch ungelöst) morgen deinem Instruktor ab.

• Die Aufgaben werden dann willkürlich zum lösen an die Klasse verteilt.