Komplexere SQL-Abfragen

JOIN - Verbinden von Tabellen

Unter einem Join versteht man das mathematische Mittel der Projektion. Dies bedeutet, dass die Ergebnismenge sich aus Spalten verschiedener Tabellen zusammensetzt. Nehmen wir einmal an, es gebe die Tabellen Kunden, Bestellungen und Produkte. Nun wollen Sie wissen, welcher Kunde (Kundenname) welches Produkt (Produktname) bestellt hat. Da diese Informationen sich in diesem Fall über drei Tabellen verteilen, ist eine Abfrage über alle beteiligten Tabellen notwendig.

Arten

Es gibt zwei Hauptarten:

• EQUIJOIN
• NON-EQUIJOIN

Weiterhin gibt es noch JOIN-Methoden:

• outer join
• self join
• set operators

Beachte den Unterschied zwischen full outer join und cross join (Kreuzprodukt, kartesisches Produkt). Ein cross join entsteht durch Angabe einer zu geringen Anzahl von Join-Bedingungen (mind. Tabellenanzahl-1). Ein full outer join liefert zusätzlich nichtverknüpfte Zeilen aus beiden Tabellen.

EQUIJOIN über zwei Tabellen

Bei einem EQUIJOIN wird auf Gleichheit getestet. Anhand des Beispiels Mitarbeiter (emp) und Abteilung (dept) soll dieser Sachverhalt dargestellt werden. Als Ergebnis wird eine Menge gewünscht, in der in der ersten Spalte der Mitarbeitername (ename aus emp) und in der zweiten Spalte der Abteilungsname (dname aus dept) angezeigt wird. Natürlich soll immer der zu diesem Mitarbeiter gehörende korrekte Abteilungsname angezeigt werden. Schauen wir uns die beiden beteiligten Tabellen etwas genauer an

In der Tabelle emp gibt es eine Spalte deptno, die der Abteilungsnummer entspricht. Diese Spalte entspricht der Spalte deptno in der Tabelle dept. Diese Entsprechung muss innerhalb der Abfrage durch einen entsprechenden Ausdruck in einer Where-Klausel kenntlich gemacht werden (emp.deptno=dept.deptno). Die gewünschte Abfrage hierfür könnte folgendermaßen lauten:

select e.ename, d.dname 
  from emp e, dept d 
  where e.deptno = d.deptno
;
ENAME       DNAME
------------------------
SMITH       RESEARCH
ALLEN       SALES
WARD        SALES
JONES       RESEARCH
MARTIN      SALES
BLAKE       SALES
CLARK       ACCOUNTING
SCOTT       RESEARCH
KING        ACCOUNTING
TURNER      SALES
ADAMS       RESEARCH
JAMES       SALES
FORD        RESEARCH
MILLER      ACCOUNTING

Betrachten wir dies Anweisung etwas genauer. Als erstes fällt auf, dass hier mit Aliasnamen für die Tabellennamen gearbeitet wurde. Das ist vor allem bei Abfragen über mehrere Tabellen bzw. sehr langen Tabellennamen angenehmer und mit weniger Schreibarbeit verbunden. Aliasnamen werden vergeben, indem hinter den Tabellennamen in der From-Klausel einfach der Aliasname geschrieben wird. Da Abfragen nicht 'von links nach rechts' abgearbeitet werden, sondern ein konkreter Ausführungsplan erstellt wird, also die Abfrage als Ganzes ausgeführt wird, können die Aliasnamen überall in der Abfrage verwendet werden. Anstelle von der obigen Abfrage könnten Sie hier auch die folgende Abfrage schreiben.

select ename, dname from emp e, dept d where e.deptno = d.deptno;

schreiben, da die Spaltennamen in der Menge der verbundenen Tabellen eindeutig sind. Das heißt, es gibt in allen an diesem JOIN beteiligten Tabellen nur eine Spalte namens ename und auch nur eine Spalte namens dname. In der Praxis hat es sich jedoch primär aus Gründen der Übersichtlichkeit eingebürgert, auch bei eindeutigen Spaltennamen den jeweiligen Aliasnamen voranzustellen. Die Lesbarkeit wird hierdurch stark gesteigert. Würden Sie keine Aliasnamen für die beteiligten Tabellen verwenden, so könnte die Abfrage folgendermaßen aussehen:

