Unterabfragen zum Lösen von Abfragen

Eine Unterabfrage wird am besten als Abfrage innerhalb einer Abfrage definiert. Mit Unterabfragen können Sie Abfragen schreiben, die Datenzeilen für Kriterien auswählen, die tatsächlich entwickelt werden, während die Abfrage zur Laufzeit ausgeführt wird. Formal ist es die Verwendung einer SELECT-Anweisung in einer der Klauseln einer anderen SELECT-Anweisung. Tatsächlich kann eine Unterabfrage in einer anderen Unterabfrage enthalten sein, die sich in einer anderen Unterabfrage befindet, und so weiter. Eine Unterabfrage kann auch in INSERT-, UPDATE- und DELETE-Anweisungen verschachtelt sein. Unterabfragen müssen in Klammern stehen.

Eine Unterabfrage kann an jeder Stelle verwendet werden, an der ein Ausdruck zulässig ist, sofern er einen einzelnen Wert zurückgibt. Dies bedeutet, dass eine Unterabfrage, die einen einzelnen Wert zurückgibt, auch als Objekt in einer FROM-Klauselliste aufgeführt werden kann. Dies wird als Inline-Ansicht bezeichnet, da eine Unterabfrage, die als Teil einer FROM-Klausel verwendet wird, wie eine virtuelle Tabelle oder Ansicht behandelt wird. Die Unterabfrage kann entweder in die FROM-Klausel, die WHERE-Klausel oder die HAVING-Klausel der Hauptabfrage eingefügt werden.

Oracle erlaubt eine maximale Verschachtelung von 255 Unterabfrageebenen in einer WHERE-Klausel. Es gibt keine Begrenzung für das Verschachteln von Unterabfragen, die in einer FROM-Klausel ausgedrückt werden. In der Praxis ist die Begrenzung auf 255 Ebenen überhaupt keine Begrenzung, da Unterabfragen, die über drei oder vier Ebenen hinaus verschachtelt sind, selten auftreten.

Eine SELECT-Anweisung für Unterabfragen ist der SELECT-Anweisung zum Starten einer regulären oder äußeren Abfrage sehr ähnlich. Die vollständige Syntax einer Unterabfrage lautet:

( SELECT [DISTINCT] subquery_select_parameter
  FROM {table_name | view_name}
               {table_name | view_name} ...
  [WHERE search_conditions]
  [GROUP BY column_name [,column_name ] ...]
  [HAVING search_conditions] )

Arten von Unterabfragen

Single Row Sub Query: Unterabfrage, die eine einzeilige Ausgabe zurückgibt. Sie kennzeichnen die Verwendung von einzeiligen Vergleichsoperatoren, wenn sie unter WHERE-Bedingungen verwendet werden.

Multiple row sub query: Unterabfrage, die mehrere Zeilen zurückgibt. Sie verwenden mehrere Zeilenvergleichsoperatoren wie IN, ANY, ALL. Es kann auch Unterabfragen geben, die mehrere Spalten zurückgeben.

Correlated Sub Query: Korrelierte Unterabfragen hängen von den Daten ab, die von der äußeren Abfrage bereitgestellt werden. Diese Art von Unterabfrage umfasst auch Unterabfragen, die den EXISTS-Operator verwenden, um das Vorhandensein von Datenzeilen zu testen, die bestimmte Kriterien erfüllen.

Einzeilige Unterabfrage

Eine einzeilige Unterabfrage wird verwendet, wenn die Ergebnisse der äußeren Abfrage auf einem einzelnen, unbekannten Wert basieren. Obwohl dieser Abfragetyp formal als "einzeilig" bezeichnet wird, impliziert der Name, dass die Abfrage mehrere Spalten zurückgibt, jedoch nur eine Ergebniszeile. Eine einzeilige Unterabfrage kann jedoch nur eine Ergebniszeile, die nur aus einer Spalte besteht, an die äußere Abfrage zurückgeben.

In der folgenden SELECT-Abfrage gibt inner SQL nur eine Zeile zurück, dh das Mindestgehalt für das Unternehmen. Dieser Wert wird wiederum verwendet, um das Gehalt aller Mitarbeiter zu vergleichen, und es werden nur diejenigen angezeigt, deren Gehalt dem Mindestgehalt entspricht.

SELECT first_name, salary, department_id
FROM employees
WHERE salary = (SELECT MIN (salary) 
		FROM employees);

Eine HAVING-Klausel wird verwendet, wenn die Gruppenergebnisse einer Abfrage aufgrund einer bestimmten Bedingung eingeschränkt werden müssen. Wenn das Ergebnis einer Unterabfrage mit einer Gruppenfunktion verglichen werden muss, müssen Sie die innere Abfrage in die HAVING-Klausel der äußeren Abfrage verschachteln.

SELECT department_id, MIN (salary)
FROM employees
GROUP BY department_id
HAVING MIN (salary)  < (SELECT AVG (salary) FROM employees)

Unterabfrage mit mehreren Zeilen

