Muniqsoft Training

Auswahl  

Neuerungen zu Joins in Version 12C 

Oracle
SQL
RDBMS 12.1
25.06.18 (MP)
25.06.18 (MP)
Joins, Oracle Neuerungen, SQL, 12C Release 1

Body

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

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
        20 DALLAS        FORD
        10 NEW YORK      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.

Besuchen Sie uns doch bei einer unsere über 40 Oracle Schulungen in München - Unterhaching.