select emp.ename, dept.dname from emp, dept 
  where emp.deptno = dept.deptno
;

Auch hierbei könnten Sie aus den oben beschriebenen Gründen zwischen SELECT und FROM den Tabellennamen weglassen. Schauen wir uns den internen Ablauf der Abfrage etwas ausführlicher an. Zuerst wird der erste Datensatz aus der Tabelle emp extrahiert:

Empno ename ...  deptno
7369  Smith ...  20

Nun wird in der Tabelle dept nach der deptno 20 gesucht. Wird in der Tabelle der Datensatz mit der deptno 20 gefunden, so werden diese beiden Datensätze in der Ergebnismenge verbunden.

Tabelle emp
Tabelle dept
Empno ename ... deptno deptno dname loc
7369 Smith ... 20 20 research dallas

Da deptno der Primärschlüssel der Tabelle dept ist, braucht nicht weiter gesucht zu werden. Als nächsten Schritt wird der zweite Datensatz aus der Tabelle emp extrahiert.

Empno ename ...  deptno
7499 Allan ...  30

Nun wird in der Tabelle dept nach der deptno 30 gesucht. Wird in der Tabelle der Datensatz mit der deptno 30 gefunden, so werden diese beiden Datensätze in der Ergebnismenge verbunden.

Tabelle emp
Tabelle dept
Empno ename ... deptno deptno dname loc
7369 Smith ... 20 20 research dallas
7499 Allan ... 30 30 sales Chicago

Dieser Prozess durchläuft alle Datensätze der Tabelle emp. Wichtig hierbei ist, dass nur Datensätze, die tatsächlich in Beziehung stehen, in der Ergebnismenge auftauchen. In unserem Beispiel führt das dazu, dass die Abteilung mit der Nummer 40 in der Ergebnismenge gar nicht auftaucht, da es hierfür keinen Mitarbeiter gibt. Eine leere Abteilung sozusagen.

EQUIJOIN über mehr als zwei Tabellen

Nehmen wir einmal an, es gäbe eine weitere Tabelle, welche die Dienstwagentypen in Abhängigkeit der Position bzw. des Jobs darstellt.

Als Ergebnis wollen Sie den Namen des Mitarbeiters und der Abteilung, in der er arbeitet und zusätzlich noch den Dienstwagentyp ermitteln. Die Daten der Ergebnismenge erstrecken sich demnach über die drei Tabellen Mitarbeiter (emp), Abteilung (dept) und Dienstwagen (official_car). Die beiden Tabellen Mitarbeiter (emp) und Abteilung (dept) sind über die jeweilige Abteilungsnummer (deptno), die in beiden Tabellen steht, verbunden. Die beiden Tabellen Mitarbeiter (emp) und Dienstwagen (official_car) sind über den Job auf Seiten der Tabelle Mitarbeiter (emp) und über Position auf Seiten der Tabelle official_car. Wir haben folglich zwei WHERE-Kriterien, die beide gelten müssen. Infolgedessen werden diese beiden WHERE-Kriterien durch ein AND verbunden. Die Anweisung könnte folgendermaßen aussehen.

select d.dname,e.ename,e.job,oc.typ 
  from official_car oc,emp e, dept d 
  where oc.position=e.job and e.deptno=d.deptno
;
DNAME       ENAME       JOB         TYP
---------- ---------- ---------- --------------------
RESEARCH    JONES       MANAGER     BMW 7er
ACCOUNTING  CLARK       MANAGER     BMW 7er
SALES       BLAKE       MANAGER     BMW 7er
ACCOUNTING  KING        PRESIDENT   Mercedes SL
SALES       ALLEN       SALESMAN    BMW 3er
SALES       MARTIN      SALESMAN    BMW 3er
SALES       TURNER      SALESMAN    BMW 3er
SALES       WARD        SALESMAN    BMW 3er

