Verwenden der Set-Operatoren

Set-Operatoren werden verwendet, um die Ergebnisse von zwei (oder mehr) SELECT-Anweisungen zu verknüpfen. Die in Oracle 11g verfügbaren SET-Operatoren sind UNION, UNION ALL, INTERSECT und MINUS.

Der UNION-Set-Operator gibt die kombinierten Ergebnisse der beiden SELECT-Anweisungen zurück. Im Wesentlichen werden Duplikate aus den Ergebnissen entfernt, dh für jedes duplizierte Ergebnis wird nur eine Zeile aufgelistet. Um diesem Verhalten entgegenzuwirken, verwenden Sie den UNION ALL-Set-Operator, in dem die Duplikate gespeichert sind Das Endergebnis. INTERSECT listet nur Datensätze auf, die beiden SELECT-Abfragen gemeinsam sind. Der MINUS-Set-Operator entfernt die Ergebnisse der zweiten Abfrage aus der Ausgabe, wenn sie auch in den Ergebnissen der ersten Abfrage enthalten sind. INTERSECT- und MINUS-Set-Operationen führen zu nicht duplizierten Ergebnissen.

Alle SET-Operatoren haben den gleichen Vorrang. Stattdessen startet Oracle während der Ausführung der Abfrage die Auswertung von links nach rechts oder von oben nach unten. Wenn explizit Klammern verwendet werden, kann die Reihenfolge abweichen, da Klammern Vorrang vor Klammern haben baumelnde Betreiber.

Punkte, die man sich merken sollte -

• Die gleiche Anzahl von Spalten muss von allen teilnehmenden SELECT-Anweisungen ausgewählt werden. Die in der Anzeige verwendeten Spaltennamen stammen aus der ersten Abfrage.

• Datentypen der Spaltenliste müssen von Oracle kompatibel / implizit konvertierbar sein. Oracle führt keine implizite Typkonvertierung durch, wenn entsprechende Spalten in den Komponentenabfragen zu verschiedenen Datentypgruppen gehören. Wenn beispielsweise eine Spalte in der ersten Komponentenabfrage vom Datentyp DATE ist und die entsprechende Spalte in der zweiten Komponentenabfrage Daten enthält Typ CHAR, Oracle führt keine implizite Konvertierung durch, löst jedoch den ORA-01790-Fehler aus.

• Die Positionsreihenfolge muss verwendet werden, um die Ergebnismenge zu sortieren. Die Reihenfolge der einzelnen Ergebnismengen ist bei Set-Operatoren nicht zulässig. ORDER BY kann einmal am Ende der Abfrage erscheinen. Zum Beispiel,

• UNION- und INTERSECT-Operatoren sind kommutativ, dh die Reihenfolge der Abfragen ist nicht wichtig. es ändert nichts am Endergebnis.

• In Bezug auf die Leistung zeigt UNION ALL im Vergleich zu UNION eine bessere Leistung, da keine Ressourcen für das Filtern von Duplikaten und das Sortieren der Ergebnismenge verschwendet werden.

• Set-Operatoren können Teil von Unterabfragen sein.

• Set-Operatoren können nicht in SELECT-Anweisungen verwendet werden, die TABLE-Auflistungsausdrücke enthalten.

• Die Tabellen LONG, BLOB, CLOB, BFILE, VARRAY oder verschachtelte Tabellen sind für die Verwendung in Set-Operatoren nicht zulässig. Die Aktualisierungsklausel ist für die Set-Operatoren nicht zulässig.

UNION

Wenn mehrere SELECT-Abfragen mit dem UNION-Operator verknüpft werden, zeigt Oracle das kombinierte Ergebnis aller zusammengesetzten SELECT-Abfragen an, nachdem alle Duplikate entfernt und in sortierter Reihenfolge (standardmäßig aufsteigend) entfernt wurden, ohne die NULL-Werte zu ignorieren.

Betrachten Sie die folgenden fünf Abfragen, die mit dem UNION-Operator verknüpft wurden. Die endgültige kombinierte Ergebnismenge enthält Werte aus allen SQLs. Beachten Sie das Entfernen und Sortieren von Duplikaten.

SELECT 1 NUM FROM DUAL
UNION
SELECT 5 FROM DUAL 
UNION
SELECT 3 FROM DUAL
UNION
SELECT 6 FROM DUAL
UNION
SELECT 3 FROM DUAL;

NUM
-------
1
3
5
6

Zu beachten ist, dass die in den SELECT-Abfragen ausgewählten Spalten vom kompatiblen Datentyp sein müssen. Oracle gibt eine Fehlermeldung aus, wenn die Regel verletzt wird.

SELECT TO_DATE('12-OCT-03') FROM DUAL
UNION
SELECT '13-OCT-03' FROM DUAL;

