Sottoquery per risolvere le query
Una sottoquery è meglio definita come una query all'interno di una query. Le sottoquery consentono di scrivere query che selezionano righe di dati per criteri effettivamente sviluppati durante l'esecuzione della query in fase di esecuzione. Più formalmente, è l'uso di un'istruzione SELECT all'interno di una delle clausole di un'altra istruzione SELECT. In effetti, una sottoquery può essere contenuta all'interno di un'altra sottoquery, che si trova all'interno di un'altra sottoquery e così via. Una sottoquery può anche essere nidificata all'interno delle istruzioni INSERT, UPDATE e DELETE. Le sottoquery devono essere racchiuse tra parentesi.
Una sottoquery può essere utilizzata ovunque sia consentita un'espressione a condizione che restituisca un singolo valore. Ciò significa che una sottoquery che restituisce un singolo valore può anche essere elencata come oggetto in un elenco di clausole FROM. Questa è definita vista inline perché quando una sottoquery viene utilizzata come parte di una clausola FROM, viene trattata come una tabella o vista virtuale. La sottoquery può essere inserita nella clausola FROM, nella clausola WHERE o nella clausola HAVING della query principale.
Oracle consente una nidificazione massima di 255 livelli di sottoquery in una clausola WHERE. Non vi è alcun limite per la nidificazione di sottoquery espresse in una clausola FROM. In pratica, il limite di 255 livelli non è affatto un limite perché è raro incontrare sottoquery nidificate oltre tre o quattro livelli.
Un'istruzione SELECT di una sottoquery è molto simile all'istruzione SELECT utilizzata per iniziare una query normale o esterna. La sintassi completa di una sottoquery è:
( 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] )Tipi di sottoquery
Single Row Sub Query: Sottoquery che restituisce l'output di una singola riga. Contrassegnano l'utilizzo di operatori di confronto a riga singola, se utilizzati in condizioni WHERE.
Multiple row sub query: Sottoquery che restituisce più righe di output. Fanno uso di più operatori di confronto di righe come IN, ANY, ALL. Possono esserci anche query secondarie che restituiscono più colonne.
Correlated Sub Query: Le sottoquery correlate dipendono dai dati forniti dalla query esterna. Questo tipo di sottoquery include anche sottoquery che utilizzano l'operatore EXISTS per verificare l'esistenza di righe di dati che soddisfano i criteri specificati.
Sottoquery su riga singola
Una sottoquery a riga singola viene utilizzata quando i risultati della query esterna sono basati su un singolo valore sconosciuto. Sebbene questo tipo di query sia formalmente chiamato "riga singola", il nome implica che la query restituisca più colonne, ma solo una riga di risultati. Tuttavia, una sottoquery a riga singola può restituire solo una riga di risultati composta da una sola colonna alla query esterna.
Nella query SELECT sottostante, l'SQL interno restituisce solo una riga, ovvero lo stipendio minimo per l'azienda. A sua volta utilizza questo valore per confrontare lo stipendio di tutti i dipendenti e visualizza solo quelli il cui stipendio è uguale al salario minimo.
SELECT first_name, salary, department_id
FROM employees
WHERE salary = (SELECT MIN (salary)
FROM employees);Una clausola HAVING viene utilizzata quando i risultati di gruppo di una query devono essere limitati in base a una condizione. Se il risultato di una sottoquery deve essere confrontato con una funzione di gruppo, è necessario nidificare la query interna nella clausola HAVING della query esterna.
SELECT department_id, MIN (salary)
FROM employees
GROUP BY department_id
HAVING MIN (salary) < (SELECT AVG (salary) FROM employees)Sottoquery su più righe
Le sottoquery su più righe sono query nidificate che possono restituire più di una riga di risultati alla query padre. Le sottoquery su più righe vengono utilizzate più comunemente nelle clausole WHERE e HAVING. Poiché restituisce più righe, deve essere gestito da operatori di confronto set (IN, ALL, ANY). Mentre l'operatore IN ha lo stesso significato discusso nel capitolo precedente, l'operatore ANY confronta un valore specificato con ogni valore restituito dalla sottoquery mentre ALL confronta un valore con ogni valore restituito da una sottoquery.
La query sotto mostra l'errore quando la sottoquery su riga singola restituisce più righe.
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 rowUtilizzo di più operatori di riga
[> ALL] Più del valore più alto restituito dalla sottoquery
[<TUTTI] Minore del valore più basso restituito dalla sottoquery
[<QUALSIASI] Minore del valore più alto restituito dalla sottoquery
[> QUALSIASI] Più del valore più basso restituito dalla sottoquery
[= ANY] Uguale a qualsiasi valore restituito dalla sottoquery (uguale a IN)
L'SQL sopra può essere riscritto utilizzando l'operatore IN come di seguito.
SELECT first_name, department_id
FROM employees
WHERE department_id IN (SELECT department_id
FROM departments
WHERE LOCATION_ID = 100)Nota nella query precedente, IN corrisponde agli ID reparto restituiti dalla sottoquery, lo confronta con quello nella query principale e restituisce il nome del dipendente che soddisfa la condizione.
Un join sarebbe una soluzione migliore per la query precedente, ma a scopo illustrativo, è stata utilizzata una sottoquery.
Sottoquery correlata
A differenza di una sottoquery regolare, in cui la query esterna dipende dai valori forniti dalla query interna, una sottoquery correlata è quella in cui la query interna dipende dai valori forniti dalla query esterna. Ciò significa che in una sottoquery correlata, la query interna viene eseguita ripetutamente, una volta per ogni riga che potrebbe essere selezionata dalla query esterna.
Le sottoquery correlate possono produrre tabelle dei risultati che rispondono a complesse domande di gestione.
Considera la seguente query SELECT. A differenza delle sottoquery considerate in precedenza, la sottoquery in questa istruzione SELECT non può essere risolta indipendentemente dalla query principale. Si noti che la query esterna specifica che le righe vengono selezionate dalla tabella dei dipendenti con un nome alias e1. La query interna confronta la colonna del numero di reparto del dipendente (DepartmentNumber) della tabella dei dipendenti con l'alias e2 con la stessa colonna per il nome della tabella alias 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)Query secondaria a più colonne
Una sottoquery a più colonne restituisce più di una colonna alla query esterna e può essere elencata nella clausola FROM, WHERE o HAVING della query esterna. Ad esempio, la query seguente mostra i dettagli storici del dipendente per quelli il cui stipendio corrente è compreso tra 1000 e 2000 e lavorano nel reparto 10 o 20.
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;Quando una sottoquery a più colonne viene utilizzata nella clausola FROM della query esterna, crea una tabella temporanea a cui può fare riferimento altre clausole della query esterna. Questa tabella temporanea è denominata più formalmente una visualizzazione in linea. I risultati della sottoquery vengono trattati come qualsiasi altra tabella nella clausola FROM. Se la tabella temporanea contiene dati raggruppati, i sottoinsiemi raggruppati vengono trattati come righe di dati separate in una tabella. Considera la clausola FROM nella query seguente. La visualizzazione in linea formata dalla sottoquery è l'origine dati per la query principale.
SELECT *
FROM (SELECT salary, department_id
FROM employees
WHERE salary BETWEEN 1000 and 2000);