Sottoquery

Informazioni sulle sottoquery

Una sottoquery è una query presente all'interno di un'altra istruzione di query. Le query secondarie vengono anche chiamate SELECTs o SELECTnests nidificate. La sintassi completa di SELECT è valida nelle sottoquery.

Sottoquery di espressione

Le sottoquery di espressione vengono utilizzate in una query ovunque le espressioni siano valide. Restituisce un singolo valore, al contrario di una colonna o di una tabella. Le sottoquery di espressione possono essere correlate.

Sottoquery scalari

( subquery )

Descrizione

Una sottoquery all'interno di un'espressione viene interpretata come sottoquery scalare. Le sottoquery scalari sono spesso utilizzate nell'elenco SELECT o nella clausola WHERE.

Una sottoquery scalare deve selezionare una singola colonna. Il tentativo di selezionare più colonne causerà un errore di analisi. Un elenco SELECT con una singola espressione è il modo più semplice per selezionare una singola colonna. Il tipo di risultato della sottoquery scalare è il tipo di espressione.

Un'altra possibilità è utilizzare SELECT AS STRUCT per definire una sottoquery che seleziona un singolo valore di tipo STRUCT i cui campi siano definiti da una o più espressioni.

Se la sottoquery restituisce esattamente una riga, quel singolo valore è il risultato della sottoquery in scala. Se la sottoquery restituisce zero righe, il risultato è NULL. Se la query secondaria restituisce più di una riga, l'errore interessa la query.

Esempi

In questo esempio, una sottoquery scalare correlata restituisce le mascotte di un elenco di giocatori, utilizzando le tabelle Players e Mascots:

SELECT
  username,
  (SELECT mascot FROM Mascots WHERE Players.team = Mascots.team) AS player_mascot
FROM
  Players;

+---------------------------+
| username  | player_mascot |
+---------------------------+
| gorbie    | cardinal      |
| junelyn   | finch         |
| corba     | parrot        |
+---------------------------+

In questo esempio, una sottoquery aggregata aggregata calcola avg_level, il livello medio di un utente nella tabella Players.

SELECT
  username,
  level,
  (SELECT AVG(level) FROM Players) AS avg_level
FROM
  Players;

+---------------------------------------+
| username  | level      | avg_level    |
+---------------------------------------+
| gorbie    | 29         | 24.66        |
| junelyn   | 2          | 24.66        |
| corba     | 43         | 24.66        |
+---------------------------------------+

Sottoquery ARRAY

ARRAY ( subquery )

Descrizione

Una sottoquery ARRAY è un caso speciale di sottoquery di espressione, in cui restituisce un ARRAY. Se la sottoquery restituisce zero righe, l'ARRAY sarà vuoto. Non restituisce mai un ARRAY NULL.

L'elenco SELECT in una sottoquery ARRAY deve avere esattamente una colonna di qualsiasi tipo, che definisce il tipo di elemento dell'array restituito dalla sottoquery di array. In caso contrario, viene restituito un errore. Quando la sottoquery è scritta con SELECT AS STRUCT, l'elenco SELECT può includere più colonne e il valore restituito dalla subquery array è un ARRAY degli STRUCT creati. Selezionare più colonne senza utilizzare SELECT AS è un errore.

Le sottoquery ARRAY possono utilizzare SELECT AS STRUCT per creare array di strutture.

Per una semantica completa, vedi Funzioni di array.

Esempi

In questo esempio, una sottoquery ARRAY restituisce un array di nomi utente assegnati al team di colore rosso nella tabella NPCs:

SELECT
  ARRAY(SELECT username FROM NPCs WHERE team = 'red') AS red;

+-----------------+
| red             |
+-----------------+
| [niles,jujul]   |
+-----------------+

IN sottoquery

value [ NOT ] IN ( subquery )

Descrizione

Restituisce TRUE se value è nel set di righe restituite dalla sottoquery. Restituisce FALSE se la sottoquery restituisce zero righe.

L'elenco SELECT della sottoquery deve avere una singola colonna di qualsiasi tipo e il tipo deve essere paragonabile al tipo per value. In caso contrario, viene restituito un errore. Per la semantica completa, inclusa la gestione dei dispositivi NULL, consulta l'operatore IN.

Se devi utilizzare una sottoquery IN con un array, i seguenti sono equivalenti:

value [ NOT ] IN ( subquery )
value [ NOT ] IN UNNEST( ARRAY( subquery ) )

Esempi

In questo esempio, l'operatore IN che controlla se nella tabella Players esiste un nome utente chiamato corba:

SELECT
  'corba' IN (SELECT username FROM Players) AS result;

+--------+
| result |
+--------+
| TRUE   |
+--------+

Sottoquery EXISTS

EXISTS( subquery )

Descrizione

Restituisce TRUE se la sottoquery produce una o più righe. Restituisce FALSE se la query secondaria non restituisce righe. Non restituisce mai NULL. A differenza di tutte le altre sottoquery di espressione, non ci sono regole nell'elenco delle colonne. È possibile selezionare un numero di colonne qualsiasi e questo non influirà sul risultato della query.

Esempi