SELECT TO_DATE('12-OCT-03') FROM DUAL
       *
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression

UNION ALL

UNION und UNION ALL sind in ihrer Funktionsweise ähnlich, mit einem kleinen Unterschied. Aber UNION ALL liefert die Ergebnismenge, ohne Duplikate zu entfernen und die Daten zu sortieren. In der obigen Abfrage wird beispielsweise UNION durch UNION ALL ersetzt, um den Effekt zu sehen.

Betrachten Sie die im Abschnitt UNION gezeigte Abfrage. Beachten Sie den Unterschied in der Ausgabe, die ohne Sortieren und Deduplizieren generiert wird.

SELECT 1 NUM FROM DUAL
UNION ALL
SELECT 5 FROM DUAL 
UNION ALL
SELECT 3 FROM DUAL
UNION ALL
SELECT 6 FROM DUAL
UNION ALL
SELECT 3 FROM DUAL;

NUM
-------
1
5
3
6
3

SICH SCHNEIDEN

Mit dem INTERSECT-Operator zeigt Oracle die gemeinsamen Zeilen aus beiden SELECT-Anweisungen an, ohne dass Duplikate und Daten in sortierter Reihenfolge angeordnet sind (standardmäßig aufsteigend).

Mit der folgenden SELECT-Abfrage wird beispielsweise das in den Abteilungen 10 und 20 übliche Gehalt abgerufen. Gemäß den ISO-SQL-Standards steht INTERSECT vor der Bewertung der Mengenoperatoren vor anderen, dies wird jedoch von Oracle noch nicht berücksichtigt.

SELECT SALARY
FROM employees
WHERE DEPARTMENT_ID = 10
INTRESECT
SELECT SALARY 
FROM employees
WHERE DEPARTMENT_ID = 20

SALARY
---------
1500
1200
2000

MINUS

Der Minus-Operator zeigt die Zeilen an, die in der ersten Abfrage vorhanden sind, aber in der zweiten Abfrage fehlen, ohne dass Duplikate und Daten standardmäßig in aufsteigender Reihenfolge angeordnet sind.

SELECT JOB_ID
FROM employees
WHERE DEPARTMENT_ID = 10
MINUS
SELECT JOB_ID
FROM employees
WHERE DEPARTMENT_ID = 20;

JOB_ID
-------------        
HR
FIN
ADMIN

Übereinstimmung mit der SELECT-Anweisung

Es kann Szenarien geben, in denen die zusammengesetzten SELECT-Anweisungen unterschiedliche Anzahl und Datentypen ausgewählter Spalten aufweisen können. Um die Spaltenliste explizit abzugleichen, werden an den fehlenden Positionen NULL-Spalten eingefügt, um der Anzahl und dem Datentyp der ausgewählten Spalten in jeder SELECT-Anweisung zu entsprechen. Bei Zahlenspalten kann auch Null ersetzt werden, um dem Typ der in der Abfrage ausgewählten Spalten zu entsprechen.

In der folgenden Abfrage stimmen der Datentyp des Mitarbeiternamens (varchar2) und die Standort-ID (Nummer) nicht überein. Daher würde die Ausführung der folgenden Abfrage aufgrund eines Kompatibilitätsproblems einen Fehler auslösen.

SELECT DEPARTMENT_ID "Dept", first_name "Employee"
FROM employees
UNION
SELECT DEPARTMENT_ID, LOCATION_ID
FROM departments;

ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression

Explizit können Spalten abgeglichen werden, indem die Standort-ID und der Name des Mitarbeiters durch NULL ersetzt werden.

SELECT DEPARTMENT_ID "Dept", first_name "Employee", NULL "Location"
FROM employees
UNION
SELECT DEPARTMENT_ID, NULL "Employee", LOCATION_ID
FROM departments;

Verwenden der ORDER BY-Klausel in SET-Operationen

Die ORDER BY-Klausel kann nur einmal am Ende der Abfrage erscheinen, die zusammengesetzte SELECT-Anweisungen enthält. Dies bedeutet, dass einzelne SELECT-Anweisungen keine ORDER BY-Klausel haben können. Darüber hinaus kann die Sortierung auf den Spalten basieren, die nur in der ersten SELECT-Abfrage angezeigt werden. Aus diesem Grund wird empfohlen, die zusammengesetzte Abfrage nach Spaltenpositionen zu sortieren.

Die folgende Compund-Abfrage vereint die Ergebnisse aus zwei Abteilungen und sortiert sie nach der Spalte SALARY.

SELECT employee_id, first_name, salary
FROM employees
WHERE department_id=10
UNION
SELECT employee_id, first_name, salary
FROM employees
WHERE department_id=20
ORDER BY 3;