Komplexe Abfragen

m_nr	m_name	m_vorname	abt_nr	abt_nr	abt_name	stadt
18316	Mueller	Gabriele	a1	a1	Beratung	Muenchen
28559	Mozer	Sibille	a1	a1	Beratung	Muenchen
2581	Kaufmann	Brigitte	a2	a2	Diagnose	Muenchen
9031	Meier	Rainer	a2	a2	Diagnose	Muenchen
29346	Probst	Andreas	a2	a2	Diagnose	Muenchen
10102	Huber	Petra	a3	a3	Freigabe	Stuttgart
25348	Keller	Hans	a3	a3	Freigabe	Stuttgart

(7 rows)

Die Projektion in Beispiel 8.1 umfaßt alle Spalten beider

Tabellen. Das ist ein Merkmal von Equijoin. Die äquivalente

Schreibweise für die Projektion in einem Equijoin wäre die

Verwendung des Zeichens "*". Die FROM-Klausel definiert

die Tabellen, die verknüpft werden; die WHERE-Klausel gibt

die jeweiligen Spalten, die miteinander verglichen werden,

an. Diese Spalten heißen Joinspalten. Sie müssen denselben

Datentyp haben.

Wir wollen nun zeigen, wie das Ergebnis eines Joins entsteht.

Diese Darstellung beschreibt nur ein gedankliches Konzept

und nicht unbedingt die Arbeitsweise des INGRES-Systems.

Das Konzept wird praktisch mit Beispiel 8.1 erklärt.

Zunächst werden alle Reihen der Tabelle mitarbeiter mit

jeder einzelnen Reihe der Tabelle abteilung verkettet.

Dadurch entsteht eine Tabelle mit insgesamt 21 Reihen, die

folgendermaßen aussieht:

m_nr	m_name	m_vorname	abt_nr	abt_nr	abt_name	stadt
-25348	Keller	Hans	a3	a1	Beratung	Muenchen
-10102	Huber	Petra	a3	a1	Beratung	Muenchen
18316	Mueller	Gabriele	a1	a1	Beratung	Muenchen
-29346	Probst	Andreas	a2	a1	Beratung	Muenchen
- 9031	Meier	Rainer	a2	a1	Beratung	Muenchen
- 2581	Kaufmann	Brigitte	a2	a1	Beratung	Muenchen
28559	Mozer	Sibille	a1	a1	Beratung	Muenchen
-25348	Keller	Hans	a3	a2	Diagnose	Muenchen
-10102	Huber	Petra	a3	a2	Diagnose	Muenchen
-18316	Mueller	Gabriele	a1	a2	Diagnose	Muenchen
29346	Probst	Andreas	a2	a2	Diagnose	Muenchen
9031	Meier	Rainer	a2	a2	Diagnose	Muenchen
2581	Kaufmann	Brigitte	a2	a2	Diagnose	Muenchen
-28559	Mozer	Sibille	a1	a2	Diagnose	Muenchen
25348	Keller	Hans	a3	a3	Freigabe	Stuttgart
10102	Huber	Petra	a3	a3	Freigabe	Stuttgart
-18316	Mueller	Gabriele	a1	a3	Freigabe	Stuttgart
-29346	Probst	Andreas	a2	a3	Freigabe	Stuttgart
-9031	Meier	Rainer	a2	a3	Freigabe	Stuttgart
-2581	Kaufmann	Brigitte	a2	a3	Freigabe	Stuttgart
-28559	Mozer	Sibille	a1	a3	Freigabe	Stuttgart

(21 rows)

Abb. 8-1 Kartesisches Produkt der Tabellen mitarbeiter und abteilung

Danach werden die Reihen entfernt, die die Bedingung

mitarbeiter.abt_nr = abteilung.abt_nr

in der WHERE-Klausel nicht erfüllen. Diese Reihen sind mit

dem Zeichen "-" in Abbildung 8-1 gekennzeichnet. Die übrigen

Reihen stellen das Ergebnis des Beispiels 8.1 dar.

Die Spaltenpaare, die in einem Vergleich beim Join

erscheinen, unterliegen in der Praxis einer weiteren

Bedingung, nämlich der, daß die sinnvolle Verknüpfung zweier

Tabellen nur über Spaltenpaare durchgeführt wird, die

dieselbe logische Bedeutung in der Anwendung haben.

In der Beispieldatenbank existieren insgesamt drei solche

Spaltenpaare. Die Tabellen abteilung und mitarbeiter

lassen sich durch die Joinspalten abteilung.abt_nr und

mitarbeiter.abt_nr verbinden. Genauso lassen sich die

Tabellen mitarbeiter und arbeiten durch die Joinspalten

mitarbeiter.m_nr und arbeiten.m_nr und die Tabellen projekt

