Da wir immer wieder nach der Syntax zu (Outer) Joins gefragt werden, beschäftigen wir uns in diesem Tipp einmal mit den Neuerungen in Version 12c und den Einschränkungen in den vorangegangenen Versionen.
Vorausgesetzt wird, dass der Leser bereits Erfahrungen mit der Formulierung von Joins gemacht hat und ihm auch die in Oracle 9i eingeführte ANSI-konforme Join Syntax nicht unbekannt ist.
AUFHEBUNG DER ORA-01417 EINSCHRÄNKUNG
Beginnen wir mit einer Einschränkung, mit der Entwickler und Anwender bis einschließlich Version 11gR2 konfrontiert wurden: Wer über die Oracle-eigene Syntax zwei oder mehr Tabellen mit einer anderen Tabelle über Outer Join verknüpfen wollte, bekam in der Regel einen ORA-01417 Fehler.
Anhand der Tabellen EMP, DEPT und SALGRADE des Benutzers SCOTT soll dies im weiteren Verlauf demonstriert werden. Dabei wurde die Tabelle SALGRADE um eine weitere Zeile ergänzt, die keine Entsprechung in der Tabelle EMP findet.
SQL> INSERT INTO scott.salgrade VALUES (6, 10000, 12999);
SQL> SELECT * FROM scott.salgrade;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
6 10000 12999
6 Zeilen ausgewählt.
Die beiden Tabellen DEPT und SALGRADE sollen jeweils durch einen Outer Join mit EMP verbunden werden, was auf folgende Weise bisher noch nicht möglich war:
SQL> SELECT d.deptno, e.ename, s.grade
FROM scott. dept d, scott.emp e, scott.salgrade s
WHERE d.deptno = e.deptno(+)
AND e.sal(+) BETWEEN s.losal AND s.hisal;
ORA-01417: Tabelle darf mit max. einer anderen Tabelle durch Outer-Join verbunden werden
Mit der Version 12c ist diese Einschränkung aufgehoben worden. Macht man sich anhand der Ergebnismenge allerdings klar, welche Logik hinter dieser Syntax steht, stellt sich die Frage, ob und wann dies wirklich gewünscht ist.
SQL> SELECT d.deptno, e.ename, s.grade
FROM dept d, emp e, salgrade s
WHERE d.deptno = e.deptno(+)
AND e.sal(+) BETWEEN s.losal AND s.hisal;
DEPTNO ENAME GRADE
---------- ---------- ----------
20 SMITH 1
30 ALLEN 3
30 WARD 2
20 JONES 4
30 MARTIN 2
30 BLAKE 4
10 CLARK 4
20 SCOTT 4
10 KING 5
30 TURNER 3
20 ADAMS 1
30 JAMES 1
20 FORD 4
10 MILLER 2
10 6
10 3
10 1
30 6
30 5
20 6
20 5
20 3
20 2
40 6
40 5
40 4
40 3
40 2
40 1
29 Zeilen ausgewählt.
Offensichtlich erzeugt Oracle ein Kreuzprodukt zwischen den beiden Outer-Tabellen DEPT und SALGRADE und gibt davon alle Zeilen aus, die keine direkte Entsprechung zu EMP haben. Bereits vor Version 12c hätte man das selbe Ergebnis über den Trick einer Inline View erhalten können:
SQL> SELECT i.deptno, e.ename, i.grade
FROM scott.emp e, (SELECT d.deptno, s.grade, s.losal, s.hisal
FROM scott.dept d, scott.salgrade s) i
WHERE i.deptno = e.deptno(+)
AND e.sal(+) BETWEEN i.losal AND i.hisal;
Bezogen auf die drei Tabellen von SCOTT, möchte man - sinnvollerweise - zu den 14 Mitarbeitern lediglich noch die Abteilungen aus DEPT sehen, in denen keine Mitarbeiter sind (hier: 40) und die Gehaltsstufen aus SALGRADE in denen sich kein Gehalt aus EMP befindet (hier: 6).
Mittels ANSI-Join-Syntax lautet die - recht übersichtliche - Lösung dazu bereits seit Version 9i:
SQL> SELECT d.deptno, e.ename, s.grade
FROM scott.dept d LEFT JOIN scott.emp e
ON d.deptno = e.deptno
FULL JOIN scott.salgrade s
ON e.sal between s.losal and s.hisal;
DEPTNO ENAME GRADE
---------- ---------- ----------
40
10 KING 5
20 FORD 4
20 SCOTT 4
20 JONES 4
30 BLAKE 4
10 CLARK 4
30 ALLEN 3
30 TURNER 3
10 MILLER 2
30 WARD 2
30 MARTIN 2
20 ADAMS 1
30 JAMES 1
20 SMITH 1
6
16 Zeilen ausgewählt.
Über die Oracle-eigene Syntax sieht das schon wieder wesentlich komplizierter aus (und mit zunehmender Tabellenanzahl wird dies nicht besser ...):
SQL> SELECT d.deptno, e.ename, s.grade
FROM scott.dept d, scott.emp e, scott.salgrade s
WHERE d.deptno = e.deptno(+)
AND e.sal BETWEEN s.losal(+) AND s.hisal(+)
UNION
SELECT d.deptno, e.ename, s.grade
FROM scott.dept d, scott.emp e, scott.salgrade s
WHERE d.deptno(+) = e.deptno
AND e.sal(+) BETWEEN s.losal AND s.hisal;
NEUERUNGEN DER ANSI-JOIN SYNTAX
Mit Version 12c hat Oracle drei neue Varianten im Bereich der ANSI-Join Syntax aufgenommen.
- CROSS APPLY
- OUTER APPLY
- LATERAL
Das Interessante an allen drei Neuerungen ist die Unterstützung der sog. Left Correlation. Dabei kann z. B. aus der FROM-Klausel innerhalb einer Inline View auf Werte aus einer anderen Tabelle der äußeren FROM-Klausel Bezug genommen werden. Diese andere Tabelle muss sich links von den neuen Schlüsselwörtern APPLY oder LATERAL befinden. Daher der Name Left Correlation.
CROSS APPLY
Der CROSS APPLY ist eine Erweiterung des CROSS JOIN und bildet in der einfachsten Form ein Kreuzprodukt der beteiligten Tabellen. Beispiel:
SQL> SELECT d.deptno, d.dname, e.ename
FROM scott.dept d CROSS APPLY scott.emp e;
DEPTNO DNAME ENAME
---------- -------------- ----------
10 ACCOUNTING SMITH
10 ACCOUNTING ALLEN
...
40 OPERATIONS FORD
40 OPERATIONS MILLER
56 Zeilen ausgewählt.
Verwendet man den CROSS APPLY jedoch in Zusammenhang mit einer Inline View, ergibt sich eine wesentlich reizvollere Alternative. Dazu sollen im nächsten Beispiel ausschließlich Zeilen der (linken) Tabelle DEPT zurückkommen, die auch eine Entsprechung innerhalb der Inline View besitzen. Relevant sind nur die Abteilungen RESEARCH, SALES und OPERATIONS.
SQL> SELECT d.deptno, d.dname, i.ename
FROM scott.dept d CROSS APPLY (SELECT * FROM scott.emp e
WHERE e.deptno = d.deptno) i
WHERE d.dname IN ('RESEARCH', 'SALES', 'OPERATIONS');
DEPTNO DNAME ENAME
---------- -------------- -------
20 RESEARCH SMITH
30 SALES ALLEN
30 SALES WARD
20 RESEARCH JONES
30 SALES MARTIN
30 SALES BLAKE
20 RESEARCH SCOTT
30 SALES TURNER
20 RESEARCH ADAMS
30 SALES JAMES
20 RESEARCH FORD
11 Zeilen ausgewählt.
Die Abteilung OPERATIONS kommt nicht zurück, da es über die Inline View keinen Treffer zur Abteilungsnummer 40 gibt.
OUTER APPLY
Dabei handelt es sich um eine Variante des LEFT OUTER JOIN. Alle Zeilen der linken Tabelle sollen ausgegeben werden, unabhängig davon, ob es dazu einen Treffer innerhalb der Inline View gibt oder nicht.
SQL> SELECT d.deptno, d.dname, i.ename
FROM scott.dept d OUTER APPLY (SELECT * FROM scott.emp e
WHERE e.deptno = d.deptno) i
WHERE d.dname IN ('RESEARCH', 'SALES', 'OPERATIONS');
DEPTNO DNAME ENAME
---------- -------------- -------
20 RESEARCH SMITH
30 SALES ALLEN
30 SALES WARD
20 RESEARCH JONES
30 SALES MARTIN
30 SALES BLAKE
20 RESEARCH SCOTT
30 SALES TURNER
20 RESEARCH ADAMS
30 SALES JAMES
20 RESEARCH FORD
40 OPERATIONS
12 Zeilen ausgewählt.
Im Gegensatz zum CROSS APPLY-Beispiel erhält man nun auch die Abteilung 40, auch wenn sie keinen Mitarbeiter besitzt.
LATERAL
LATERAL bietet eine Alternative zum Equi- oder Inner Join. Dabei kann auch hier über eine Inline View ein Bezug zur äußeren (linken) Tabelle der FROM-Klausel hergestellt werden.
SQL> SELECT i.deptno, i.loc, e.ename
FROM scott.emp e, LATERAL (SELECT * FROM scott.dept d
WHERE e.deptno = d.deptno) i;
DEPTNO LOC ENAME
---------- ------------- ----------
20 DALLAS SMITH
30 CHICAGO ALLEN
30 CHICAGO WARD
20 DALLAS JONES
30 CHICAGO MARTIN
30 CHICAGO BLAKE
10 NEW YORK CLARK
20 DALLAS SCOTT
10 NEW YORK KING
30 CHICAGO TURNER
20 DALLAS ADAMS
30 CHICAGO JAMES