Die SQL WITH clause (oder Subquery Refactoring)

Die SQL WITH clause (oder Subquery Refactoring) wurde mit der Oracle 9i Release 2 Database eingeführt. Deren Benutzung ist seither Standard, da sie deutliche Vorteile bietet.

Mit der WITH clause wird eine temporäre Tabelle/View erstellt, auf die im Nachhinein zugegriffen werden kann.

Dadurch spart man sich bei komplexen SQL-Statments jede Menge an Code und macht ihn in der Regel auch lesbarer. Die temporäre Tabelle/View wird nur solang innerhalb eines SQL Statements verwendet, bis die Ausführung beendet ist.

Als Beispiel nehmen wir eine Employee und Department Tabelle.

Employees:

Departments:

Wir wollen nun alle Employees anzeigen, deren Standort sich in New York verbindet.

Standardmäßig benutzen wir ein Subselect da sich die Location in der Departments Tabelle befindet.

SELECT *
FROM emp e
WHERE e.DEPTNO in (SELECT d.DEPTNO from dept d where d.loc = 'NEW YORK');

Jetzt benutzen wir die WITH clause und erhalten das gleiche Ergebnis.

With w_dept_ny as
(SELECT /*+ materialize */ d.deptno
FROM dept d
WHERE d.loc = 'NEW YORK')

SELECT *
FROM emp e
join w_dept_ny d on e.deptno = d.deptno;

Seit Oracle 12c ist es möglich PL/SQL Functions und Procedures in eine WITH claus zu packen.

Dazu wird es noch einen gesonderten Blog Eintrag geben.