und arbeiten durch die Joinspalten projekt.pr_nr und

arbeiten.pr_nr verbinden.

Wie man hieraus erkennt, hat jedes Spaltenpaar der

Beispieldatenbank denselben Namen, was im allgemeinen

nicht der Fall sein muß. Die gekennzeichneten Namen

in der WHERE-Klausel, wie z.B. mitarbeiter.abt_nr und

abteilung.abt_nr, sind unbedingt anzugeben, falls die

Spaltennamen in der SELECT-Anweisung nicht eindeutig sind.

Zusätzlich zu Bedingungen mit Joinspalten können weitere

Bedingungen in der WHERE-Klausel existieren.

Beispiel 8.2

Finden Sie alle Daten der Mitarbeiter, die im Projekt Gemini

arbeiten.

SELECT *

FROM arbeiten, projekt

WHERE arbeiten.pr_nr = projekt.pr_nr

AND pr_name = 'Gemini';

Das Ergebnis ist

m_nr	pr_nr	aufgabe	einst_dat	pr_nr	pr_name	mittel
25348	p2	Sachbearbeiter	15-feb-1988	p2	Gemini	95000.000
18316	p2		01-jun-1989	p2	Gemini	95000.000
29346	p2		15-dec-1987	p2	Gemini	95000.000
28559	p2	Sachbearbeiter	01-feb-1989	p2	Gemini	95000.000

(4 rows)

Für die Spalte pr_name ist es nicht unbedingt notwendig, den

gekennzeichneten Namen anzugeben, weil dieser Spaltenname in

Beispiel 8.2 eindeutig ist.

Zurück zum Inhaltsverzeichnis

8.1.2 Das Kartesische Produkt

Im vorherigen Abschnitt haben wir die Entstehung des

Ergebnisses in Beispiel 8.1 mit Hilfe eines Konzeptes

erklärt. Im ersten Schritt dieses Konzeptes sind alle

Reihen der Tabelle mitarbeiter mit jeder einzelnen Reihe

der Tabelle abteilung verkettet worden. Das so entstandene

Zwischenergebnis heißt Kartesisches Produkt.

Beispiel 8.3

SELECT *

FROM mitarbeiter, abteilung;

Das Ergebnis ist in Abbildung 8-1 dargestellt.

Die Ergebnistabelle des Kartesischen Produktes enthält so

viele Reihen wie das Produkt der Reihenanzahl der ersten und

zweiten Tabelle. In Beispiel 8.3 lautet das Ergebnis 7x3=21

Reihen.

Das folgende Beispiel stellt das Kartesische Produkt zweier

Tabellen dar, in dem die Bedingung in der WHERE-Klausel

existiert, das aber nicht den Vergleich zwischen Joinspalten

enthält.

Beispiel 8.4

SELECT *

FROM arbeiten, projekt

WHERE arbeiten.pr_nr = 'p3';

Das Ergebnis ist:

m_nr	pr_nr	aufgabe	einst_dat	pr_nr	pr_name	mittel
10102	p3	Gruppenleiter	01-jan-1989	p1	Apollo	120000.000
10102	p3	Gruppenleiter	01-jan-1989	p2	Gemini	95000.000
10102	p3	Gruppenleiter	01-jan-1989	p3	Merkur	186500.000
2581	p3	Projektleiter	15-oct-1989	p1	Apollo	120000.000
2581	p3	Projektleiter	15-oct-1989	p2	Gemini	95000.000
2581	p3	Projektleiter	15-oct-1989	p3	Merkur	186500.000
9031	p3	Sachbearbeiter	15-nov-1988	p1	Apollo	120000.000
9031	p3	Sachbearbeiter	15-nov-1988	p2	Gemini	95000.000
9031	p3	Sachbearbeiter	15-nov-1988	p3	Merkur	186500.000

(9 rows)

In der Praxis wird das Kartesische Produkt äußerst selten

bewußt benutzt. Manchmal kommt es vor, daß der Anwender

das Kartesische Produkt unbewußt erzeugt, wenn er vergißt,

den Vergleich zwischen den Joinspalten in der WHERE-Klausel

anzugeben. Dieses Ergebnis entspricht dann nicht dem

tatsächlichen Resultat, das der Anwender erhalten wollte.

Zurück zum Inhaltsverzeichnis

8.1.3 Der Natürliche Join

Der natürliche Join entsteht aus einem Equijoin, wenn die

doppelte Spalte aus der Projektion entfernt wird. Der

Equijoin in Beispiel 8.1 kann auf folgende Weise in einen

natürlichen Join umgewandelt werden:

Beispiel 8.5

SELECT mitarbeiter.*, abt_name, stadt

FROM mitarbeiter, abteilung