Auch hier wird wieder ein Aliasname für die beteiligten Tabellen benutzt. Die Ergebnismenge könnte in unserem Beispiel folgendermaßen aussehen:

Vereinfacht kann man sagen, dass in der Regel eine Abfrage, die auf Daten von X verschiedenen Tabellen zugreift, X-1 verschiedene WHERE-Kriterien vonnöten sind. Zu beachten ist auch hier, dass die Jobs, denen keine Dienstwagen zustehen (Clerk etc.), auch nicht in der Ergebnismenge auftauchen.

Hinweis:
Bei der Anzahl der WHERE-Kriterien ist nicht entscheidend, wie viel Spalten ausgegeben werden. Nehmen wir uns das obere Beispiel. Angenommen, sie wollen lediglich wissen, welche Dienstwagen in welchen Abteilungen zur Verfügung stehen, nicht jedoch die Mitarbeiter. Auch in diesem Fall ist ein Join über alle drei Tabellen notwendig, um die richtige Ergebnismenge zu erhalten. Zwischen der Tabelle Abteilung und Dienstwagen allein gibt es kein zutreffendes WHERE-Kriterium. Somit würde auch in diesem Falle die Abfrage so aussehen:

select d.dname,oc.typ 
  from official_car oc,emp e, dept d 
  where oc.position = e.job and e.deptno = d.deptno
;

Der lieben Ordnung wegen wäre hier noch, um Duplikate bei der Ausgabe auszuschließen, ein DISTINCT das Mittel der Wahl:

select DISTINCT d.dname,oc.typ 
  from official_car oc,emp e, dept d 
  where oc.position = e.job and e.deptno = d.deptno
;
DNAME       TYP
-------------------------
ACCOUNTING  BMW 7er
ACCOUNTING  Mercedes SL
RESEARCH    BMW 7er
SALES       BMW 3er
SALES       BMW 7er

Non-Equijoins

Wenn keine Spalte einer Tabelle mit einer Spalte einer anderen Tabelle direkt korrespondiert, dann ist nur ein Non-Equijoin möglich.

OUTER JOIN

Bei einem OUTER JOIN werden nicht nur die Datensätze angezeigt, die in direkter Beziehung zueinander stehen, sondern auch Datensätze, die keinen direkten Bezug zu Datensätzen der anderen Tabelle haben. Als Beispiel betrachten wir die Tabellen Mitarbeiter (emp) und Abteilung (dept). Wie wir im vorigen Beispiel gesehen haben, gibt es keinen Mitarbeiter, der zur Abteilung Nummer 40 gehört. Dieser wird daher auch bei einem normalen JOIN nicht in der Ergebnismenge angezeigt. Es kann jedoch durchaus sein, dass Sie auch die Abteilung / Abteilungen in der Ergebnismenge sehen wollen, die keine direkte Beziehung zu einem Mitarbeiter haben, also die zur Zeit leeren Abteilungen. Genau hier kommt der OUTER JOIN zum Einsatz.

select e.ename,d.dname 
  from emp e,dept d 
  where e.deptno(+) = d.deptno
;
ENAME       DNAME
----------  ----------
CLARK       ACCOUNTING
KING        ACCOUNTING
MILLER      ACCOUNTING
SMITH       RESEARCH
ADAMS       RESEARCH
FORD        RESEARCH
SCOTT       RESEARCH
JONES       RESEARCH
ALLEN       SALES
BLAKE       SALES
MARTIN      SALES
JAMES       SALES
TURNER      SALES
WARD        SALES
            OPERATIONS

Durch das PLUS-Zeichen wird erreicht, dass alle Datensätze der dem PLUS gegenüberliegenden Tabelle in die Ergebnismenge aufgenommen werden, egal, ob es eine direkte Verbindung gibt oder nicht. Im Ergebnis können Sie hier erkennen, dass die Abteilung OPERATIONS keine Mitarbeiter hat. Würde sich das PLUS-Zeichen an der gegenüberliegenden Seite befinden, so würden auch die Mitarbeiter angezeigt werden, die zu keiner Abteilung gehören.

