Als PDF Downloaden!

Tipps & Tricks: Juli 2015
Bereich:
SQL
Erstellung:
07/2015 MM
Versionsinfo:
getestet auf 11g und 12c
Letzte Überarbeitung:
07/2015 MM
Neuerungen zu Joins in Version 12c
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 (Tipps & Tricks zu Joins ab 9i).
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
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 1 von 6
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;
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 2 von 6
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
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 3 von 6
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
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 4 von 6
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
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 5 von 6
10 NEW YORK
20 DALLAS
10 NEW YORK
30 CHICAGO
20 DALLAS
30 CHICAGO
20 DALLAS
10 NEW YORK
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
14 Zeilen ausgewählt.
Ohne das Schlüsselwort LATERAL erhält man auch in 12c weiterhin eine Fehlermeldung:
SQL> SELECT i.deptno, i.loc, e.ename
FROM scott.emp e, (SELECT * FROM scott.dept d
WHERE e.deptno = d.deptno) i;
WHERE e.deptno = d.deptno) i
*
FEHLER in Zeile 3:
ORA-00904: "E"."DEPTNO": ungültiger Bezeichner
Bei der Vorstellung dieser Neuerungen haben wir versucht, uns auf das Wesentliche zu beschränken. Für
detailliertere Einschränkungen und Anwendungsfälle verweisen wir auf das Handbuch "SQL Reference" in der
Oracle Dokumentation.
Damit sollten Sie wieder auf dem Laufenden sein, was die Möglichkeiten bezüglich Joins in der Version 12c
betrifft.
Falls wir damit Ihr Interesse an weiteren Neuerungen in 12c geweckt haben, besuchen Sie doch einfach unseren
12c New Features Kurs.
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 6 von 6

Download Report