WHERE mitarbeiter.abt_nr = abteilung.abt_nr;

Das Ergebnis ist:

m_nr	m_name	m_vorname	abt_nr	abt_name	stadt
18316	Mueller	Gabriele	a1	Beratung	Muenchen
28559	Mozer	Sibille	a1	Beratung	Muenchen
2581	Kaufmann	Brigitte	a2	Diagnose	Muenchen
9031	Meier	Rainer	a2	Diagnose	Muenchen
29346	Probst	Andreas	a2	Diagnose	Muenchen
10102	Huber	Petra	a3	Freigabe	Stuttgart
25348	Keller	Hans	a3	Freigabe	Stuttgart

(7 rows)

Die Projektion in Beispiel 8.5 beinhaltet alle Spalten der

ersten Tabelle und jene Spalten der zweiten Tabelle, die

nicht die Joinspalte darstellen. Die Spalten der zweiten

Tabelle abt_name und stadt sind in der Projektion mit nicht

gekennzeichneten Namen, d.h. ohne Tabellennamen angegeben.

Dies ist möglich, weil die beiden Namen in der Tabelle

eindeutig sind.

Der natürliche Join wird in der Praxis von allen Joinarten am

häufigsten angewendet. Deswegen impliziert das Kürzel "Join"

immer einen natürlichen Join.

Die Projektion eines natürlichen Joins muß nicht unbedingt

alle unterschiedlichen Spalten beider Tabellen beinhalten,

was auch das folgende Beispiel zeigt.

Beispiel 8.6

Nennen Sie die Abteilungsnummern aller Mitarbeiter, die am

15.10.1989 eingestellt wurden.

SELECT abt_nr

FROM mitarbeiter, arbeiten

WHERE mitarbeiter.m_nr = arbeiten.m_nr

AND einst_dat = '15-oct-1989';

Das Ergebnis ist:

abt_nr

(1 row)

Beispiel 8.6 ist identisch mit dem Beispiel 7.21, nur werden

beide auf unterschiedliche Weise gelöst. Wie aus beiden

Beispielen ersichtlich, ist die Lösung mit Join einfacher

als die Lösung mittels einer Unterabfrage. Zusätzlich

dazu hat die Unterabfrage mit dem Gleichheitszeichen eine

Einschränkung: Die Unterabfrage darf nur eine Reihe als

Ergebnis liefern. Diese Einschränkung gilt nicht für den

Join.

Zurück zum Inhaltsverzeichnis

8.1.4 Der Thetajoin

Der Thetajoin kennzeichnet jene SELECT-Anweisung, bei

der die Joinspalten in der WHERE-Klausel mit einem der

Vergleichsoperatoren verglichen werden. Die allgemeine Form

eines Thetajoins sieht folgendermaßen aus:

SELECT tabelle_1.spalten, tabelle_2.spalten

FROM tabelle_1, tabelle_2

WHERE join_spalte_1 Ý join_spalte_2;

wobei "Ý" einen beliebigen Vergleichsoperator darstellt.

Jeder Equijoin ist gleichzeitig auch ein Thetajoin, wenn man

für "Ý" das Gleichheitszeichen benutzt.

Im folgenden Beispiel wird die Tabelle mit_erweiter

verwendet.

Beispiel 8.7

SELECT mit_erweiter.*, abteilung.*

FROM mit_erweiter, abteilung

WHERE wohnort > stadt;

Das Ergebnis ist:

m_nr	m_name	m_vorname	abt_nr	wohnort	abt_nr	abt_name	stadt
18316	Mueller	Gabriele	a1	Rosenheim	a1	Beratung	Muenchen
18316	Mueller	Gabriele	a1	Rosenheim	a2	Diagnose	Muenchen
28559	Mozer	Sibille	a1	Ulm	a1	Beratung	Muenchen
28559	Mozer	Sibille	a1	Ulm	a2	Diagnose	Muenchen
28559	Mozer	Sibille	a1	Ulm	a3	Freigabe	Stuttgart

(5 rows)

In Beispiel 8.7 wurden die Spalten wohnort und stadt

verglichen und alle Mitarbeiter- und Abteilungsdaten

ausgewählt; der Wohnort des Mitarbeiters folgt jeweils

alphabetisch nach dem Standort der Abteilung.

Die Verwendung der Form des Thetajoins mit einem anderen

Vergleichsoperator als dem Gleichheitszeichen kommt in der

Praxis nicht oft vor. Am häufigsten wird noch der Vergleich

auf die Ungleichheit durchgeführt.

Beispiel 8.8

Finden Sie alle Mitarbeiter- und Abteilungsdaten, bei

denen die Abteilungsnummer in den beiden Tabellen nicht

übereinstimmt.

SELECT *

FROM mitarbeiter, abteilung