select e.ename,d.dname 
  from emp e,dept d 
  where e.deptno = d.deptno(+)
;

Mitarbeiter, die zu keiner Abteilung gehören, gibt es in unserem Beispiel jedoch nicht.

OUTER JOIN Anwendungen

Angenommen, Sie wollen herausfinden, in zu welcher Abteilung keine Mitarbeiter gehören. Es sollten jedoch nur diese Datensätze in der Ergebnismenge erscheinen. Folgende Abfrage würde das gewünschte Resultat bringen:

select e.ename,d.dname from emp e,dept d 
  where e.deptno(+) = d.deptno AND 
        e.empno IS NULL
;
ENAME     DNAME
--------------------
          OPERATIONS

Wenn wir uns die Abfrage etwas genauer anschauen, so erkennen wir, dass zuerst alle Datensätze aus der Tabelle Abteilung (dept) in die Ergebnismenge aufgenommen werden, egal, ob es eine direkte Verbindung zu Mitarbeitern gibt oder nicht.

ENAME       DNAME
------------------------
CLARK       ACCOUNTING
KING        ACCOUNTING
MILLER      ACCOUNTING
SMITH       RESEARCH
ADAMS       RESEARCH
FORD        RESEARCH
SCOTT       RESEARCH
JONES       RESEARCH
ALLEN       SALES
BLAKE       SALES
MARTIN      SALES
JAMES       SALES
TURNER      SALES
WARD        SALES
NULL        OPERATIONS

Für die Abteilungen, die keine Verbindung zu einem Mitarbeiter haben, sind alle dazugehörigen Spalten der Tabelle Mitarbeiter (emp) NULL. Somit müssen wir lediglich noch nach IS NULL bei den geeigneten Spalten der Tabelle Mitarbeiter (emp) einschränken. Als geeignete Spalten gelten alle Spalten, die nicht NULL sein können (als NOT NULL definiert wurden, siehe spätere Kapitel).

Kreuzprodukt

Unter einem Kreuzprodukt, auch Cartesian Product genannt, versteht man das Verbinden mehrerer Tabellen ohne Verbindungskriterium. Obgleich dies in den meisten Fällen 'sinnlos' erscheint, wollen wir hier ein kleines Beispiel vorstellen. Angenommen, sie veranstalten eine Schachrunde, in der jeder Mitarbeiter gegen jeden antreten muss. Folgende Abfrage, mit Hilfe eines Kreuzproduktes oder im englischen CROSS JOIN, würde die Partien ermitteln:

select e1.ename,e2.ename from emp e1, emp e2;

Diese Abfrage würde jeden Namen aus der Tabelle e1 (entspricht emp) mit jedem Namen aus der Tabelle e2(entspricht ebenfalls emp) verbinden und in der Ergebnismenge aufnehmen. Da die Tabelle emp 14 Datensätze hat, werden in der Ergebnismenge 14 * 14 Datensätze erscheinen, also 196. Da die Mitarbeiter schlecht gegen sich selbst spielen können, muss dieser Fakt noch ausgeschlossen werden.

select e1.ename,e2.ename from emp e1, emp e2 
  where e1.ename <> e2.ename
;

Folglich sollten 196 weniger 14 Datensätze in der Ergebnismenge erscheinen. Jeder gegen jeden - mit Hin- und Rückrunde!

ACHTUNG:
Oftmals benutzt man auch versehentlich ein Kreuzprodukt - wenn Sie zum Beispiel vergessen haben, ein WHERE-Kriterium zu definieren und es mehr als eine Tabelle hinter FROM gibt. Sollten Sie zwei Tabellen mit beispielsweise 10000 Datensätzen verbinden (Kreuzprodukt), so hat die Ergebnismenge 10000 * 10000 Datensätze, also 100 Millionen. Übrigens auch ein gutes Mittel zum Erzeugen von großen Datenbeständen!

SELF JOIN

