Die WITH-Klausel ein großartiges Werkzeug

Die With-Klausel für Oracle und PostgreSQL Datenbanken

WITH-Klausel, CTE (Common Table Expression) oder auch „Subquery Factoring“ sind Namen einer Technik, welche mit SQL-99 eingefügt wurde. Die WITH-Klauseln arbeiten im Kern so, dass wir SQL-Statements Aliase geben und die Ergebnisse dieser selektieren können. Somit ähneln sie Views – mit dem Unterschied, dass sie nur innerhalb eines SQL-Befehls gültig sind.

Subqueries und WITH-Klauseln werden in erster Linie für denselben Zweck verwendet - nämlich Ergebnisse aus Abfragen als Grundlage für andere Abfragen zu verwenden. Subqueries benötigen weniger Zeichen, solange ihr Ergebnis nicht mehrmals in einer Abfrage benötigt wird. Sie sind syntaktisch simpler gestrickt als WITH-Klauseln. Bei umfangreichen SQL-Statements, welche auf Ergebnissen vieler Abfragen beruhen, sorgen WITH-Klauseln für eine deutlich bessere Gliederung und Übersichtlichkeit. Umfangreiche Abfragen, deren Ergebnisse häufiger als nur einmal benötigt werden, müssen nur einmal geschrieben werden, was die Länge der Abfrage deutlich reduzieren kann.

WITH-Klausel, implementiert mit SQL-99, wurden in der Oracle Datenbank erstmals mit 9i Release 2 eingeführt. Sie sollten dafür sorgen, dass ein Ergebnis einer Teilabfrage, welches häufig verwendet wird nur einmal statt n-mal berechnet werden muss. Dies sollte die Performance steigern. Leider funktionierte es in der Praxis nicht immer, wie es soll. In den 20 Jahren ihrer Existenz in der Oracle Datenbank hat die WITH-Klauseln Features hinzugewonnen. Zum Beispiel können, seit 12c auch Funktionen und Prozeduren in WITH-Klauseln eingebunden werden.

Im Folgenden werden wir die WITH-Klausel sowohl für die Oracle als auch PostgreSQL Datenbank betrachten. Um das Thema mit Leben zu füllen, nutzen wir auf der Oracle Datenbank das Beispielschema SCOTT. Für die PostgreSQL Datenbank erstellen wir ein Pendant mit dem Skript unter folgendem Link:

Syntax der WITH-Klausel

WITH
  <NAME_DER_ABFRAGE_1> AS
    (
     <SELECT STATEMENT>    
     ),
  <NAME_DER_ABFRAGE_2> AS
    (
     <SELECT STATEMENT>
     )
<KERN SELECT STATEMENT>;

Syntaktisch wird die WITH-Klausel mit dem Schlüsselwort „WITH“ eingeleitet, danach folgt ein beliebiger Alias, welchem ein AS und dann in Klammern eine auszuführende Unterabfrage folgt. Nach der ersten Unterabfrage in der WITH-Klausel folgt dann entweder direkt die Kernabfrage oder durch Komma getrennt mit neuem Alias versehen eine oder mehrere weitere Unterabfragen.

WITH
  AVG_SAL AS
    (
    SELECT ROUND(AVG(SAL), 2) AS SAL FROM SCOTT.EMP
    )
SELECT EMPNO, ENAME, SAL
FROM SCOTT.EMP
WHERE SAL > (SELECT SAL FROM AVG_SAL);

In der WITH-Klausel fragen wir das durchschnittliche Gehalt der Mitarbeiter aus der Tabelle EMP aus und selektieren das Ergebnis in der WHERE-Bedingung der Hauptabfrage, um alle Mitarbeiter auszugeben, welche überdurchschnittlich gut verdienen.

Verschachteln von WITH-Abfragen

Unter verschachtelten WITH-Abfragen versteht man, dass innerhalb einer WITH-Klausel weitere WITH-Klauseln verwendet werden. An diesem Punkt unterscheiden sich Oracle und PostgreSQL Datenbanken. Als Beispiel werden wir unser erstes Beispiel weiter verschachteln und schauen wie die beiden RDBMS darauf reagieren.

WITH
  NESTED_WITH AS
    (
    WITH AVG_SAL AS
      (
      SELECT ROUND(AVG(SAL), 2) AS SAL FROM SCOTT.EMP
      )
    SELECT SAL FROM AVG_SAL
    )
SELECT EMPNO, ENAME, SAL
FROM SCOTT.EMP
WHERE SAL > (SELECT SAL FROM AVG_SAL);

Oracle DB

Eine Oracle DB wird uns auf den oberen SELECT immer mit „ORA-32034: Unsupported use of WITH clause“ antworten. Verschachtelte WITH-Klauseln sind also hier nicht unterstützt. Dies ist bedauerlich, da es in einigen Fällen nützlich sein kann, um eine sehr lange und komplexe Abfrage zu strukturieren.

PostgreSQL

Postgres hingegen antwortet uns folgendermaßen „ERROR:  relation "avg_sal" does not exist“. Wie man an der Fehlermeldung erkennen kann, hat PostgreSQL nichts gegen derlei verschachtelte WITH-Klauseln einzuwenden, kann jedoch mit dem Ausdruck „AVG_SAL“ nichts anfangen. Die Erklärung dafür ist, dass sich in Postgres der Gültigkeitsbereich einer WITH-Klausel nur über die Abfrage erstreckt, in welcher sich die jeweilige WITH-Klausel befindet. Da „AVG_SAL“ Teil von „NESTED_WITH“ ist, kann außerhalb dieser Abfrage nicht auf „AVG_SAL“ zugegriffen werden. Andersherum funktioniert es, was uns folgendes Beispiel zeigt.