WHERE mitarbeiter.abt_nr <> abteilung.abt_nr;

Das Ergebnis ist:

m_nr	m_name	m_vorname	abt_nr	abt_nr	abt_name	stadt
25348	Keller	Hans	a3	a1	Beratung	Muenchen
10102	Huber	Petra	a3	a1	Beratung	Muenchen
29346	Probst	Andreas	a2	a1	Beratung	Muenchen
9031	Meier	Rainer	a2	a1	Beratung	Muenchen
2581	Kaufmann	Brigitte	a2	a1	Beratung	Muenchen
25348	Keller	Hans	a3	a2	Diagnose	Muenchen
10102	Huber	Petra	a3	a2	Diagnose	Muenchen
18316	Mueller	Gabriele	a1	a2	Diagnose	Muenchen
28559	Mozer	Sibille	a1	a2	Diagnose	Muenchen
18316	Mueller	Gabriele	a1	a3	Freigabe	Stuttgart
29346	Probst	Andreas	a2	a3	Freigabe	Stuttgart
9031	Meier	Rainer	a2	a3	Freigabe	Stuttgart
2581	Kaufmann	Brigitte	a2	a3	Freigabe	Stuttgart
28559	Mozer	Sibille	a1	a3	Freigabe	Stuttgart

(14 rows)

Zurück zum Inhaltsverzeichnis

8.1.5 Verknüpfung von mehr als zwei Tabellen

Die Anzahl der Tabellen, die miteinander verknüpft sein

können, ist theoretisch unbegrenzt. Trotzdem hat das

INGRES-System eine implementierungsbedingte Einschränkung,

die die Anzahl der möglichen Joins begrenzt hält.

Beispiel 8.9

Nennen Sie Namen und Vornamen aller Projektleiter, deren

Abteilung den Standort Stuttgart hat.

SELECT m_name, m_vorname

FROM arbeiten, mitarbeiter, abteilung

WHERE arbeiten.m_nr = mitarbeiter.m_nr

AND mitarbeiter.abt_nr = abteilung.abt_nr

AND aufgabe = 'Projektleiter'

AND stadt = 'Stuttgart';

Das Ergebnis ist:

m_name	m_vorname
Huber	Petra

(1 row)

In Beispiel 8.9 müssen drei Tabellen, nämlich arbeiten,

mitarbeiter und abteilung miteinander verknüpft werden, damit

die notwendige Information mittels einer SELECT-Anweisung

ausgewählt wird. Diese drei Tabellen werden mit Hilfe von

zwei Paaren von Joinspalten verknüpft:

(arbeiten.m_nr, mitarbeiter.m_nr)

(mitarbeiter.abt_nr, abteilung.abt_nr)

Im folgenden Beispiel werden alle vier Tabellen der

Beispieldatenbank miteinander verknüpft.

Beispiel 8.10

Nennen Sie die Namen der Projekte, in denen die Mitarbeiter

arbeiten, die zur Abteilung Diagnose gehören.

SELECT DISTINCT pr_name

FROM projekt, arbeiten, mitarbeiter, abteilung

WHERE projekt.pr_nr = arbeiten.pr_nr

AND arbeiten.m_nr = mitarbeiter.m_nr

AND mitarbeiter.abt_nr = abteilung.abt_nr

AND pr_name = 'Diagnose';

Das Ergebnis ist:

pr_name

Apollo

Gemini

Merkur

(3 rows)

Zurück zum Inhaltsverzeichnis

8.1.6 Eine Tabelle mit sich selbst verknüpfen

Join kann nicht nur auf zwei oder mehrere Tabellen angewendet

werden, sondern auch auf eine einzige Tabelle. In diesem

Fall wird die Tabelle mit sich selbst verknüpft, wobei eine

einzige Spalte dieser Tabelle gewöhnlich mit sich selbst

verglichen wird.

Wird eine Tabelle mit sich selbst verknüpft, erscheint ihr

Name doppelt in der FROM-Klausel einer SELECT-Anweisung.

Damit der Tabellenname in beiden Fällen unterschieden

werden kann, müssen Aliasnamen benutzt werden. Gleichzeitig

müssen alle Spalten dieser Tabelle in der SELECT-Anweisung

gekennzeichnet sein und zwar mit dem entsprechenden

Aliasnamen als Präfix.

Beispiel 8.11

Finden Sie alle Abteilungen, die sich an demselben Standort

befinden.

SELECT a.abt_nr, a.abt_name, a.stadt

FROM abteilung a, abteilung b

WHERE a.stadt = b.stadt

AND a.abt_nr <> b.abt_nr;

Das Ergebnis ist:

abt_nr	abt_name	stadt
a1	Beratung	Muenchen
a2	Diagnose	Muenchen