Unter einem SELF JOIN versteht man eine Verbindung einer Tabelle mit sich selbst. Dieser Join ist wohl der am schwersten zu verstehende JOIN, da eine Tabelle mit sich selbst verknüpft ist.

Um diesen SELF JOIN verständlich zu machen, benutzen wir das Beispiel der Tabelle Mitarbeiter (emp). In der Tabelle Mitarbeiter (emp) hat jeder Mitarbeiter eine Mitarbeiternummer empno und daneben noch die Mitarbeiternummer des Vorgesetzten (mgr).

Schauen wir uns hierfür die Tabelle etwas genauer an. Herr Smith hat die Mitarbeiternummer(empno) 7369. Sein Vorgesetzter ist der Herr mit der Mitarbeiternummer 7902 (entspricht der Spalte MGR), also der Herr Ford. Herr ALLAN hat die Mitarbeiternummer(empno) 7499. Sein Vorgesetzter ist der Herr mit der Mitarbeiternummer 7698 (entspricht der Spalte MGR), also der Herr Blake. Auf diese Weise wird die gesamte Hierarchie der Firma abgebildet. Soll nun in einer Abfrage ermittelt werden, welcher Mitarbeiter welchen Vorgesetzten (jeweils den Namen) hat, so ist die eine Verbindung der Spalten Mitarbeiternummer (empno) und der Spalte Manager (mgr) ein und derselben Tabelle:

select mitarbeiter.ename as "Mitarbeiter",
       vorgesetzter.ename as "Vorgesetzter" 
  from 
       emp mitarbeiter,
       emp vorgesetzter 
  where mitarbeiter.mgr = vorgesetzter.empno
;
Mitarbeiter Vorgesetzt
-----------------------
SMITH       FORD
ALLEN       BLAKE
WARD        BLAKE
JONES       KING
MARTIN      BLAKE
BLAKE       KING
CLARK       KING
SCOTT       JONES
TURNER      BLAKE
ADAMS       SCOTT
JAMES       BLAKE
FORD        JONES
MILLER      CLARK

Die Ergebnismenge würde folgendermaßen aussehen: Das einzige Manko ist, dass der Chef des Ganzen natürlich keine MGR hat - und demnach auch nicht angezeigt wird. Aber auch hier kann man sich leicht helfen. Wir nutzen einfach das Konzept der Outer Joins.

SELECT mitarbeiter.ename  AS "Mitarbeiter", 
       vorgesetzter.ename AS "Vorgesetzter" 
   FROM emp Mitarbeiter, emp vorgesetzter 
   WHERE mitarbeiter.mgr = vorgesetzter.empno(+)
;
Mitarbeiter  Vorgesetzter
----------  ----------
SMITH       FORD
ALLEN       BLAKE
WARD        BLAKE
JONES       KING
MARTIN      BLAKE
BLAKE       KING
CLARK       KING
SCOTT       JONES
KING
TURNER      BLAKE
ADAMS       SCOTT
JAMES       BLAKE
FORD        JONES
MILLER      CLARK

Hierdurch wird erreicht, dass alle Mitarbeiter angezeigt werden, egal, ob diese auch einen Manager (Vorgesetzten) haben. Die Ergebnismenge würde nunmehr folgendermaßen ausschauen:

Mengenoperationen

MINUS

Von der ersten Menge wird die zweite Menge abgezogen. MINUS verwendet DISTINCT.

select field1, field2, ... field_n from tables
  MINUS
select field1, field2, ... field_n from tables;

select d.deptno from scott.dept d
  minus
select e.deptno from scott.emp e;

INTERSECT

INTERSECT ergibt die Überschneidungsmenge von Elementen, die zu beiden Mengen gehören.

select field1, field2, ... field_n from tables
  INTERSECT
select field1, field2, ... field_n from tables;

select distinct city from customers   
  intersect  
select distinct city from suppliers;

UNION

Ergibt die Gesamtmenge beider Mengen. UNION verwendet DISTINCT.

select field1, field2, ... field_n from tables
  UNION
select field1, field2, ... field_n from tables;

select supplier_id from suppliers
  UNION
