À propos des sous-requêtes
Une sous-requête est une requête qui apparaît dans une autre instruction de requête. Les sous-requêtes sont également appelées sous-SELECT ou SELECT imbriqués. La syntaxe SELECT complète est valide dans les sous-requêtes.
La clause WITH n'est pas compatible avec une sous-requête. Cela renvoie une erreur :
SELECT account
FROM (
WITH result AS (SELECT * FROM NPCs)
SELECT *
FROM result);
Sous-requêtes d'expression
Les sous-requêtes d'expression sont utilisées dans une requête dès lors que les expressions sont valides. Elles renvoient une seule valeur, et non une colonne ou une table. Les sous-requêtes d'expression peuvent être corrélées.
Sous-requêtes scalaires
( subquery )
Description
Une sous-requête dans une expression est interprétée comme une sous-requête scalaire.
Les sous-requêtes scalaires sont souvent utilisées dans une liste SELECT ou une clause WHERE.
Une sous-requête scalaire doit sélectionner une seule colonne. Si vous essayez de sélectionner plusieurs colonnes, une erreur d'analyse se produit. Une liste SELECT avec une seule expression constitue le moyen le plus simple de sélectionner une seule colonne. Le type de résultat de la sous-requête scalaire est le type de cette expression.
Vous pouvez également utiliser SELECT AS STRUCT pour définir une sous-requête qui sélectionne une seule valeur de type STRUCT dont les champs sont définis par une ou plusieurs expressions.
Si la sous-requête renvoie exactement une ligne, cette valeur unique correspond au résultat de la sous-requête scalaire. Si la sous-requête ne renvoie aucune ligne, le résultat est NULL.
Si la sous-requête renvoie plus d'une ligne, la requête échoue et renvoie une erreur d'exécution.
Exemples
Dans cet exemple, une sous-requête scalaire corrélée renvoie les mascottes pour une liste de joueurs à l'aide des tables Players et Guilds :
SELECT account, (SELECT mascot FROM Guilds WHERE Players.guild = id) AS player_mascot
FROM Players;
+---------------------------+
| account | player_mascot |
+---------------------------+
| gorbie | cardinal |
| junelyn | finch |
| corba | parrot |
+---------------------------+
Dans cet exemple, une sous-requête scalaire d'agrégation calcule avg_level, le niveau moyen d'un compte utilisateur dans la table Players.
SELECT account, level, (SELECT AVG(level) FROM Players) AS avg_level
FROM Players;
+---------------------------------------+
| account | level | avg_level |
+---------------------------------------+
| gorbie | 29 | 24.66 |
| junelyn | 2 | 24.66 |
| corba | 43 | 24.66 |
+---------------------------------------+
Sous-requêtes ARRAY
ARRAY ( subquery )
Description
Une sous-requête de type ARRAY est un cas particulier de sous-requête d'expression dans le sens où elle renvoie un tableau. Si la sous-requête ne renvoie aucune ligne, elle renvoie un tableau vide.
Ne renvoie jamais un tableau NULL.
La liste SELECT d'une sous-requête ARRAY doit comprendre exactement une colonne de n'importe quel type, qui définit le type d'élément du tableau renvoyé par la sous-requête ARRAY. Sinon, une erreur est renvoyée. Lorsque la sous-requête est écrite avec SELECT AS STRUCT, la liste SELECT peut inclure plusieurs colonnes et la valeur renvoyée par la sous-requête ARRAY est un tableau des objets STRUCT construits.
En revanche, le fait de sélectionner plusieurs colonnes sans utiliser SELECT AS est considéré comme une erreur.
Les sous-requêtes ARRAY peuvent utiliser SELECT AS STRUCT pour créer des tableaux de structures.
Consultez la section Fonctions de tableau pour connaître la sémantique complète.
Exemples
Dans cet exemple, une sous-requête ARRAY renvoie un tableau de comptes attribués à la guilde rouge dans la table NPCs :
SELECT ARRAY(SELECT account FROM NPCs WHERE guild = 'red') as red
FROM NPCs LIMIT 1;
+-----------------+
| red |
+-----------------+
| [niles,jujul] |
+-----------------+
Sous-requêtes IN
value [ NOT ] IN ( subquery )
Description
Renvoie TRUE si value se trouve dans l'ensemble de lignes renvoyé par la sous-requête.
Renvoie FALSE si la sous-requête ne renvoie aucune ligne.
La liste SELECT de la sous-requête doit comporter une seule colonne de n'importe quel type et son type doit être comparable au type de value. Sinon, une erreur est renvoyée. Pour obtenir la sémantique complète, y compris concernant la gestion de NULL, consultez la section consacrée à l'opérateur IN.
Si vous devez utiliser une sous-requête IN avec un tableau, ces deux lignes sont équivalentes :
value [ NOT ] IN ( subquery )
value [ NOT ] IN UNNEST( ARRAY( subquery ) )
Exemples
Dans cet exemple, l'opérateur IN vérifie si un compte appelé corba existe dans la table Players :
SELECT "corba" IN (SELECT account FROM Players) as result;
+--------+
| result |
+--------+
| TRUE |
+--------+
Sous-requêtes EXISTS
EXISTS ( subquery )
Description
Renvoie TRUE si la sous-requête génère une ou plusieurs lignes. Renvoie FALSE si la sous-requête ne génère aucune ligne. Ne renvoie jamais NULL. Contrairement à toutes les autres sous-requêtes d'expression, il n'existe ici aucune règle concernant la liste de colonnes.
Il est possible de sélectionner n'importe quel nombre de colonnes sans que cela ait une incidence sur le résultat de la requête.
Exemples
Dans cet exemple, l'opérateur EXISTS qui vérifie si des lignes sont générées à l'aide de la table Players :
SELECT EXISTS(SELECT account FROM Players WHERE guild="yellow") as result;
+--------+
| result |
+--------+
| FALSE |
+--------+
Sous-requêtes de table
FROM ( subquery ) [ [ AS ] alias ]
Description
Avec les sous-requêtes de table, la requête externe traite le résultat de la sous-requête en tant que table. Vous ne pouvez les utiliser que dans la clause FROM.
Exemples
Dans cet exemple, une sous-requête renvoie une table de comptes à partir de la table Players :
SELECT results.account
FROM (SELECT * FROM Players) AS results;
+-----------+
| account |
+-----------+
| gorbie |
| junelyn |
| corba |
+-----------+
Sous-requêtes corrélées
Une sous-requête corrélée est une sous-requête qui référence une colonne extérieure à cette sous-requête. La corrélation empêche la réutilisation du résultat de la sous-requête. Cliquez ici pour en savoir plus.
Exemples
Cet exemple renvoie une liste des mascottes auxquelles aucun joueur n'est attribué. Les tables Guilds et Players sont référencées.
SELECT mascot
FROM Guilds
WHERE NOT EXISTS (SELECT account
FROM Players
WHERE Guilds.id = Players.guild)
+----------+
| mascot |
+----------+
| sparrow |
+----------+
Dans cet exemple, une sous-requête scalaire corrélée renvoie les mascottes pour une liste de joueurs à l'aide des tables Players et Guilds :
SELECT account, (SELECT mascot FROM Guilds WHERE Players.guild = id) AS player_mascot
FROM Players;
+---------------------------+
| account | player_mascot |
+---------------------------+
| gorbie | cardinal |
| junelyn | finch |
| corba | parrot |
+---------------------------+
Sous-requêtes volatiles
Une sous-requête volatile est une sous-requête qui ne produit pas toujours le même résultat sur les mêmes entrées. Par exemple, si une sous-requête inclut une fonction qui renvoie un nombre aléatoire, la sous-requête est volatile, car le résultat n'est pas toujours identique.
Exemples
Dans cet exemple, un nombre aléatoire de comptes est renvoyé à partir de la table Players.
SELECT results.account
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:
+---------+
| account |
+---------+
| gorbie |
| junelyn |
+---------+
Règles d'évaluation pour les sous-requêtes
Certaines sous-requêtes sont évaluées une seule fois, d'autres plus souvent.
- Une sous-requête volatile non corrélée peut être réévaluée une fois par ligne, en fonction de votre plan de requête.
- Une sous-requête corrélée doit être réévaluée de manière logique pour chaque ensemble distinct de valeurs de paramètres. Suivant votre plan de requête, une sous-requête corrélée peut être réévaluée une fois par ligne, même si plusieurs lignes présentent les mêmes valeurs de paramètre.
- Une sous-requête assignée à une table temporaire par
WITHest évaluée une fois en mode "comme si". Un plan de requête ne peut réévaluer la sous-requête que s'il est garantit que la réévaluation produit la même table à chaque fois.
Tables courantes utilisées dans les exemples
Certains exemples font référence à une table appelée Players :
+-----------------------------+
| account | level | guild |
+-----------------------------+
| gorbie | 29 | red |
| junelyn | 2 | blue |
| corba | 43 | green |
+-----------------------------+
Certains exemples font référence à une table appelée NPCs :
+-------------------+
| account | guild |
+-------------------+
| niles | red |
| jujul | red |
| effren | blue |
+-------------------+
Certains exemples font référence à une table appelée Guilds :
+-------------------+
| mascot | id |
+-------------------+
| cardinal | red |
| parrot | green |
| finch | blue |
| sparrow | yellow |
+-------------------+
Vous pouvez utiliser cette clause WITH pour émuler des noms de table temporaires pour Players et NPCs dans les sous-requêtes compatibles avec la clause WITH :
WITH
Players AS (
SELECT 'gorbie' AS account, 29 AS level, 'red' AS guild UNION ALL
SELECT 'junelyn', 2 , 'blue' UNION ALL
SELECT 'corba', 43, 'green'),
NPCs AS (
SELECT 'niles' AS account, 'red' AS guild UNION ALL
SELECT 'jujul', 'red' UNION ALL
SELECT 'effren', 'blue'),
Guilds AS (
SELECT 'cardinal' AS mascot , 'red' AS id UNION ALL
SELECT 'parrot', 'green' UNION ALL
SELECT 'finch', 'blue' UNION ALL
SELECT 'sparrow', 'yellow')
SELECT * FROM (
SELECT account, guild FROM Players UNION ALL
SELECT account, guild FROM NPCs)