In questo esempio, l'operatore EXISTS controlla se vengono prodotte righe utilizzando la tabella Players:

SELECT
  EXISTS(SELECT username FROM Players WHERE team = 'yellow') AS result;

+--------+
| result |
+--------+
| FALSE  |
+--------+

Sottoquery di tabella

FROM ( subquery ) [ [ AS ] alias ]

Descrizione

Con le sottoquery di tabella, la query esterna considera il risultato della sottoquery come tabella. Puoi utilizzarli solo nella clausola FROM.

Esempi

In questo esempio, una sottoquery restituisce una tabella dei nomi utente dalla tabella Players:

SELECT results.username
FROM (SELECT * FROM Players) AS results;

+-----------+
| username  |
+-----------+
| gorbie    |
| junelyn   |
| corba     |
+-----------+

In questo esempio viene restituito un elenco NPCs assegnato al team di colore rosso.

SELECT
  username
FROM (
  WITH red_team AS (SELECT * FROM NPCs WHERE team = 'red')
  SELECT * FROM red_team
);

+-----------+
| username  |
+-----------+
| niles     |
| jujul     |
+-----------+

Sottoquery correlate

Una sottoquery correlata è una sottoquery che fa riferimento a una colonna dall'esterno di tale query secondaria. La correlazione impedisce il riutilizzo del risultato della sottoquery. Puoi trovare ulteriori informazioni qui.

Esempi

In questo esempio viene restituito un elenco di mascotte a cui non è assegnato alcun giocatore. È possibile fare riferimento alle tabelle Mascots e Players.

SELECT mascot
FROM Mascots
WHERE
  NOT EXISTS(SELECT username FROM Players WHERE Mascots.team = Players.team);

+----------+
| mascot   |
+----------+
| sparrow  |
+----------+

In questo esempio, una sottoquery scalare correlata restituisce le mascotte di un elenco di giocatori, utilizzando le tabelle Players e Mascots:

SELECT
  username,
  (SELECT mascot FROM Mascots WHERE Players.team = Mascots.team) AS player_mascot
FROM Players;

+---------------------------+
| username  | player_mascot |
+---------------------------+
| gorbie    | cardinal      |
| junelyn   | finch         |
| corba     | parrot        |
+---------------------------+

Sottoquery di volatili

Una sottoquery volatile è una sottoquery che non produce sempre lo stesso risultato con gli stessi input. Ad esempio, se una sottoquery include una funzione che restituisce un numero casuale, questa è volatile perché il risultato non è sempre lo stesso.

Esempi

In questo esempio, nella tabella Players viene restituito un numero casuale di nomi utente.

SELECT
  results.username
FROM
  (SELECT * FROM Players WHERE RAND() < 0.5) AS results;

-- The results are not always the same when you execute
-- the preceding query, but will look similar to this:
+----------+
| username |
+----------+
| gorbie   |
| junelyn  |
+----------+

Regole di valutazione per le sottoquery

Alcune sottoquery vengono valutate una volta e altre più spesso

• Una sottoquery volatile non correlata all'argomento può essere rivalutata una volta per riga, a seconda del piano di query.
• Una sottoquery correlata deve essere rivalutata logicamente per ogni set di valori dei parametri distinto. A seconda del piano di query, una sottoquery correlata potrebbe essere rivalutata una volta per riga, anche se più righe hanno gli stessi valori parametro.

Tabelle comuni utilizzate negli esempi

Alcuni esempi fanno riferimento a una tabella denominata Players:

+-----------------------------+
| username  | level   | team  |
+-----------------------------+
| gorbie    | 29      | red   |
| junelyn   | 2       | blue  |
| corba     | 43      | green |
+-----------------------------+

Alcuni esempi fanno riferimento a una tabella denominata NPCs:

+-------------------+
| username  | team  |
+-------------------+
| niles     | red   |
| jujul     | red   |
| effren    | blue  |
+-------------------+

Alcuni esempi fanno riferimento a una tabella denominata Mascots:

+-------------------+
| mascot   | team   |
+-------------------+
| cardinal | red    |
| parrot   | green  |
| finch    | blue   |
| sparrow  | yellow |
+-------------------+

Puoi utilizzare questa clausola WITH per emulare i nomi delle tabelle temporanee per Players e NPCs nelle sottoquery che supportano la clausola WITH:

WITH
  Players AS (
    SELECT 'gorbie' AS username, 29 AS level, 'red' AS team UNION ALL
    SELECT 'junelyn', 2 , 'blue' UNION ALL
    SELECT 'corba', 43, 'green'),
  NPCs AS (
    SELECT 'niles' AS username, 'red' AS team UNION ALL
    SELECT 'jujul', 'red' UNION ALL
    SELECT 'effren', 'blue'),
  Mascots AS (
    SELECT 'cardinal' AS mascot , 'red' AS team UNION ALL
    SELECT 'parrot', 'green' UNION ALL
    SELECT 'finch', 'blue' UNION ALL
    SELECT 'sparrow', 'yellow')
SELECT * FROM (
  SELECT username, team FROM Players UNION ALL
  SELECT username, team FROM NPCs);