Utilisation des sous-requêtes

Une sous-requête est une requête dans une autre requête. Les sous-requêtes dans une clause FROM ou WHERE sont utilisées pour fournir des données qui seront utilisées pour limiter ou comparer/évaluer les données renvoyées par la requête les contenant.

Dans ce chapitre :

Types de sous-requêtes

Sous-requêtes corrélées vs. non corrélées

Les sous-requêtes peuvent être classées comme corrélées ou non corrélées :

  • Une sous-requête corrélée fait référence à une ou plusieurs colonnes à l’extérieur de la sous-requête. (Les colonnes sont généralement référencées dans la clause WHERE de la sous-requête.) Une sous-requête corrélée peut être considérée comme un filtre sur la table à laquelle elle se réfère, comme si la sous-requête était évaluée sur chaque ligne de la table dans la requête externe.

  • Une sous-requête non corrélée ne présente pas de telles références de colonnes externes. Il s’agit d’une requête indépendante dont les résultats sont renvoyés et utilisés une fois par la requête externe (et non par ligne).

Par exemple :

-- Uncorrelated subquery:
SELECT c1, c2
  FROM table1 WHERE c1 = (SELECT MAX(x) FROM table2);

-- Correlated subquery:
SELECT c1, c2
  FROM table1 WHERE c1 = (SELECT x FROM table2 WHERE y = table1.c2);

Sous-requêtes scalaires vs. non scalaires

Les sous-requêtes peuvent aussi être classées comme scalaires ou non scalaires :

  • Une sous-requête scalaire retourne une valeur unique (une colonne d’une ligne). Si aucune ligne ne peut être retournée, la sous-requête retourne NULL.

  • Une sous-requête non scalaire renvoie 0, 1 ou plusieurs lignes, chacune pouvant contenir 1 ou plusieurs colonnes. Pour chaque colonne, s’il n’y a pas de valeur à retourner, la sous-requête retourne NULL. Si aucune ligne ne peut être retournée, la sous-requête retourne 0 ligne (pas NULLs).

Types pris en charge par Snowflake

Snowflake prend actuellement en charge les types de sous-requêtes suivants :

  • Sous-requêtes scalaires non corrélées dans n’importe quel endroit où une expression de valeur peut être utilisée.

  • Sous-requêtes scalaires corrélées dans des clauses WHERE.

  • Sous-requêtes EXISTS, ANY / ALL et IN dans des clauses WHERE. Ces sous-requêtes peuvent être corrélées ou non.

Opérateurs de sous-requête

Les opérateurs de sous-requêtes opèrent sur des expressions de requête imbriquées. Ils peuvent être utilisés pour calculer des valeurs qui sont :

  • Retournées dans une liste SELECT.

  • Regroupées dans une clause GROUP BY.

  • Comparées à d’autres expressions de la clause WHERE ou HAVING.

Différences entre les sous-requêtes corrélées et non corrélées

La requête suivante illustre une sous-requête non corrélée dans une clause WHERE. La sous-requête obtient le GDP par habitant au Brésil, et la requête externe sélectionne tous les emplois (dans n’importe quel pays) qui paient moins que le GDP par habitant au Brésil. La sous-requête n’est pas corrélée, car la valeur qu’elle renvoie ne dépend d’aucune colonne de la requête externe. La sous-requête ne doit être appelée qu’une seule fois pendant toute l’exécution de la requête externe.

SELECT p.name, p.annual_wage, p.country
  FROM pay AS p
  WHERE p.annual_wage < (SELECT per_capita_GDP
                           FROM international_GDP
                           WHERE name = 'Brazil');

La requête suivante illustre une sous-requête corrélée dans une clause WHERE. Cette requête liste les emplois pour lesquels le salaire annuel de l’emploi est inférieur au GDP par habitant dans ce pays. Cette sous-requête est corrélée parce qu’elle est appelée une fois pour chaque ligne de la requête externe et reçoit une valeur p.country (nom du pays) de cette ligne.

SELECT p.name, p.annual_wage, p.country
  FROM pay AS p
  WHERE p.annual_wage < (SELECT MAX(per_capita_GDP)
                           FROM international_GDP i
                           WHERE p.country = i.name);

Note

La fonction d’agrégation MAX n’est pas logiquement nécessaire dans ce cas parce que la table international_GDP ne contient qu’une ligne par pays ; cependant, parce que le serveur ne le sait pas, et parce que le serveur exige que la sous-requête ne retourne qu’une seule ligne, nous utilisons la fonction d’agrégation pour forcer le serveur à reconnaître que la sous-requête retournera une ligne à chaque exécution.

Dans cet exemple, MAX, MIN ou AVG auraient tous fonctionné parce que l’application de l’une de ces valeurs à une valeur unique retournera cette valeur inchangée.

Sous-requêtes scalaires

Une sous-requête scalaire est une sous-requête qui retourne au maximum une ligne. Une sous-requête scalaire peut apparaître partout où une expression de valeur peut apparaître, y compris la liste SELECT, la clause GROUP BY, ou comme argument dans une fonction dans une clause WHERE ou HAVING.

Notes sur l’utilisation

  • Une sous-requête scalaire ne peut contenir qu’un seul élément dans la liste SELECT.

  • Si une sous-requête scalaire renvoie plus d’une ligne, une erreur d’exécution est générée.

  • Les sous-requêtes scalaires corrélées ne sont actuellement prises en charge que si elles peuvent être déterminées statiquement pour retourner une ligne, (par exemple si la liste SELECT contient une fonction d’agrégation sans fonction GROUP BY).

  • Les sous-requêtes scalaires non corrélées sont prises en charge partout où une expression de valeur est autorisée.

  • Les sous-requêtes avec une corrélation à l’intérieur de FLATTEN ne sont actuellement pas prises en charge.

  • La clause LIMIT / FETCH n’est autorisée que dans les sous-requêtes scalaires non corrélées.

Exemples

Cet exemple montre une sous-requête de base non corrélée dans une clause WHERE :

SELECT employee_id
FROM employees
WHERE salary = (SELECT max(salary) FROM employees);

Cet exemple montre une sous-requête non corrélée dans une clause FROM ; cette sous-requête de base n’obtient qu’un sous-ensemble des informations dans la table international_GDP. La requête globale liste les emplois dans les pays « à salaire élevé » où le salaire annuel de l’emploi est le même que le GDP par habitant dans ce pays.

SELECT p.name, p.annual_wage, p.country
  FROM pay AS p INNER JOIN (SELECT name, per_capita_GDP
                              FROM international_GDP
                              WHERE per_capita_GDP >= 10000.0) AS pcg
    ON pcg.per_capita_GDP = p.annual_wage AND p.country = pcg.name;

Limites

Bien que les sous-requêtes puissent contenir un grand nombre d’instructions SELECT, elles présentent les limitations suivantes :

  • Certaines clauses ne sont pas autorisées dans les sous-requêtes ANY/ALL/NOT EXISTS.

  • Le seul type de sous-requête qui autorise une clause LIMIT / FETCH est une sous-requête scalaire non corrélée. De plus, puisqu’une sous-requête scalaire non corrélée ne renvoie qu’une seule ligne, la clause LIMIT a peu ou pas de valeur pratique dans une sous-requête.