(2 rows)

In der FROM-Klausel des obigen Beispiels sind zwei

Aliasnamen für die Tabelle abteilung eingeführt worden. Die

erste Erscheinung der Tabelle hat den Aliasnamen a und

die zweite den Aliasnamen b. Die erste Bedingung in der

WHERE-Klausel definiert die Joinspalten, während die zweite

Bedingung notwendig ist, damit aus dem Ergebnis die mehrfach

vorhandenen Reihen entfernt werden.

Beispiel 8.12

Finden Sie Personalnummer, Namen und Wohnort der Mitarbeiter,

die im gleichen Ort wohnen und zu derselben Abteilung

gehören.

SELECT a.m_nr, a.m_name, a.wohnort

FROM mit_erweiter a, mit_erweiter b

WHERE a.wohnort = b.wohnort

AND a.abt_nr = b.abt_nr

AND a.m_nr <> b.m_nr;

Das Ergebnis ist:

m_nr	m_name	wohnort
29346	Probst	Augsburg
9031	Meier	Augsburg

(2 rows)

Zurück zum Inhaltsverzeichnis

8.1.7 Der Outer Join

In den bisherigen Beispielen für Equi-, Theta- und

natürlichen Join sind die Spalten zweier Tabellen immer

miteinander verglichen worden. Auf Grund dieses Vergleichs

werden dann die Reihen ausgewählt. Manchmal ist es in der

Praxis notwendig, nicht nur die Reihen auszuwählen, die

die Bedingung mit Joinspalten erfüllen, sondern auch die

Reihen einer der beiden Tabellen, die diese Bedingung nicht

erfüllen. Eine solche Verknüpfung der Tabellen heißt Outer

Join.

Damit der Unterschied zwischen Equijoin und Outer Join

transparenter wird, werden wir zunächst ein Beispiel mit

Equijoin zeigen.

Beispiel 8.13

Finden Sie alle Kombinationen von Mitarbeiter- und

Abteilungsdaten für Orte, die gleichzeitig Wohnorte der

Mitarbeiter und Standorte der Abteilungen sind.

SELECT *

FROM mit_erweiter, abteilung

WHERE wohnort = stadt;

Das Ergebnis ist:

m_nr	m_name	m_vorname	abt_nr	wohnort	abt_nr	abt_name	stadt
2581	Kaufmann	Brigitte	a2	Muenchen	a2	Diagnose	Muenchen
25348	Keller	Hans	a3	Muenchen	a1	Beratung	Muenchen
2581	Kaufmann	Brigitte	a2	Muenchen	a1	Beratung	Muenchen
25348	Keller	Hans	a3	Muenchen	a2	Diagnose	Muenchen

(4 rows)

Beispiel 8.13 stellt einen Equijoin dar. Falls wir zusätzlich

auch alle anderen Wohnorte der Mitarbeiter auswählen wollen,

muß Outer Join verwendet werden. Das Ergebnis dieses Outer

Joins kann man sich als die Erweiterung des Ergebnisses des

Beispiels 8.13 um eine zusätzliche Spalte vorstellen. Jeder

Datenwert dieser Spalte erhält den NULL-Wert, falls die

Bedingung in der WHERE-Klausel für diese Reihe nicht erfüllt

ist bzw. den entsprechenden Datenwert der zweiten Tabelle,

falls die Bedingung in der WHERE-Klausel erfüllt ist.

INGRES bietet keinen expliziten Operator für den Outer Join.

Grundsätzlich ist es möglich, den Outer Join mit Hilfe des

UNION-Operators und der Unterabfrage mit dem EXISTS-Operator

zu ersetzen, wie das nachfolgende Beispiel zeigt.

Beispiel 8.14

Finden Sie alle Kombinationen der Mitarbeiter- und

Abteilungsdaten für die Orte, die entweder nur Wohnorte der

Mitarbeiter oder gleichzeitig Wohnorte der Mitarbeiter und

Standorte der Abteilungen sind.

SELECT mit_erweiter.*, abteilung.abt_nr

FROM mit_erweiter, abteilung

WHERE wohnort = stadt

UNION

SELECT mit_erweiter.*, ' '

FROM mit_erweiter

WHERE NOT EXISTS

(SELECT *

FROM abteilung

WHERE stadt = wohnort);

Das Ergebnis ist:

m_nr	m_name	m_vorname	abt_nr	wohnort	abt_nr
2581	Kaufmann	Brigitte	a2	Muenchen	a1
25348	Keller	Hans	a3	Muenchen	a1
2581	Kaufmann	Brigitte	a2	Muenchen	a2
25348	Keller	Hans	a3	Muenchen	a2
10102	Huber	Petra	a3	Landshut
18316	Mueller	Gabriele	a1	Rosenheim
29346	Probst	Andreas	a2	Augsburg
9031	Meier	Rainer	a2	Augsburg
28559	Mozer	Sibille	a1	Ulm

