Sicher und einfach joinen mit JOIN TO ONE

Der wohl häufigste Grund, Tabellen zu joinen, ist der Zugriff auf zusätzliche Spalteninformationen in den verknüpften Tabellen. Dabei werden die Datensätze aus der Basistabelle über einen Fremdschlüssel mit anderen Tabellen verknüpft, in denen sich weitere Informationen zu dem vorhandenen Datensatz befinden. Genau dieser Anwendungsfall wird ab der Oracle Datenbankversion 23.26.2 mit der neuen Syntax JOIN TO ONE vereinfacht. Gleichzeitig werden typische Fehler zur Laufzeit erkannt und somit verhindert.

Vereinfachung der Syntax

Wie stark die Syntax sich mit JOIN TO ONE vereinfacht, zeigt folgendes Beispiel aus dem HR-Schema, bei dem zu jedem Mitarbeiter zusätzlich angezeigt werden soll, auf welchem Kontinent er arbeitet:

SELECT e.last_name, r.region_name
FROM employees e
	LEFT JOIN departments d ON e.department_id = d.department_id
 	LEFT JOIN locations l ON d.location_id = l.location_id
 	LEFT JOIN countries c ON l.country_id = c.country_id
 	LEFT JOIN regions r ON c.region_id = r.region_id;


LAST_NAME                 REGION_NAME              
------------------------- -------------------------
King                      Americas
Yang                      Americas
...
107 Zeilen ausgewählt. 

Mit JOIN TO ONE vereinfacht sich die Abfrage deutlich, ohne dass sich die Semantik der Abfrage ändert:

SELECT e.last_name, r.region_name
FROM employees e
	JOIN TO ONE(departments, locations, countries, regions r);

JOIN TO ONE führt ausgehend von der Basistabelle (im Beispiel employees) standardmäßig LEFT JOINs mit den als Argument aufgezählten Tabellen aus. Die Verknüpfungsbedingungen, die beim gewöhnlichen JOIN in der ON-Klausel angegeben werden, werden hierbei automatisch über die vorhandenen Fremdschlüssel ermittelt. Dabei werden die angegeben Tabellen sukzessive mit den vorhandenen Datensätzen verknüpft. Damit die dafür benötigten Fremdschlüssel bekannt sind, müssen die Tabellen in der gleichen Reihenfolge angegeben werden, wie beim gewöhnlichen JOIN mit ON-Klausel. Vertauscht man beispielsweise in der letzten Abfrage die Reihenfolge der Tabellen locations und countries, so erhält man eine Fehlermeldung:

SELECT e.last_name, r.region_name
FROM employees e
	JOIN TO ONE(departments, countries, locations, regions r);


ORA-18641: Kein Join-Schlüssel für "COUNTRIES"  gefunden

Optionale Erweiterungen

Neben dem standardmäßigen LEFT JOIN können auch INNER JOINs verwendet werden, wie das folgende Beispiel zeigt.

SELECT d.department_name, e.last_name
FROM departments d
   JOIN TO ONE (INNER JOIN employees e);


DEPARTMENT_NAME                LAST_NAME                
------------------------------ -------------------------
Administration                 Whalen
Marketing                      Martinez
...
11 Zeilen ausgewählt. 

Auch hier muss keine ON-Klausel angegeben werden. Da die Basistabelle in diesem Beispiel die Tabelle departments ist, wird über den Fremdschlüssel manager_id automatisch eine Verknüpfung zum Primärschlüssel employee_id der employees-Tabelle hergestellt. Durch die Option INNER JOIN werden jedoch nur Abteilungen angezeigt, die über einen Manager verfügen. Für den Anwendungsfall, Datensätze der Basistabelle über JOINs mit zusätzlichen Informationen zu ergänzen, sind die Optionen RIGHT JOIN und FULL JOIN nicht geeignet. Daher ist INNER JOIN die einzige weitere syntaktische Option neben dem Standard LEFT OUTER JOIN.