select supplier_id from orders;

UNION ALL

Ergibt die Gesamtmenge beider Mengen. Im Gegensatz zu UNION wird kein DISTINCT verwendet.

select field1, field2, ... field_n from tables
  UNION ALL
select field1, field2, ... field_n from tables;

select supplier_id from suppliers
  UNION ALL
select supplier_id from orders;

Übungen 04

Übungen siehe Seite .

Gruppierungen

Oftmals sollen nicht einzelne Werte zurückgegeben werden, sondern Berechnungen über Gruppen von Werten hinweg.

SUM()

SELECT sum(sal) AS "Summe der Gehälter" FROM emp;
Summe der Gehälter
------------------
             29025

Ermittelt wird die Summe der Gehälter aller Mitarbeiter.

MIN()

select min(sal) AS "Kleinstes Gehalt" from emp;
Kleinstes Gehalt
----------------
             800

Ermittelt wird das kleinste Gehalt aller Mitarbeiter.

MAX()

SELECT max(sal) AS "Größtes Gehalt" FROM emp;
Größtes Gehalt
--------------
         5000

Ermittelt wird das größte Gehalt aller Mitarbeiter.

AVG()

SELECT avg(sal) AS "Durchschnittsgehalt" FROM emp;
Durchschnittsgehalt
-------------------
         2073,21429

Ermittelt wird das Durchschnittsgehalt aller Mitarbeiter.

COUNT()

SELECT COUNT(EMPNO) AS "Mitarbeiteranzahl" FROM emp;
MITARBEITERANZAHL
-----------------
               14

Ermittelt wird die Anzahl der Einträge in der Spalte empno. Es wird also die Mitarbeiteranzahl ermittelt. Zu beachten ist hierbei, dass als COUNT-Kriterium eine Spalte genommen werden sollte, die nicht leer sein kann. Wird als COUNT-Kriterium eine NULL-Spalte benutzt, wird diese nicht mitgezählt. Wenn diese Abfrage z.B. wie folgt wäre, so wäre das Ergebnis lediglich 4:

SELECT COUNT(comm) AS "Mitarbeiteranzahl" FROM emp;
MITARBEITERANZAHL
-----------------
                4

In 10 Datensätzen steht nämlich NULL in der Spalte comm.

GROUP BY - Gruppen bilden

Mit der SQL-Anweisung GROUP BY werden Datensätze zu Gruppen zusammengefasst. Die Gruppenfunktionen gelten dann lediglich für die einzelnen Gruppen. Eine Gruppe ist durch die Identität der Inhalte gekennzeichnet. Das bedeutet, wenn nach der Abteilungsnummer deptno in der Tabelle Mitarbeiter (emp) mit GROUP BY gruppiert wird, so werden jeweils Gruppen mit den gleichen Abteilungsnummern gebildet und die entsprechende Gruppenfunktion auf die gebildeten Gruppen angewendet.

SELECT deptno,avg(sal) as "Durchschnittsgehalt je Abt" 
  FROM EMP group by deptno
;
DEPTNO  Durchschnittsgehalt je Abt
----------------------------------
10              2916,66667
20              2175
30              1566,66667

Durch die Gruppierung GROUP BY deptno werden jeweils Gruppen mit identischen Abteilungsnummern gebildet. Über diese Gruppen wird nun die Funktion ausgeführt, also der Durchschnitt der Gehälter ermittelt.

HAVING - Einschränkungen nach Gruppenfunktionen

Häufig sollen nicht alle Gruppen ausgegeben werden. Wenn Sie nach Gruppenfunktionen einschränken wollen, so benötigen Sie die Anweisung HAVING.

SELECT deptno,avg(sal) as "Durchschnittsgehalt je Abt" 
  FROM EMP group by deptno 
    HAVING avg(sal) > 2000
;
DEPTNO  Durchschnittsgehalt je Abt
------------------------------------
   10              2916,66667
   20               2175

Auch hier wird das Durchschnittsgehalt je Abteilung ermittelt. Jedoch nur die Durchschnittsgehälter, die über 2000 liegen, werden ausgegeben.