(9 rows)

Die erste SELECT-Anweisung stellt den natürlichen Join der

Tabellen mitarbeiter und abteilung mit den Joinspalten

wohnort und stadt dar. Diese Anweisung wählt alle Reihen aus,

in denen die Orte gleichzeitig Wohnorte der Mitarbeiter und

Standorte der Abteilungen sind.

Die zweite SELECT-Anweisung mit der Unterabfrage wählt

zusätzliche Reihen aus, in denen die Orte nur Wohnorte der

Mitarbeiter sind.

Zurück zum Inhaltsverzeichnis

8.2 Korrelierte Unterabfragen

Eine Unterabfrage wird dann korreliert genannt, wenn die

innere SELECT-Anweisung eine Spalte enthält, deren Werte in

der äußeren SELECT-Anweisung festgelegt sind.

Die beiden folgenden Beispiele zeigen, wie dieselbe Aufgabe

mit Hilfe einer einfachen und einer korrelierten Unterabfrage

gelöst werden kann.

Beispiel 8.15

Nennen Sie die Namen aller Mitarbeiter, die im Projekt p3

arbeiten.

SELECT m_name

FROM mitarbeiter

WHERE m_nr IN

(SELECT m_nr

FROM arbeiten

WHERE pr_nr = 'p3');

Das Ergebnis ist:

m_name

Huber

Meier

Kaufmann

(3 rows)

Beispiel 8.15 zeigt eine einfache Unterabfrage, in der

zunächst die innere SELECT-Anweisung, unabhängig von der

äußeren SELECT-Anweisung, berechnet wird und als Ergebnis

die Werte für den IN-Operator geliefert werden. Diese Werte

werden dann zur Bildung des endgültigen Ergebnisses benutzt.

Beispiel 8.16

SELECT m_name

FROM mitarbeiter

WHERE 'p3' IN

(SELECT pr_nr

FROM arbeiten

WHERE arbeiten.m_nr = mitarbeiter.m_nr);

Das Ergebnis ist:

m_name

Huber

Meier

Kaufmann

(3 rows)

In Beispiel 8.16 kann die innere SELECT-Anweisung nicht in

einem Schritt ausgewertet werden, weil sie die Spalte m_nr

beinhaltet, die der Tabelle mitarbeiter aus der äußeren

SELECT-Anweisung angehört. Die innere SELECT-Anweisung ist

also von einer Variablen abhängig, die in der äußeren

SELECT-Anweisung berechnet sein muß.

In Beispiel 8.16, wie dies bei der korrelierten Unterabfrage

grundsätzlich der Fall ist, untersucht das System zunächst

die erste Reihe der Tabelle mitarbeiter und vergleicht die

Mitarbeiternummer (25348) mit der Spalte arbeiten.m_nr in der

inneren SELECT-Anweisung. Die innere SELECT-Anweisung liefert

nach diesem Vergleich als Ergebnis den Datenwert p2 für die

Spalte pr_nr. Dieser Wert ist nicht gleich dem Datenwert p3

in der äußeren SELECT-Anweisung. Deswegen wird die zweite

Reihe der Tabelle mitarbeiter auf dieselbe Weise untersucht.

Der Vergleich arbeiten.m_nr = mitarbeiter.m_nr in der inneren

SELECT-Anweisung liefert als Ergebnis zwei Datenwerte: p1

und p3. Weil p3 zu der Menge der Datenwerte gehört, die

die innere SELECT-Anweisung liefert, ist die Bedingung in

der äußeren SELECT-Anweisung erfüllt, und der Datenwert der

Spalte m_name in der zweiten Reihe (Huber) wird ausgewählt.

Danach werden auf dieselbe Weise alle Reihen der Tabelle

mitarbeiter untersucht und die Ergebnistabelle gebildet.

Im folgenden Beispiel wird eine korrelierte Unterabfrage

gezeigt, in der die Tabelle mit sich selbst verknüpft wird.

Beispiel 8.17

Finden Sie alle Abteilungen, die sich an demselben Standort

befinden.

SELECT a.abt_nr, a.abt_name, a.stadt

FROM abteilung a

WHERE a.stadt IN

(SELECT b.stadt

FROM abteilung b

WHERE a.abt_nr <> b.abt_nr);

Das Ergebnis ist

abt_nr	abt_name	stadt
a1	Beratung	Muenchen
a2	Diagnose	Muenchen

(2 rows)

Die Aufgabe in Beispiel 8.17 ist mit der Aufgabe in Beispiel

8.11 identisch.

Im folgenden Beispiel wird eine korrelierte Unterabfrage mit