Im folgenden Beispiel wird jeder Datensatz aus der Tabelle job_history noch um den Namen des Mitarbeiters aus der Tabelle employees und den Namen des ehemaligen Jobs aus der Tabelle jobs ergänzt. Hierfür wird die Möglichkeit genutzt, innerhalb der JOIN TO ONE-Klausel die ON-Klausel für eine Tabellenverknüpfung anzugeben.

SELECT jh.employee_id, e.last_name, j.job_id
FROM job_history jh
   JOIN TO ONE(employees e, jobs j ON (jh.job_id=j.job_id))
ORDER BY jh.employee_id, j.job_id;

Da sowohl die Tabelle job_history, als auch die Tabelle employees über einen Fremdschlüssel zur jobs-Tabelle verfügen, reicht es nicht, die Tabelle jobs ohne die ON-Klausel anzugeben. Dies führt zu einer Fehlermeldung:

SELECT jh.employee_id, e.last_name, j.job_id
FROM job_history jh
  JOIN TO ONE(employees e, jobs j)
ORDER BY jh.employee_id, j.job_id;


ORA-18642: Mehrere Join-Schlüssel für "JOBS" gefunden

Um die Verknüpfung eindeutig festzulegen, braucht es hier die konkrete Verknüpfungsbedingung. Daher bietet die Syntax JOIN TO ONE auch die Option, die ON-Klausel explizit anzugeben.

Mit JOIN TO ONE typische Fehler erkennen

Da der Anwendungsfall für JOIN TO ONE lediglich vorsieht, die Datensätze der Basistabelle mit Informationen aus anderen Tabellen zu ergänzen, dürfen die Datensätze der Basistabelle innerhalb der JOIN TO ONE-Klausel nicht dupliziert werden. Wird ein Datensatz durch die JOIN TO ONE-Klausel vervielfältigt, führt dies zu einer Fehlermeldung.
Da die meisten typischen Fehler bei der Tabellenverknüpfung aber gerade diese Duplikate erzeugen, kann die JOIN TO ONE-Klausel auch eingesetzt werden, um diese Fehler zu erkennen. Dies ist besonders hilfreich, da die typischen Fehler zwar zu falschen Ergebnissen führen können, aber trotzdem gültige Abfragen sind und damit potentiell unentdeckt bleiben.

Das folgende Beispiel zeigt einen typischen Fehler.

SELECT 
   e.last_name,
   r.region_name
FROM employees e
   LEFT OUTER JOIN departments d ON e.department_id = d.department_id
   -- auf der rechten Seite der ON-Klausel in der nächsten Zeile steht d.location_id statt l.location_id
   LEFT OUTER JOIN locations l ON d.location_id = d.location_id
   LEFT OUTER JOIN countries c ON l.country_id = c.country_id
   LEFT OUTER JOIN regions r ON c.region_id = r.region_id;


LAST_NAME                 REGION_NAME              
------------------------- -------------------------
Grant                     Europe
Grant                     Europe
...
2.439 Zeilen ausgewählt. 

Durch die Angabe der falschen Tabelle in der Verknüpfungsbedingung wird unbeabsichtigt ein Kreuzprodukt (Cross Join) erzeugt, d.h. jeder Datensatz der departments-Tabelle wird mit jedem Datensatz der locations-Tabelle verknüpft.
In einem solchen Fall kann die Verknüpfung mit JOIN TO ONE kontrolliert werden.

SELECT 
   e.last_name,
   r.region_name
FROM employees e
   JOIN TO ONE(
   LEFT OUTER JOIN departments d ON e.department_id = d.department_id
-- auf der rechten Seite der ON-Klausel in der nächsten Zeile steht d.location_id statt l.location_id
   LEFT OUTER JOIN locations l ON d.location_id = d.location_id
   LEFT OUTER JOIN countries c ON l.country_id = c.country_id
   LEFT OUTER JOIN regions r ON c.region_id = r.region_id
   );


ORA-18640: JOIN TO ONE hat beim Join in "L" mehrere Zeilen erreicht. Dies hat zu einem nicht eindeutigen Join geführt 

