Utilizzo degli operatori di gruppo

Gli operatori Set vengono utilizzati per unire i risultati di due (o più) istruzioni SELECT. Gli operatori SET disponibili in Oracle 11g sono UNION, UNION ALL, INTERSECT e MINUS.

L'operatore di gruppo UNION restituisce i risultati combinati delle due istruzioni SELECT. Essenzialmente, rimuove i duplicati dai risultati, ovvero verrà elencata solo una riga per ogni risultato duplicato.Per contrastare questo comportamento, utilizzare l'operatore di gruppo UNION ALL che conserva i duplicati in il risultato finale.INTERSECT elenca solo i record che sono comuni a entrambe le query SELECT; l'operatore di gruppo MINUS rimuove i risultati della seconda query dall'output se si trovano anche nei risultati della prima query. Le operazioni sugli insiemi INTERSECT e MINUS producono risultati non duplicati.

Tutti gli operatori SET condividono lo stesso grado di precedenza tra loro. Invece, durante l'esecuzione della query, Oracle avvia la valutazione da sinistra a destra o dall'alto verso il basso.Se vengono utilizzate esplicitamente le parentesi, l'ordine potrebbe differire poiché le parentesi avrebbero la priorità operatori penzolanti.

Punti da ricordare -

  • Lo stesso numero di colonne deve essere selezionato da tutte le istruzioni SELECT partecipanti. I nomi delle colonne utilizzati nella visualizzazione sono presi dalla prima query.

  • I tipi di dati dell'elenco di colonne devono essere compatibili / convertibili in modo implicito da Oracle. Oracle non eseguirà la conversione del tipo implicito se le colonne corrispondenti nelle query del componente appartengono a gruppi di tipi di dati diversi.Ad esempio, se una colonna nella prima query del componente è del tipo di dati DATE e la colonna corrispondente nella query del secondo componente è dei dati digitare CHAR, Oracle non eseguirà la conversione implicita, ma genererà l'errore ORA-01790.

  • L'ordinamento di posizione deve essere utilizzato per ordinare il set di risultati. L'ordinamento di set di risultati individuali non è consentito con gli operatori di set. ORDER BY può apparire una volta alla fine della query. Per esempio,

  • Gli operatori UNION e INTERSECT sono commutativi, ovvero l'ordine delle query non è importante; non cambia il risultato finale.

  • Dal punto di vista delle prestazioni, UNION ALL mostra prestazioni migliori rispetto a UNION perché le risorse non vengono sprecate nel filtraggio dei duplicati e nell'ordinamento del set di risultati.

  • Gli operatori di gruppo possono essere la parte di sottoquery.

  • Gli operatori di gruppo non possono essere utilizzati nelle istruzioni SELECT che contengono espressioni di raccolta TABLE.

  • La tabella LONG, BLOB, CLOB, BFILE, VARRAY o nidificata non è consentita per l'uso negli operatori Set. La clausola di aggiornamento non è consentita con gli operatori set.

UNIONE

Quando più query SELECT vengono unite utilizzando l'operatore UNION, Oracle visualizza il risultato combinato di tutte le query SELECT composte, dopo aver rimosso tutti i duplicati e in ordine (crescente per impostazione predefinita), senza ignorare i valori NULL.

Considera le cinque query seguenti unite utilizzando l'operatore UNION. Il set di risultati combinato finale contiene il valore di tutti gli SQL. Notare la rimozione della duplicazione e l'ordinamento dei dati.

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

Per essere notato, le colonne selezionate nelle query SELECT devono essere di tipo di dati compatibile. Oracle genera un messaggio di errore quando la regola viene violata.

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 e UNION ALL sono simili nel loro funzionamento con una leggera differenza. Ma UNION ALL fornisce il set di risultati senza rimuovere la duplicazione e ordinare i dati. Ad esempio, nella query precedente UNION è sostituito da UNION ALL per vedere l'effetto.

Considera la query dimostrata nella sezione UNION. Notare la differenza nell'output generato senza l'ordinamento e la deduplicazione.

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

INTERSECT

Utilizzando l'operatore INTERSECT, Oracle visualizza le righe comuni di entrambe le istruzioni SELECT, senza duplicati e dati disposti in ordine (crescente per impostazione predefinita).

Ad esempio, la query SELECT di seguito recupera lo stipendio che è comune nei dipartimenti 10 e 20. Secondo gli standard ISO SQL, INTERSECT è al di sopra degli altri in precedenza nella valutazione degli operatori di set, ma questo non è ancora incorporato da Oracle. 

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

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

MENO

L'operatore meno visualizza le righe presenti nella prima query ma assenti nella seconda query, senza duplicati e dati disposti in ordine crescente per impostazione predefinita. 

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

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

Corrispondenza con l'istruzione SELECT

Ci possono essere gli scenari in cui le istruzioni SELECT composte possono avere un conteggio e un tipo di dati diversi delle colonne selezionate. Pertanto, per abbinare esplicitamente l'elenco di colonne, le colonne NULL vengono inserite nelle posizioni mancanti in modo da corrispondere al conteggio e al tipo di dati delle colonne selezionate in ciascuna istruzione SELECT. Per le colonne numeriche, è anche possibile sostituire lo zero in modo che corrisponda al tipo di colonne selezionate nella query.

Nella query seguente, il tipo di dati del nome del dipendente (varchar2) e l'ID della posizione (numero) non corrispondono. Pertanto, l'esecuzione della query seguente genererebbe un errore a causa di un problema di compatibilità.

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

In modo esplicito, le colonne possono essere abbinate sostituendo NULL per l'ID posizione e il nome del dipendente.

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

Utilizzo della clausola ORDER BY nelle operazioni SET

La clausola ORDER BY può essere visualizzata solo una volta alla fine della query contenente istruzioni SELECT composte e implica che le singole istruzioni SELECT non possono avere la clausola ORDER BY. Inoltre, l'ordinamento può essere basato sulle colonne che compaiono solo nella prima query SELECT. Per questo motivo, si consiglia di ordinare la query composta utilizzando le posizioni delle colonne.

La query composta di seguito unifica i risultati di due reparti e ordina in base alla colonna SALARIO.

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;