WITH
  AVG_SAL AS
    (
    SELECT ROUND(AVG(SAL), 2) AS SAL FROM SCOTT.EMP
    ),
  NESTED_WITH AS
    (
    WITH
      SALS_OVER_AVG AS
        (
        SELECT EMPNO, ENAME, SAL
        FROM SCOTT.EMP
        WHERE SAL > (SELECT SAL FROM AVG_SAL)
        )
      SELECT EMPNO, ENAME, SAL FROM SALS_OVER_AVG
    )
SELECT EMPNO, ENAME, SAL FROM NESTED_WITH;

Hier haben PostgreSQL Datenbanken die Nase vorne.

Funktionen und Prozeduren via WITH-Klausel einbinden (Oracle DB)

Wie eingangs erwähnt ist es bei Oracle seit Version 12c exklusiv möglich, Funktionen und Prozeduren in WITH-Klauseln einzubinden. Als Beispiel lassen wir uns alle Mitarbeiter IDs, Namen, Gehälter und einen Kommentar zum Gehalt aus der Tabelle SCOTT.EMP ausgeben. Der Kommentar gibt die Information aus, ob das jeweilige Gehalt über-, unter- oder durchschnittlich ist und wird über eine Funktion bestimmt, welche wir mittels einer WITH-Klausel einbinden.  

WITH FUNCTION IS_AVG(SAL IN NUMBER) RETURN VARCHAR2 IS
  MESSAGE VARCHAR2(20) DEFAULT 'NOTHING';
  AVG_SALL NUMBER(7,2) DEFAULT 0;
  BEGIN
    SELECT ROUND(AVG(SAL),2)
    INTO AVG_SALL
    FROM SCOTT.EMP;  
    IF SAL > AVG_SALL THEN
      MESSAGE := 'OVER_AVG';
    ELSIF SAL = AVG_SALL THEN
      MESSAGE := 'AVG';
    ELSE
      MESSAGE := 'UNDER_AVG';
    END IF;
    RETURN MESSAGE;
  END;
SELECT EMPNO, ENAME, SAL, IS_AVG(SAL) AS IS_AVG
FROM SCOTT.EMP;
/

Die Möglichkeit Funktionen und Prozeduren mit WITH-Klauseln einzubinden, bringt zwei Vorteile mit sich. Erstens die Verwendung von PL/SQL an Stellen, wo dies ohne WITH-Klausel nicht möglich wäre – zum Beispiel als Code für eine View. Zweitens benötigt man kein CREATE PROCEDURE-Recht, um auf diese Weise mit Funktionen und Prozeduren zu arbeiten. Die SELECT-Rechte reichen aus.

WITH-Klauseln und DML (PostgreSQL)

PostgreSQL hat vor über 10 Jahren mit Version 9.1 ein bislang exklusives Feature der WITH-Klausel eingeführt – die Verwendung von DML-Statements innerhalb von WITH-Klauseln. Die Verwendung von DML in WITH-Klauseln ermöglicht es, mehrere DML-Statements zu einem zusammenzufassen. Die RETURNING-Klausel ermöglicht uns den Zugriff auf die geänderten Datensätze. Als Beispiel:

WITH
  EMP_OF_DEPT_30 AS
    (
    DELETE FROM EMP WHERE DEPTNO = 30 RETURNING *
    )
INSERT INTO EMP_30
SELECT * FROM EMP_OF_DEPT_30;

Hier löschen wir mit Hilfe des Statements in der WITH-Klausel alle Datensätze der Tabelle EMP, welche in der Spalte „DEPTNO“ den Wert 30 haben. Das Schlüsselwort „RETURNING“ sorgt dafür, dass der WITH-Klausel die gelöschten Datensätze übergeben werden. Hinter dem Schlüsselwort, um die Datensätze zu übergeben, werden die zurückzugebenden Spalten angegeben. Der Stern hier hat dieselbe Bedeutung wie beim „SELECT“, also alle Spalten. Mit dem Insert-Statement fügen wir die gelöschten Datensätze in die Tabelle EMP_30 ein. Diese muss die entsprechenden Spalten und Datentypen samt Länge besitzen.

Wie schont erwähnt, übergibt die RETURNING-Klausel in einen DELETE Befehl die gelöschten Datensätze, da bleibt noch die Frage, was bei INSERT und UPDATE passiert, welche ebenfalls bei PostgreSQL in den WITH-Klauseln verwendet werden können. Bei einem INSERT werden die neu eingefügten Datensätze übergeben, dies ist z. B. nützlich, um Daten zeitgleich in andere Tabellen einzufügen, Bei Tabellen mit Millionen oder Milliarden von Datensätzen, sollte anschließend trotzdem zusätzlich überprüft werden, ob die gewollten Datensätze auch tatsächlich eingefügt wurden. Beim UPDATE wird der WITH-Klausel die aktualisierten Datensätze übergeben. Das Anwenden einer RETURNING-Klausel auf einen UPDATE Befehl, hat im etwa dieselben Anwendungsgebiete wie bei einen INSERT-Befehl.

Fazit

Die WITH-Klausel ist definitiv eine gute Möglichkeit, längere und komplexe Abfragen zu strukturieren Sie kann mit den hier vorgestellten Funktionen als mächtiges Werkzeug dienen. Es wird häufig behauptet, dass WITH-Klauseln gegenüber Subqueries performanter sind dies ist, aber schlicht falsch. Ob eine WITH-Klausel oder eine Subquery performanter ist, sollte man immer durch Tests prüfen. Subqueries sind für einfache und kurz Abfragen bequemer und haben ihren Platz in SQL verdient.