der Aggregatfunktion MIN dargestellt.

Beispiel 8.18

Alle Mitarbeiter sind nach ihren Aufgaben in verschiedene

Gruppen (Sachbearbeiter usw.) unterteilt. Finden Sie

Personal-, Projektnummer und Aufgabe aller Mitarbeiter, die

nicht die kleinste Personalnummer in ihrer Aufgabengruppe

haben.

SELECT a.m_nr, a.pr_nr, a.aufgabe

FROM arbeiten a

WHERE m_nr >

(SELECT MIN(m_nr)

FROM arbeiten b

WHERE a.aufgabe= b.aufgabe);

Das Ergebnis ist:

m_nr	pr_nr	aufgabe
10102	p1	Projektleiter
10102	p3	Gruppenleiter
25348	p2	Sachbearbeiter
28559	p2	Sachbearbeiter
29346	p1	Sachbearbeiter

(5 rows)

Wie bereits erwähnt, darf die innere SELECT-Anweisung bei

der Verwendung eines Vergleichsoperators in der Unterabfrage,

wie in Beispiel 8.18, nur einen Datenwert liefern. Falls

das Ergebnis der inneren SELECT-Anweisung mehrere Datenwerte

beinhaltet, muß der IN-Operator benutzt werden.

Weitere Beispiele für die korrelierte Unterabfrage finden Sie

im nächsten Abschnitt.

Zurück zum Inhaltsverzeichnis

8.3 Der EXISTS-Operator in Unterabfragen

Im vorherigen Kapitel haben wir den EXISTS-Operator bereits

definiert. In diesem Abschnitt wird dieser Operator mit Hilfe

von Beispielen ausführlich erklärt.

Beispiel 8.19

Finden Sie die Namen aller Mitarbeiter, die im Projekt p1

arbeiten.

SELECT m_name

FROM mitarbeiter

WHERE EXISTS

(SELECT *

FROM arbeiten

WHERE mitarbeiter.m_nr = arbeiten.m_nr

AND pr_nr = 'p1');

Das Ergebnis ist:

m_name

Huber

Probst

Meier

Mozer

(4 rows)

Beim EXISTS-Operator wird die Bedingung in der äußeren

SELECT-Anweisung als wahr betrachtet, wenn die innere

SELECT-Anweisung zumindest eine Ergebnisreihe liefert.

Dementsprechend ist die Bedingung in der äußeren

SELECT-Anweisung falsch, wenn die innere SELECT-Anweisung

keine einzige Reihe als Ergebnis liefert.

Bei Unterabfragen mit dem EXISTS-Operator ist in der Praxis

die innere SELECT-Anweisung immer von einer Variablen

abhängig, die in der äußeren SELECT-Anweisung berechnet

wird. Alle praxisbezogenen SELECT-Anweisungen mit dem

EXISTS-Operator stellen damit gleichzeitig auch korrelierte

Unterabfragen dar.

Wie das INGRES-System eine SELECT-Anweisung mit dem

EXISTS-Operator bearbeitet, wird an Beispiel 8.19 erklärt. In

der Tabelle mitarbeiter wird zunächst der Datenwert (Keller)

der Spalte m_name der ersten Reihe ausgewählt. Danach

wird getestet, ob für den Mitarbeiter dieses Namens die

Bedingungen in der WHERE-Klausel der inneren SELECT-Anweisung

erfüllt sind oder nicht. Wenn die Bedingungen erfüllt

sind, liefert die innere SELECT-Anweisung eine oder

mehrere Ergebnisreihen. Das bedeutet gleichzeitig, daß der

ausgewählte Name zur Ergebnistabelle gehört. Beim Mitarbeiter

namens Keller ist die zweite Bedingung nicht erfüllt; deshalb

gehört dieser Name auch nicht zu der Ergebnistabelle. Danach

wird der Datenwert der zweiten Reihe (Huber) ausgewählt

und die Bedingungen überprüft. Bei diesem Mitarbeiter sind

beide Bedingungen erfüllt. Alle anderen Reihen der Tabelle

mitarbeiter werden dementsprechend nacheinander abgearbeitet.

Beispiel 8.20

Nennen Sie die Namen aller Mitarbeiter, deren Abteilung als

Standort eine andere Stadt als Stuttgart hat.

SELECT m_name

FROM mitarbeiter

WHERE NOT EXISTS

(SELECT *

FROM abteilung

WHERE mitarbeiter.abt_nr=abteilung.abt_nr

AND stadt = 'Stuttgart');

Das Ergebnis ist:

m_name

Mueller

Probst

Meier

Kaufmann

Mozer

(5 rows)

Die innere SELECT-Anweisung einer Unterabfrage mit dem

EXISTS-Operator wird in der Praxis als

