Query nidificate in Oracle

Abbiamo appreso come formulare query per interrogare le tabelle di un database in Oracle, e ne abbiamo esaminato la sintassi:

• il comando SELECT per selezionare le colonne di nostro interesse;
• la clausola FROM per indicare le tabelle su cui eseguire la query;
• la clausola WHERE per indicare condizioni di filtro e prelevare i soli dati che soddisfano tali condizioni;
• la clausola ORDER BY per impostare un ordine di visualizzazione dei dati.

Una "subquery" è una query inclusa in un'altra, ovvero un'interrogazione all'interno di altre interrogazioni. Una subquery ritorna dei dati (tabelle o dati singoli) necessari all'esecuzione di query ad un livello più alto.

Una query così strutturata rispetta l'ordine gerarchico di esecuzione: Oracle esegue sempre prima le query più interne e, una volta completate, quelle situate a "livelli" superiori.

Una query situata a un livello più basso rispetto ad un'altra viene definita "child", al contrario la query situata ad un livello più alto rispetto alla child viene definita "parent".

Le subquery possono essere usate sia nella clausola FROM (si chiamano in questo caso inline view ma non le tratteremo) sia nella clausola WHERE di una SELECT. In quest'ultimo caso si parla di query nidificate e possiamo avere al massimo 255 livelli di nidificazione ("strati" di interrogazione).

Subquery a riga singola

Hanno la particolarità di restituire sempre una sola riga di dati.

Per esempio cerchiamo l'impiegato (tabella employees) con lo stipendio più alto.

SQL> SELECT last_name AS cognome, first_name AS nome, salary AS salario
  2 FROM employees
  3 WHERE salary = (SELECT MAX (salary) FROM employees);

Oracle esegue la query nidificata in quest'ordine:

1. la child query (2° livello) ritorna il massimo valore della colonna "salary" della tabella "employees"
2. la parent query (1° livello) cerca gli impiegati il cui salario corrisponde al valore restituito dalla child query.

Una nidificazione di questo tipo usa sempre un operatore a singola riga, solitamente quello di uguaglianza (=).

Per esempio visualizziamo gli impiegati che lavorano nel dipartimento 'Public Relations'. Il codice identificativo del dipartimento è 70, basta interrogare la colonna "department_id" della tabella "departments", ma fingiamo di non conoscere il codice identificativo del dipartimento 'Public Relations' e scriviamo pertanto la seguente subquery:

SQL> SELECT last_name AS cognome, first_name AS nome, salary AS salario
  2 FROM employees WHERE department_id = 
  3  (SELECT department_id FROM departments
  4   WHERE department_name = 'Public Relations');

Oracle eseguirà inizialmente la subquery di 2° livello cercando l'id del dipartimento 'Public Relations', poi cercherà tutti gli impiegati il cui dipartimento (colonna department_id) coincide con il valore restituito dalla child query.

Nota: se la child query restituisse più di un risultato (ad esempio se più dipartimenti fossero memorizzati con lo stesso nome nella tabella departments) allora la query fallirebbe.

Query nidificate: le Subquery a riga multipla

Le subquery di questo tipo restituiscono più di una riga di risultati. È necessario usare operatori a riga multipla come IN, EXISTS, ANY e ALL.

Ad esempio visualizziamo gli impiegati che lavorano nei dipartimenti 'Administration' e 'Marketing'.

SQL> SELECT last_name AS cognome, first_name AS nome, salary
  2 FROM employees WHERE department_id IN 
  3  (SELECT department_id FROM departments
  4   WHERE (department_name = 'Administration' OR 
  5          department_name = 'Marketing'));

Oracle esegue la query rispettando il seguente ordine:

1. la child query (2° livello) seleziona i codici dei dipartimenti 'Administration' e 'Marketing' formando un insieme di due valori;
2. la parent query (1° livello) seleziona gli impiegati che lavorano nel dipartimento il cui codice è contenuto tra i valori presenti nell'insieme restituito dalla child query.

Usare le subquery in altri statement DML

Possiamo usare le subquery anche nelle operazioni di inserimento, modifica e cancellazione di dati (INSERT, UPDATE, DELETE).

Ad esempio inseriamo nella tabella "Padre" un nuovo nominativo. Alla colonna "idpadre" (PRIMARY KEY), sarà attribuito un nuovo valore utilizzando una subquery. Questa calcola il valore massimo presente nella colonna "idpadre" ed aggiunge un'unità per rispettare il vincolo di chiave primaria.

SQL> INSERT INTO padre
  2  VALUES ((SELECT MAX (IDPADRE)+1 FROM padre), 
  3  'Ferraris Nicola', TO_DATE ('23/03/1954', 'DD/MM/YYYY'));

Ancora aggiorniamo, nella tabella "Padre", la data di nascita del nominativo 'Rominelli Giacomo'. Non conoscendo a priori il suo id, useremo la subquery per ottenere il relativo valore di "idpadre".

SQL> UPDATE padre
  2 SET DATANASCITA = TO_DATE ('25/11/1964', 'DD/MM/YYYY')
  3 WHERE idpadre = (SELECT idpadre FROM padre 
  4   WHERE nominativo = 'Rominelli Giacomo');

Infine cancelliamo le righe della tabella "Padre" in cui il valore di "idpadre" è maggiore di 100.

SQL> DELETE FROM
  2 (SELECT * FROM padre WHERE idpadre > 100);