Besonderheiten bei NULL und AVG

Die Funktion AVG zieht in die Berechnung des Durchschnittes lediglich die Zeilen ein, die nicht NULL in der entsprechenden Spalte sind. Dies führt zu folgendem Ergebnis:

select avg(comm) from emp;
AVG(COMM)
---------
   550

Es gibt jedoch nur 4 Datensätze, in denen die Spalte Comm nicht NULL ist. In diesen Datensätzen ist die Spalte Comm 300, 500, 1400 und 0. Das Ergebnis von 550 ergibt sich demnach aus (300+500+1400+0) / 4. Sollen jedoch auch die Datensätze einbezogen werden, in denen Comm NULL ist, so können Sie diese mit der Funktion NVL durch eine 0 ersetzen lassen.

select avg(nvl(comm,0)) from emp;
AVG(NVL(COMM,0))
----------------
   157,142857

Durch die Funktion NVL(comm,0) werden NULL-Werte in der Spalte comm als 0 interpretiert. Demnach wird nun (300+500+1400+0+0+0+0+0+0+0+0+0+0+0) / 14 gerechnet.

Übungen 03

Übungen siehe Seite .

Unterabfragen

Häufig wollen Sie nicht das geringste Gehalt als Ergebnismenge, sondern den oder die Mitarbeiter, die das geringste Gehalt erhalten. Hierfür leistet eine Unterabfrage gute Dienste.

select ename from emp where sal = 
  (
    select min(sal) from emp
  )
;

In der in Klammern eingeschlossenen Unterabfrage wird zuerst das geringste Gehalt ermittelt (z.B. 800). Nun wird für die Unterabfrage der ermittelte Wert eingesetzt. Somit lautet die Abfrage:

select ename from emp where sal = 800;
ENAME
------
SMITH

Hierdurch wird der Name des oder der Mitarbeiter zurückgegeben, der diese 800 verdient - die natürlich wiederum dem geringsten Gehalt entsprechen.

Inline View

Inline Views sind Unterabfragen nach FROM.

select  a.last_name, a.salary, a.department_id, b.maxsal
    FROM employees a,
      (
        SELECT department_id,max(salary) maxsal 
          FROM employees 
          GROUP BY department_id
      ) b
      WHERE a.department_id = department_id
        AND a_salary < b.maxsal
;

IN

Gibt die Unterabfrage mehr als einen Wert zurück, so können Sie natürlich nicht auf Gleichheit oder Ungleichheit testen. Hier müssen Sie mit entsprechenden Operatoren, wie IN arbeiten.

select distinct d.dname from dept d 
  where d.deptno in 
   (
     select distinct deptno from emp 
       where job = 'SALESMAN' or job = 'MANAGER'
   )
;
DNAME
--------------
ACCOUNTING
RESEARCH
SALES

In der Unterabfrage werden die Abteilungsnummern ermittelt, in denen Mitarbeiter der Positionen SALESMAN oder MANAGER arbeiten. In der äußeren Abfrage werden hierzu die Abteilungsnamen ausgegeben. Würden Sie hier anstelle des IN ein = benutzen, so würde ein Fehler ausgegeben werden:

FEHLER in Zeile 1: 
ORA-01427: Unterabfrage für eine Zeile liefert mehr als eine Zeile

ANY

Einer der Datensätze muss der Bedingung genügen.

select * from employees where employeeid > any
  (select employeeid from orders)
;

ALL

Alle Datensätze müssen der Bedingung genügen (AND).

select * from employees where employeeid > all
  (select employeeid from orders)
;

Top-N Analyse

select ROWNUM AS Rank, Name, Region, Sales from
  (
    select Name, Region, sum(Sales) AS Sales from Sales
   GROUP BY Name, Region
   order by sum(Sales) DESC
  )
  WHERE ROWNUM <= 10
;

Welche drei Mitarbeiter verdienen am besten?

select * from
  (
    select * from scott.emp order by SAL desc
  )
  where ROWNUM < 4
;

Übungen 05

Übungen siehe Seite .