Mehrzeilige Unterabfragen sind verschachtelte Abfragen, die mehr als eine Ergebniszeile an die übergeordnete Abfrage zurückgeben können. Mehrzeilige Unterabfragen werden am häufigsten in WHERE- und HAVING-Klauseln verwendet. Da es mehrere Zeilen zurückgibt, muss es von festgelegten Vergleichsoperatoren (IN, ALL, ANY) verarbeitet werden. Während der IN-Operator dieselbe Bedeutung wie im vorherigen Kapitel hat, vergleicht der ANY-Operator einen angegebenen Wert mit jedem von der Unterabfrage zurückgegebenen Wert ALL vergleicht einen Wert mit jedem Wert, der von einer Unterabfrage zurückgegeben wird.

Die folgende Abfrage zeigt den Fehler, wenn eine einzelne Zeilenunterabfrage mehrere Zeilen zurückgibt.

SELECT	first_name, department_id
FROM employees
WHERE department_id = (SELECT department_id FROM employees WHERE LOCATION_ID = 100)
department_id = (select
               *
ERROR at line 4:
ORA-01427: single-row subquery returns more than one row

Verwendung mehrerer Zeilenoperatoren

• [> ALL] Mehr als der höchste von der Unterabfrage zurückgegebene Wert

• [<ALL] Weniger als der niedrigste von der Unterabfrage zurückgegebene Wert

• [<ANY] Weniger als der höchste von der Unterabfrage zurückgegebene Wert

• [> ANY] Mehr als der niedrigste von der Unterabfrage zurückgegebene Wert

• [= ANY] Entspricht jedem Wert, der von der Unterabfrage zurückgegeben wird (wie IN).

Oben SQL kann mit dem IN-Operator wie unten umgeschrieben werden.

SELECT	first_name, department_id
FROM employees
WHERE department_id IN (SELECT department_id
                   	    FROM departments
                   	    WHERE LOCATION_ID = 100)

Beachten Sie, dass IN in der obigen Abfrage mit den von der Unterabfrage zurückgegebenen Abteilungs-IDs übereinstimmt, diese mit denen in der Hauptabfrage vergleicht und den Namen des Mitarbeiters zurückgibt, der die Bedingung erfüllt.

Ein Join wäre eine bessere Lösung für die obige Abfrage, aber zur Veranschaulichung wurde darin eine Unterabfrage verwendet.

Korrelierte Unterabfrage

Im Gegensatz zu einer regulären Unterabfrage, bei der die äußere Abfrage von den von der inneren Abfrage bereitgestellten Werten abhängt, ist eine korrelierte Unterabfrage eine Abfrage, bei der die innere Abfrage von den von der äußeren Abfrage bereitgestellten Werten abhängt. Dies bedeutet, dass in einer korrelierten Unterabfrage die innere Abfrage wiederholt ausgeführt wird, einmal für jede Zeile, die von der äußeren Abfrage ausgewählt werden könnte.

Korrelierte Unterabfragen können Ergebnistabellen erstellen, die komplexe Verwaltungsfragen beantworten.

Betrachten Sie die folgende SELECT-Abfrage. Im Gegensatz zu den zuvor berücksichtigten Unterabfragen kann die Unterabfrage in dieser SELECT-Anweisung nicht unabhängig von der Hauptabfrage aufgelöst werden. Beachten Sie, dass die äußere Abfrage angibt, dass Zeilen aus der Mitarbeitertabelle mit dem Aliasnamen e1 ausgewählt werden. Die innere Abfrage vergleicht die Spalte mit der Abteilungsnummer der Mitarbeiter (DepartmentNumber) der Mitarbeitertabelle mit dem Alias ​​e2 mit derselben Spalte für den Alias-Tabellennamen e1.

SELECT EMPLOYEE_ID, salary, department_id
FROM   employees E
WHERE salary > (SELECT AVG(salary)
                FROM   EMP T
                WHERE E.department_id = T.department_id)

Unterabfrage mit mehreren Spalten

Eine mehrspaltige Unterabfrage gibt mehr als eine Spalte an die äußere Abfrage zurück und kann in der FROM-, WHERE- oder HAVING-Klausel der äußeren Abfrage aufgeführt werden. Die folgende Abfrage zeigt beispielsweise die historischen Details des Mitarbeiters für diejenigen, deren aktuelles Gehalt im Bereich von 1000 bis 2000 liegt und die in Abteilung 10 oder 20 arbeiten.

SELECT first_name, job_id, salary
FROM emp_history
WHERE (salary, department_id) in (SELECT salary, department_id
				  FROM employees
 				  WHERE salary BETWEEN 1000 and 2000 
				  AND department_id BETWEEN 10 and 20)
ORDER BY first_name;

Wenn in der FROM-Klausel der äußeren Abfrage eine mehrspaltige Unterabfrage verwendet wird, wird eine temporäre Tabelle erstellt, auf die andere Klauseln der äußeren Abfrage verweisen können. Diese temporäre Tabelle wird formeller als Inline-Ansicht bezeichnet. Die Ergebnisse der Unterabfrage werden wie jede andere Tabelle in der FROM-Klausel behandelt. Wenn die temporäre Tabelle gruppierte Daten enthält, werden die gruppierten Teilmengen als separate Datenzeilen in einer Tabelle behandelt. Betrachten Sie die FROM-Klausel in der folgenden Abfrage. Die von der Unterabfrage gebildete Inline-Ansicht ist die Datenquelle für die Hauptabfrage.

SELECT * 
FROM (SELECT salary, department_id
	FROM employees
 	WHERE salary BETWEEN 1000 and 2000);