Die originale Abfrage wird fehlerfrei ausgeführt, liefert wegen des Kreuzprodukts aber ein fehlerhaftes Ergebnis, was eventuell unbemerkt bleibt. Bei der Verwendung der JOIN TO ONE-Klausel tritt jedoch ein Fehler auf, da die Datensätze der Basistabelle dupliziert werden. Somit wird die fehlerhafte Abfrage nicht ausgeführt und das versehentliche Kreuzprodukt wird auf jeden Fall bemerkt.

Ein zweiter typischer Fehler wird im nächsten Beispiel gezeigt, das für einige Mitarbeiter den Namen des aktuellen und des ehemaligen Jobs anzeigen soll.

SELECT e.employee_id, e.last_name, j_new.job_title aktueller_job, j_old.job_title vorheriger_job
FROM employees e
   JOIN jobs j_new ON (e.job_id = j_new.job_id)
   JOIN job_history jh ON (e.employee_id = jh.employee_id)
   JOIN jobs j_old ON (jh.job_id = j_old.job_id)
WHERE e.employee_id >=200;


EMPLOYEE_ID LAST_NAME                 AKTUELLER_JOB                       VORHERIGER_JOB                     
----------- ------------------------- ----------------------------------- -----------------------------------
        200 Whalen                    Administration Assistant            Administration Assistant           
        200 Whalen                    Administration Assistant            Public Accountant                  
        201 Martinez                  Marketing Manager                   Marketing Representative 

Bei dieser Abfrage wird davon ausgegangen, dass jeder Mitarbeiter maximal einen früheren Job in der Firma ausgeübt hat. Solche Annahmen sind oft historisch gewachsen. Als die Abfrage erstellt wurde, mag diese Annahme auch erfüllt gewesen sein, nach einiger Zeit kann die Annahme aber durch neue Daten verletzt werden.
Da die Annahme nicht explizit in das Datenmodell übertragen wurde, gerät sie eventuell in Vergessenheit, oder ist anderen Nutzern der Datenbank von vornherein nicht bekannt.
Trotzdem führt eine Verletzung der Annahme nicht zu einem Datenbankfehler, sondern erneut zu unerwünschten Datensätzen in der Ergebnismenge, die wieder unbemerkt bleiben können. Im letzten Beispiel ist dieser Fall offensichtlich eingetreten, was zu mehreren Datensätzen für die Mitarbeiterin Whalen führt.

Um die JOIN TO ONE-Klausel anzuwenden, müssen für jeden (INNER) JOIN explizit die Schlüsselworte INNER JOIN angegeben werden.

SELECT e.employee_id, e.last_name, j_new.job_title aktueller_job, j_old.job_title vorheriger_job
FROM employees e
   JOIN TO ONE(
   INNER JOIN jobs j_new ON (e.job_id = j_new.job_id)
   INNER JOIN job_history jh ON (e.employee_id = jh.employee_id)
   INNER JOIN jobs j_old ON (jh.job_id = j_old.job_id)
   )
WHERE e.employee_id >=200;


ORA-18640: JOIN TO ONE hat beim Join in "JH" mehrere Zeilen erreicht. Dies hat zu einem nicht eindeutigen Join geführt

Auch in diesem Fall werden unerwünschte Datensätze mit der JOIN TO ONE-Klausel zur Laufzeit erkannt und somit die Datenintegrität bewahrt.

Fazit

Für den Anwendungsfall, die Datensätze einer Basistabelle mit Informationen aus anderen Tabellen zu ergänzen, bietet die neue JOIN TO ONE-Klausel zum einen eine deutlich vereinfachte Syntax, zum anderen eine Möglichkeit, die Duplikation der Datensätze zu verhindern, was die Fehleranfälligkeit deutlich reduziert.

Weitere Neuerungen der Oracle AI Database 26ai lernen Sie im Praxisworkshop New Features Oracle 26ai für Entwickler kennen.