SELECT *

geschrieben. Als Schreibweise wäre auch

SELECT spalten_liste

möglich, wobei spalten_liste eine oder mehrere

Spalten kennzeichnet. Der Grund für verschiedene

Darstellungsmöglichkeiten ist, daß beim EXISTS-Operator nur

die Existenz bzw. Nichtexistenz der Ergebnisreihen in der

inneren SELECT-Anweisung von Bedeutung ist und nicht die

Anzahl der ausgewählten Spalten.

Mit Hilfe des EXISTS-Operators ist es möglich, die

Mengenoperationen Durchschnitt und Differenz darzustellen.

Das nächste Beispiel zeigt, wie der Durchschnitt durch den

EXISTS-Operator ersetzt werden kann. In diesem, wie auch

in dem folgenden Beispiel, wird die Tabelle mit_erweiter

benutzt.

Beispiel 8.21

Finden Sie die Städte, die sowohl die Wohnorte der

Mitarbeiter als auch die Standorte der Abteilungen sind.

SELECT DISTINCT wohnort

FROM mit_erweiter

WHERE EXISTS

(SELECT stadt

FROM abteilung

WHERE stadt = wohnort);

Das Ergebnis ist:

wohnort

Muenchen

(1 row)

Das Beispiel 8.22 zeigt, wie die Differenz durch den NOT

EXISTS-Operator dargestellt werden kann.

Beispiel 8.22

Finden Sie die Wohnorte der Mitarbeiter, die nicht an den

Firmenstandorten wohnen.

SELECT DISTINCT wohnort

FROM mit_erweiter

WHERE NOT EXISTS

(SELECT stadt

FROM abteilung

WHERE wohnort = stadt);

Das Ergebnis ist:

wohnort

Augsburg

Rosenheim

Landshut

Ulm

(4 rows)

Der EXISTS-Operator kann auch verwendet werden, um die beiden

Operatoren ANY und ALL zu ersetzen. Dabei gilt grundsätzlich,

daß der Operator ANY durch den EXISTS-Operator zu ersetzen

ist, während NOT EXISTS für die Ersetzung des ALL-Operators

benutzt wird.

Das folgende Beispiel ist mit Beispiel 7.26 identisch.

Beispiel 8.23

Nennen Sie die Namen und Vornamen aller Mitarbeiter, die im

Projekt p1 arbeiten.

SELECT m_name, m_vorname

FROM mitarbeiter

WHERE EXISTS

(SELECT *

FROM arbeiten

WHERE pr_nr = 'p1'

AND mitarbeiter.m_nr = arbeiten.m_nr);

Das Ergebnis ist:

m_name	m_vorname
Huber	Petra
Probst	Andreas
Meier	Rainer
Mozer	Sibille

(4 rows)

Das Beispiel 8.24 ist mit Beispiel 7.27 identisch.

Beispiel 8.24

Nennen Sie die Aufgabe des Mitarbeiters, der die kleinste

Personalnummer hat.

SELECT DISTINCT aufgabe

FROM arbeiten

WHERE NOT EXISTS

(SELECT *

FROM mitarbeiter

WHERE NOT arbeiten.m_nr <= mitarbeiter.m_nr);

Das Ergebnis ist:

aufgabe

Projektleiter

(1 row)

Zurück zum Inhaltsverzeichnis

Aufgaben

A.8.1 Erstellen Sie ein:

- Equijoin,

- Natürlicher Join und ein

- Kartesisches Produkt

für die Tabellen projekt und arbeiten.

A.8.2 Finden Sie Personalnummer und Aufgabe aller Mitarbeiter,

die im Projekt Gemini arbeiten.

A.8.3 Finden Sie Namen und Vornamen aller Mitarbeiter, die

entweder Beratung oder Diagnose durchführen.

A.8.4 Finden Sie das Einstellungsdatum der Mitarbeiter, die zu

Abteilung a2 gehören und in ihrem Projekt Sachbearbeiter

sind.

A.8.5 Finden Sie die Namen des Projekts, in dem zwei oder

mehrere Sachbearbeiter arbeiten.

A.8.6 Nennen Sie Namen und Vornamen der Mitarbeiter, die

Gruppenleiter sind und im Projekt Merkur arbeiten.

A.8.7 Finden Sie in der erweiterten Beispieldatenbank die

Personalnummer der Mitarbeiter, die im gleichen Ort

wohnen und zu derselben Abteilung gehören.

A.8.8 Finden Sie die Personalnummer aller Mitarbeiter, die zur

Abteilung Freigabe gehören. Lösen Sie diese Aufgabe mit

Hilfe:

a) des Join-Operators

b) der korrelierten Unterabfrage.

Zurück zum Inhaltsverzeichnis