Korzystanie z operatorów zbiorów

Operatory zbioru są używane do łączenia wyników dwóch (lub więcej) instrukcji SELECT. Operatory SET dostępne w Oracle 11g to UNION, UNION ALL, INTERSECT i MINUS.

Operator zbioru UNION zwraca połączone wyniki dwóch instrukcji SELECT. W zasadzie usuwa duplikaty z wyników, tj. Tylko jeden wiersz będzie wymieniony dla każdego zduplikowanego wyniku.Aby przeciwdziałać temu zachowaniu, użyj operatora zestawu UNION ALL, który zachowuje duplikaty w wynik końcowy.INTERSECT wyświetla tylko rekordy, które są wspólne dla obu zapytań SELECT; operator zestawu MINUS usuwa wyniki drugiego zapytania z danych wyjściowych, jeśli znajdują się one również w wynikach pierwszego zapytania. Operacje na zbiorach INTERSECT i MINUS dają niepowtarzalne wyniki.

Wszystkie operatory SET mają między sobą ten sam stopień pierwszeństwa. Zamiast tego podczas wykonywania zapytania Oracle rozpoczyna ocenę od lewej do prawej lub od góry do dołu. wiszące operatory.

Punkty do zapamiętania -

Ta sama liczba kolumn musi być wybrana przez wszystkie uczestniczące instrukcje SELECT. Nazwy kolumn używane na ekranie są pobierane z pierwszego zapytania.
Typy danych z listy kolumn muszą być zgodne / niejawnie konwertowane przez Oracle. Oracle nie wykona niejawnej konwersji typu, jeśli odpowiednie kolumny w zapytaniach składowych należą do różnych grup typów danych.Na przykład, jeśli kolumna w pierwszym zapytaniu składowym ma typ danych DATE, a odpowiadająca jej kolumna w drugim zapytaniu składowym zawiera dane typ CHAR, Oracle nie wykona niejawnej konwersji, ale zgłosi błąd ORA-01790.
Aby posortować zestaw wyników, należy użyć porządku pozycyjnego. Porządkowanie poszczególnych zestawów wyników nie jest dozwolone w przypadku operatorów zestawów. ORDER BY może pojawić się raz na końcu zapytania. Na przykład,
Operatory UNION i INTERSECT są przemienne, tzn. Kolejność zapytań nie jest ważna; nie zmienia to efektu końcowego.
Pod względem wydajności UNION ALL wykazuje lepszą wydajność w porównaniu z UNION, ponieważ zasoby nie są marnowane na filtrowanie duplikatów i sortowanie zestawu wyników.
Operatory zestawów mogą być częścią zapytań podrzędnych.
Operatorów zestawów nie można używać w instrukcjach SELECT zawierających wyrażenia kolekcji TABLE.
Tabela LONG, BLOB, CLOB, BFILE, VARRAY lub zagnieżdżona nie są dozwolone do użycia w operatorach zestawów. Klauzula For update nie jest dozwolona w przypadku operatorów zestawów.

UNIA

Gdy wiele zapytań SELECT jest połączonych za pomocą operatora UNION, Oracle wyświetla połączony wynik ze wszystkich złożonych zapytań SELECT, po usunięciu wszystkich duplikatów i w kolejności posortowanej (domyślnie rosnącej), bez ignorowania wartości NULL.

Rozważmy poniższe pięć zapytań połączonych za pomocą operatora UNION. Końcowy połączony zestaw wyników zawiera wartości ze wszystkich SQL. Zwróć uwagę na usuwanie duplikatów i sortowanie danych.

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

Należy zauważyć, że kolumny wybrane w zapytaniach SELECT muszą mieć zgodny typ danych. Oracle zgłasza komunikat o błędzie, gdy reguła zostanie naruszona.

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

UNIA WSZYSTKO

UNION i UNION ALL funkcjonują podobnie, z niewielką różnicą. Ale UNION ALL daje zestaw wyników bez usuwania duplikatów i sortowania danych. Na przykład w powyższym zapytaniu UNION jest zastępowane przez UNION ALL, aby zobaczyć efekt.

Rozważ zapytanie przedstawione w sekcji UNION. Zwróć uwagę na różnicę w danych wyjściowych, które są generowane bez sortowania i deduplikacji.

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

KRZYŻOWAĆ

Używając operatora INTERSECT, Oracle wyświetla wspólne wiersze z obu instrukcji SELECT, bez duplikatów i danych uporządkowanych w kolejności posortowanej (domyślnie rosnącej).

Na przykład poniższe zapytanie SELECT pobiera wynagrodzenie, które jest powszechne w dziale 10 i 20. Zgodnie ze standardami ISO SQL, INTERSECT ma pierwszeństwo przed innymi ocenami operatorów zbiorów, ale nie jest to nadal włączone przez Oracle.

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

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

MINUS

Operator minus wyświetla wiersze, które są obecne w pierwszym zapytaniu, ale nie występują w drugim zapytaniu, bez duplikatów i danych ułożonych domyślnie w porządku rosnącym.

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

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

Dopasowanie instrukcji SELECT

Mogą istnieć scenariusze, w których złożone instrukcje SELECT mogą mieć różną liczbę i typ danych wybranych kolumn. Dlatego, aby jawnie dopasować listę kolumn, kolumny NULL są wstawiane w brakujących pozycjach, aby dopasować liczbę i typ danych wybranych kolumn w każdej instrukcji SELECT. W przypadku kolumn liczbowych można również podstawić zero, aby dopasować typ kolumn wybranych w zapytaniu.

W poniższym zapytaniu typ danych nazwisko pracownika (varchar2) i identyfikator lokalizacji (numer) nie są zgodne. Dlatego wykonanie poniższego zapytania spowodowałoby błąd z powodu problemu ze zgodnością.

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

Wyraźnie kolumny można dopasować, zastępując NULL dla identyfikatora lokalizacji i nazwy pracownika.

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

Korzystanie z klauzuli ORDER BY w operacjach SET

Klauzula ORDER BY może pojawić się tylko raz na końcu zapytania zawierającego złożone instrukcje SELECT, co oznacza, że poszczególne instrukcje SELECT nie mogą mieć klauzuli ORDER BY. Ponadto sortowanie może opierać się na kolumnach, które pojawiają się tylko w pierwszym zapytaniu SELECT. Z tego powodu zaleca się sortowanie zapytania złożonego według pozycji kolumn.

Poniższe zapytanie złożone ujednolica wyniki z dwóch działów i sortuje według kolumny PŁATNOŚĆ.

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;