Syntaxe des requêtes

Les instructions de requête analysent une ou plusieurs tables ou expressions, et renvoient des lignes de calculs de résultats. Cette rubrique décrit la syntaxe des requêtes SQL dans Cloud Spanner SQL.

Syntaxe SQL

query_statement:
    [ statement_hint_expr ][ table_hint_expr ][ join_hint_expr ]
query_expr statement_hint_expr: '@{' statement_hint_key = statement_hint_value [, ...] '}' statement_hint_key: { USE_ADDITIONAL_PARALLELISM | OPTIMIZER_VERSION | OPTIMIZER_STATISTICS_PACKAGE | ALLOW_DISTRIBUTED_MERGE | LOCK_SCANNED_RANGES } query_expr: [ WITH cte[, ...] ] { select | ( query_expr ) | set_operation } [ ORDER BY expression [{ ASC | DESC }] [, ...] ] [ LIMIT count [ OFFSET skip_rows ] ] select: SELECT [ AS { STRUCT | VALUE } ] [{ ALL | DISTINCT }] { [ expression. ]* [ EXCEPT ( column_name [, ...] ) ]
[ REPLACE ( expression [ AS ] column_name [, ...] ) ]
| expression [ [ AS ] alias ] } [, ...] [ FROM from_clause[, ...] ] [ WHERE bool_expression ] [ GROUP BY expression [, ...] ] [ HAVING bool_expression ] table_hint_expr: '@{' table_hint_key = table_hint_value '}' table_hint_key: { FORCE_INDEX | GROUPBY_SCAN_OPTIMIZATION }

Règles de notation

  • Les crochets "[]" indiquent des clauses facultatives.
  • Les parenthèses "()" indiquent des parenthèses littérales.
  • La barre verticale "|" indique un opérateur logique "OR".
  • Les accolades "{}" renferment un ensemble d'options.
  • Une virgule suivie de points de suspension entre crochets "[, … ]" indique que l'élément précédent peut être répété dans une liste d'éléments séparés par des virgules.

Optimisations d'instruction

Les optimisations suivantes sont acceptées dans les instructions de requête :

Clé d'optimisation Valeurs possibles Description
USE_ADDITIONAL_PARALLELISM TRUE
FALSE (par défaut)
Si la valeur est TRUE, le moteur d'exécution privilégie l'utilisation du parallélisme lorsque cela est possible. Étant donné que cela peut diminuer les ressources disponibles pour d'autres opérations, vous pouvez éviter cette optimisation si vous exécutez des opérations sensibles à la latence sur la même instance.
OPTIMIZER_VERSION De 1 à N|latest_version|default_version Exécute la requête à l'aide de la version de l'optimiseur spécifiée. Les valeurs possibles sont 1 à N (la dernière version de l'optimiseur), default_version ou latest_version. Si l'indicateur n'est pas défini, l'optimiseur s'exécute sur le package défini dans les options de la base de données ou spécifié via l'API cliente. Si aucune de ces options n'est définie, l'optimiseur utilise la version par défaut.

En ce qui concerne la priorité du paramètre de version, la valeur définie par l'API cliente est prioritaire sur la valeur des options de base de données et celle définie par cet indice. a priorité sur tout le reste.

Pour en savoir plus, consultez la page sur l'optimiseur de requêtes.
OPTIMIZER_STATISTICS_PACKAGE package_name|latest Exécute la requête à l'aide du package de statistiques de l'optimiseur spécifié. Vous pouvez trouver les valeurs possibles pour package_name en exécutant la requête suivante :


SELECT * FROM INFORMATION_SCHEMA.SPANNER_STATISTICS

Si l'indicateur n'est pas défini, l'optimiseur s'exécute sur le package défini dans l'option de base de données ou spécifié via l'API cliente. Si aucun de ces paramètres n'est défini, l'optimiseur utilise par défaut le dernier package.

La valeur définie par l'API cliente a priorité sur la valeur des options de base de données. La valeur définie par cet indicateur est prioritaire sur toute autre valeur.

Le package spécifié doit être épinglé à l'aide de l'option de base de données ou disposer de allow_gc=false pour éviter la récupération de mémoire.

Pour en savoir plus, consultez la section Packages de statistiques de l'optimiseur de requêtes.
ALLOW_DISTRIBUTED_MERGE TRUE (par défaut)
FALSE
Si la valeur TRUE est définie (par défaut), le moteur privilégie l'utilisation d'un algorithme de tri de fusion distribué pour certaines requêtes ORDER BY. Le cas échéant, les tris globaux sont remplacés par des tris locaux. Cela offre l'avantage d'un tri parallèle à l'endroit où les données sont stockées. Les données triées localement sont ensuite fusionnées pour fournir des données triées dans le monde entier. Cela permet de supprimer les tris globaux complets et d'obtenir une latence potentiellement améliorée. Cette fonctionnalité peut augmenter le parallélisme de certaines requêtes ORDER BY. Cette indication a été fournie pour que les utilisateurs puissent essayer de désactiver l'algorithme de fusion distribuée, s'ils le souhaitent.
LOCK_SCANNED_RANGES exclusive
shared (par défaut)
Utilisez cette indication pour demander un verrouillage exclusif sur un ensemble de plages analysées par une transaction. L'acquisition d'un verrou exclusif est utile dans les cas où vous observez un conflit d'écriture élevé, c'est-à-dire que vous remarquez que plusieurs transactions tentent simultanément de lire et d'écrire dans les mêmes données, ce qui entraîne un grand nombre d'annulations. (Installation de Python groupée).

Sans indication, il est possible que plusieurs transactions simultanées obtiennent des verrous partagés, puis essayez de passer à des verrous exclusifs. Cette situation entraîne un interblocage, car le verrouillage partagé de chaque transaction empêche la ou les autres transactions de passer à la version exclusive. Cloud Spanner annule toutes les transactions, sauf une. Lorsque vous demandez un verrouillage exclusif à l'aide de ce conseil, l'une des transactions acquiert le verrouillage et poursuit l'exécution, tandis que les autres transactions attendent le verrouillage. Le débit est toujours limité, car les transactions en conflit ne peuvent être effectuées qu'une seule à la fois. Dans ce cas, Cloud Spanner progresse toujours sur une transaction, ce qui vous fait gagner du temps que l'annulation et la répétition seraient sinon dépensées. Transactions.

Ce conseil est compatible avec tous les types d'instructions, à la fois pour les requêtes et pour les instructions LMD.

Cloud Spanner applique toujours la sérialisabilité. Les indicateurs du mode de verrouillage peuvent affecter les transactions qui attendent ou annulent des charges de travail en concurrence, mais ne modifient pas le niveau d'isolation.

Étant donné qu'il s'agit d'un simple indice, il ne doit pas être considéré comme équivalent à un mutex. En d'autres termes, vous ne devez pas utiliser les verrous exclusifs Cloud Spanner en tant que mécanisme d'exclusion mutuelle pour l'exécution de code en dehors de Cloud Spanner.

Pour en savoir plus, consultez la section Verrouillage.

Exemples de tables

Les tables suivantes illustrent le comportement des différentes clauses de requête présentées dans ce document de référence.

Table Roster

La table Roster comprend une liste de noms de joueurs (LastName) et l'ID unique attribué à leur école (SchoolID). Elle se présente comme suit :

+-----------------------+
| LastName   | SchoolID |
+-----------------------+
| Adams      | 50       |
| Buchanan   | 52       |
| Coolidge   | 52       |
| Davis      | 51       |
| Eisenhower | 77       |
+-----------------------+

Vous pouvez émuler un nom de table temporaire pour les exemples figurant dans ce document de référence à l'aide de la clause WITH :

WITH Roster AS
 (SELECT 'Adams' as LastName, 50 as SchoolID UNION ALL
  SELECT 'Buchanan', 52 UNION ALL
  SELECT 'Coolidge', 52 UNION ALL
  SELECT 'Davis', 51 UNION ALL
  SELECT 'Eisenhower', 77)
SELECT * FROM Roster

Table PlayerStats

La table PlayerStats comprend une liste de noms de joueurs (LastName), l'ID unique attribué à l'adversaire qu'ils ont affronté lors d'une partie donnée (OpponentID) ainsi que le nombre de points obtenus par l'athlète lors de cette partie (PointsScored).

+----------------------------------------+
| LastName   | OpponentID | PointsScored |
+----------------------------------------+
| Adams      | 51         | 3            |
| Buchanan   | 77         | 0            |
| Coolidge   | 77         | 1            |
| Adams      | 52         | 4            |
| Buchanan   | 50         | 13           |
+----------------------------------------+

Vous pouvez émuler un nom de table temporaire pour les exemples figurant dans ce document de référence à l'aide de la clause WITH :

WITH PlayerStats AS
 (SELECT 'Adams' as LastName, 51 as OpponentID, 3 as PointsScored UNION ALL
  SELECT 'Buchanan', 77, 0 UNION ALL
  SELECT 'Coolidge', 77, 1 UNION ALL
  SELECT 'Adams', 52, 4 UNION ALL
  SELECT 'Buchanan', 50, 13)
SELECT * FROM PlayerStats

Table TeamMascot

La table TeamMascot comprend une liste d'ID d'école uniques (SchoolID) et la mascotte de cette école (Mascot).

+---------------------+
| SchoolID | Mascot   |
+---------------------+
| 50       | Jaguars  |
| 51       | Knights  |
| 52       | Lakers   |
| 53       | Mustangs |
+---------------------+

Vous pouvez émuler un nom de table temporaire pour les exemples figurant dans ce document de référence à l'aide de la clause WITH :

WITH TeamMascot AS
 (SELECT 50 as SchoolID, 'Jaguars' as Mascot UNION ALL
  SELECT 51, 'Knights' UNION ALL
  SELECT 52, 'Lakers' UNION ALL
  SELECT 53, 'Mustangs')
SELECT * FROM TeamMascot

Liste SELECT

SELECT [ AS { typename | STRUCT | VALUE } ] [{ ALL | DISTINCT }]
    { [ expression. ]* [ EXCEPT ( column_name [, ...] ) ]
[ REPLACE ( expression [ AS ] column_name [, ...] ) ]
| expression [ [ AS ] alias ] } [, ...]

La liste SELECT définit les colonnes renvoyées par la requête. Les expressions de la liste SELECT peuvent faire référence à des colonnes de n'importe quel élément from_item de la clause FROM correspondante.

Chaque élément de la liste SELECT est l'un des éléments suivants :

  • *
  • expression
  • expression.*

SELECT *

SELECT *, souvent appelé sélecteur étoile, génère une colonne de sortie pour chaque colonne visible après l'exécution de la requête complète.

SELECT * FROM (SELECT "apple" AS fruit, "carrot" AS vegetable);

+-------+-----------+
| fruit | vegetable |
+-------+-----------+
| apple | carrot    |
+-------+-----------+

SELECT expression

Les éléments d'une liste SELECT peuvent être des expressions. Ces expressions ont une valeur unique et produisent une colonne de sortie, avec un alias explicite facultatif.

Si l'expression ne comporte pas d'alias explicite, elle reçoit un alias implicite conformément aux règles applicables aux alias implicites, le cas échéant. Sinon, la colonne est anonyme et vous ne pouvez pas y faire référence par son nom ailleurs dans la requête.

SELECT expression.*

Un élément d'une liste SELECT peut également prendre la forme expression.*. Dans ce cas-là, une colonne de sortie est générée pour chaque colonne ou champ expression de premier niveau. L'expression doit être soit un alias de table, soit une valeur unique de type de données contenant des champs, tel que STRUCT.

La requête suivante génère une colonne de sortie pour chaque colonne de la table groceries, associée à g.

WITH groceries AS
  (SELECT "milk" AS dairy,
   "eggs" AS protein,
   "bread" AS grain)
SELECT g.*
FROM groceries AS g;

+-------+---------+-------+
| dairy | protein | grain |
+-------+---------+-------+
| milk  | eggs    | bread |
+-------+---------+-------+

Voici d'autres exemples :

WITH locations AS
  (SELECT STRUCT("Seattle" AS city, "Washington" AS state) AS location
  UNION ALL
  SELECT STRUCT("Phoenix" AS city, "Arizona" AS state) AS location)
SELECT l.location.*
FROM locations l;

+---------+------------+
| city    | state      |
+---------+------------+
| Seattle | Washington |
| Phoenix | Arizona    |
+---------+------------+
WITH locations AS
  (SELECT ARRAY<STRUCT<city STRING, state STRING>>[("Seattle", "Washington"),
    ("Phoenix", "Arizona")] AS location)
SELECT l.LOCATION[offset(0)].*
FROM locations l;

+---------+------------+
| city    | state      |
+---------+------------+
| Seattle | Washington |
+---------+------------+

Modificateurs pour l'opérateur *

SELECT * EXCEPT

Une instruction SELECT * EXCEPT spécifie les noms d'une ou de plusieurs colonnes à exclure du résultat. Tous les noms de colonne correspondants sont ôtés du résultat.

WITH orders AS
  (SELECT 5 as order_id,
  "sprocket" as item_name,
  200 as quantity)
SELECT * EXCEPT (order_id)
FROM orders;

+-----------+----------+
| item_name | quantity |
+-----------+----------+
| sprocket  | 200      |
+-----------+----------+

SELECT * REPLACE

Une instruction SELECT * REPLACE spécifie une ou plusieurs clauses expression AS identifier. Chaque identifiant doit correspondre à un nom de colonne de l'instruction SELECT *. Dans la liste des colonnes de sortie, la colonne correspondant à l'identifiant d'une clause REPLACE est remplacée par l'expression de cette même clause REPLACE.

L'instruction SELECT * REPLACE ne modifie ni les noms ni l'ordre des colonnes. Cependant, elle peut modifier la valeur et le type de valeur.

WITH orders AS
  (SELECT 5 as order_id,
  "sprocket" as item_name,
  200 as quantity)
SELECT * REPLACE ("widget" AS item_name)
FROM orders;

+----------+-----------+----------+
| order_id | item_name | quantity |
+----------+-----------+----------+
| 5        | widget    | 200      |
+----------+-----------+----------+

WITH orders AS
  (SELECT 5 as order_id,
  "sprocket" as item_name,
  200 as quantity)
SELECT * REPLACE (quantity/2 AS quantity)
FROM orders;

+----------+-----------+----------+
| order_id | item_name | quantity |
+----------+-----------+----------+
| 5        | sprocket  | 100      |
+----------+-----------+----------+

Traitement des lignes en double

Vous pouvez modifier les résultats renvoyés par une requête SELECT comme suit.

SELECT DISTINCT

Une instruction SELECT DISTINCT ignore les lignes en double et ne renvoie que les lignes restantes. SELECT DISTINCT ne peut pas renvoyer les colonnes des types suivants :

  • STRUCT
  • ARRAY

SELECT ALL

Une instruction SELECT ALL renvoie toutes les lignes, y compris les lignes en double. SELECT ALL est le comportement par défaut de SELECT.

Utilisation de types STRUCT avec SELECT

  • Les requêtes qui renvoient un message STRUCT à la racine du type renvoyé ne sont pas acceptées dans les API Cloud Spanner. Par exemple, la requête suivante est prise en charge uniquement en tant que sous-requête:

    SELECT STRUCT(1, 2) FROM Users;
    
  • Il est possible de renvoyer un tableau de structures. Par exemple, les requêtes suivantes sont compatibles avec les API Cloud Spanner:

    SELECT ARRAY(SELECT STRUCT(1 AS A, 2 AS B)) FROM Users;
    
    SELECT ARRAY(SELECT AS STRUCT 1 AS a, 2 AS b) FROM Users;
    
  • Toutefois, les formes de requête pouvant renvoyer une valeur NULL de type ARRAY<STRUCT<...>> ou une valeur de type ARRAY<STRUCT<...>> avec un élément NULL ne sont pas acceptées dans les API Cloud Spanner. La requête suivante n'est compatible que sous forme de sous-requête :

    SELECT ARRAY(SELECT IF(STARTS_WITH(Users.username, "a"), NULL, STRUCT(1, 2)))
    FROM Users;
    

Consultez la section Rechercher des éléments STRUCT dans un tableau pour plus d'exemples sur la façon d'interroger des STRUCTs dans un ARRAY.

Consultez également les remarques sur l'utilisation de STRUCTs dans les sous-requêtes.

Tables de valeurs

Dans Cloud Spanner SQL, une table de valeurs est une table dans laquelle le type de ligne est une valeur unique. Dans une table régulière, chaque ligne est composée de colonnes et chaque colonne possède un nom et un type. Dans une table de valeurs, le type de ligne correspond à une valeur unique et il n'y a pas de nom de colonne.

Dans Cloud Spanner, les tables de valeurs apparaissent principalement en tant que résultat de l'opérateur UNNEST ou d'une sous-requête. La clause WITH introduit une table de valeurs si la sous-requête utilisée génère une table de valeurs. Cloud Spanner n'accepte pas les tables de valeurs en tant que tables de base dans les schémas de base de données et n'accepte pas les tables de valeur renvoyées dans les résultats de requête. Par conséquent, les requêtes produisant des tables de valeurs ne sont pas acceptées en tant que requêtes de premier niveau.

Dans les contextes où une requête avec une seule colonne est attendue, une requête de table de valeurs peut être utilisée à la place. Par exemple, les sous-requêtes scalaires et les sous-requêtes de tableau (consultez la page Sous-requêtes) nécessitent généralement une requête à une colonne. Or, dans Cloud Spanner SQL, elles permettent également l'utilisation d'une requête de table de valeurs.

Une requête génère une table de valeurs si elle utilise SELECT AS conjointement à l'une des syntaxes suivantes :

SELECT AS STRUCT

SELECT AS STRUCT expr [[AS] struct_field_name1] [,...]

Cette instruction génère une table de valeurs avec un type de ligne STRUCT, où les noms et les types de champ STRUCT correspondent aux noms et types de colonne générés dans la liste SELECT.

Exemple :

SELECT ARRAY(SELECT AS STRUCT 1 a, 2 b)

SELECT AS STRUCT peut être utilisé dans une sous-requête scalaire ou de tableau pour produire un seul type STRUCT regroupant plusieurs valeurs. Les sous-requêtes scalaires et les sous-requêtes de tableau (consultez la section Sous-requêtes) ne sont généralement pas autorisées à renvoyer plusieurs colonnes, mais elles peuvent renvoyer une seule colonne avec le type STRUCT.

Les colonnes anonymes sont autorisées.

Exemple :

SELECT AS STRUCT 1 x, 2, 3

La requête ci-dessus produit des valeurs STRUCT de type STRUCT<int64 x, int64, int64>.. Le premier champ porte le nom x, tandis que les deuxième et troisième champs sont anonymes.

L'exemple ci-dessus produit le même résultat que cette requête SELECT AS VALUE à l'aide d'un constructeur de structure:

SELECT AS VALUE STRUCT(1 AS x, 2, 3)

Les colonnes en double sont autorisées.

Exemple :

SELECT AS STRUCT 1 x, 2 y, 3 x

La requête ci-dessus produit des valeurs STRUCT de type STRUCT<int64 x, int64 y, int64 x>.. Les premier et troisième champs portent le même nom x, tandis que le second champ porte le nom y.

L'exemple ci-dessus produit le même résultat, étant donné que cette requête SELECT AS VALUE utilise un constructeur de structure :

SELECT AS VALUE STRUCT(1 AS x, 2 AS y, 3 AS x)

SELECT AS VALUE

SELECT AS VALUE génère une table de valeurs à partir d'une liste SELECT comportant une seule colonne. Plutôt que de produire une table de sortie à une colonne, éventuellement avec un nom, la sortie sera une table de valeurs dans laquelle le type de ligne ne correspond qu'au type de valeur généré dans la colonne SELECT. Tous les alias de la colonne seront ignorés dans la table de valeurs.

Exemple :

SELECT AS VALUE 1

La requête ci-dessus génère une table avec le type de ligne INT64.

Exemple :

SELECT AS VALUE STRUCT(1 AS a, 2 AS b) xyz

La requête ci-dessus génère une table avec le type de ligne STRUCT<a int64, b int64>.

Exemple :

SELECT AS VALUE v FROM (SELECT AS STRUCT 1 a, true b) v WHERE v.b

Avec une table de valeurs v en tant qu'entrée, la requête ci-dessus filtre certaines valeurs de la clause WHERE, puis génère une table de valeurs avec la même valeur que celle contenue dans la table d'entrée. Si la requête ci-dessus n'utilisait pas SELECT AS VALUE, le schéma de la table de sortie diffèrerait du schéma de la table d'entrée, car la table de sortie serait une table standard avec une colonne nommée v contenant la valeur d'entrée.

Alias

Consultez la section Utiliser des alias pour en savoir plus sur la syntaxe et la visibilité des alias de la liste SELECT.

Clause FROM

FROM from_clause[, ...]

from_clause:
    from_item
    [ tablesample_operator ]

from_item:
    {
      table_name [ table_hint_expr ] [ as_alias ]
      | { join_operation | ( join_operation ) }
      | ( query_expr ) [ table_hint_expr ] [ as_alias ]
      | field_path
      | unnest_operator
      | cte_name [ table_hint_expr ] [ as_alias ]
    }

table_hint_expr:
    '@{' table_hint_key = table_hint_value '}'

table_hint_key:
    {
      FORCE_INDEX
      | GROUPBY_SCAN_OPTIMIZATION
    }

as_alias:
    [ AS ] alias

La clause FROM indique la ou les tables à partir desquelles extraire des lignes, et comment associer ces lignes afin de générer un flux unique de lignes à traiter dans le reste de la requête.

tablesample_operator

Consultez la section Opérateur TABLESAMPLE.

table_name

Nom d'une table existante.

SELECT * FROM Roster;
Optimisations relatives aux tables

Les optimisations suivantes s'appliquent aux tables :

Clé d'optimisation Valeurs possibles Description
FORCE_INDEX Chaîne. Nom d'un index existant dans la base de données ou _BASE_TABLE pour utiliser la table de base plutôt qu'un index.
  • Si elle est définie sur le nom d'un index, utilisez cet index à la place de la table de base. Si l'index ne peut pas fournir toutes les colonnes nécessaires, effectuez une nouvelle jointure avec la table de base.
  • Si elle est définie sur la chaîne _BASE_TABLE, utilisez la table de base pour la stratégie d'index plutôt qu'un index. Notez qu'il s'agit de la seule val eur valide lorsque FORCE_INDEX est utilisé dans une expression d'optimisation d'instruction.

Remarque : FORCE_INDEX est en fait une directive et non une optimisation, ce qui signifie qu'une erreur est générée si l'index n'existe pas.

GROUPBY_SCAN_OPTIMIZATION TRUE
FALSE

L'optimisation "group by scan" (grouper par analyse) peut accélérer les requêtes si celles-ci utilisent GROUP BY ou SELECT DISTINCT. Elle peut être appliquée si les clés de regroupement peuvent former un préfixe de la table ou la clé d'index sous-jacente, et si la requête ne requiert que la première ligne de chaque groupe.

L'optimisation est appliquée si l'optimiseur estime qu'elle améliorera la requête. Cette optimisation est prioritaire par rapport à cette décision. Si la valeur est définie sur FALSE, l'optimisation n'est pas prise en compte. Si la valeur est définie sur TRUE, l'optimisation sera appliquée tant qu'elle sera autorisée.

L'exemple suivant montre comment utiliser un index secondaire lors de la lecture à partir d'une table, en ajoutant une directive d'index au nom de la table sous la forme @{FORCE_INDEX=index_name} :

SELECT s.SingerId, s.FirstName, s.LastName, s.SingerInfo
FROM Singers@{FORCE_INDEX=SingersByFirstLastName} AS s
WHERE s.FirstName = "Catalina" AND s.LastName > "M";

Vous pouvez inclure plusieurs index dans une requête, même si un seul index est accepté pour chaque référence de table distincte. Exemple :

SELECT s.SingerId, s.FirstName, s.LastName, s.SingerInfo, c.ConcertDate
FROM Singers@{FORCE_INDEX=SingersByFirstLastName} AS s JOIN
     Concerts@{FORCE_INDEX=ConcertsBySingerId} AS c ON s.SingerId = c.SingerId
WHERE s.FirstName = "Catalina" AND s.LastName > "M";

Pour en savoir plus sur les directives d'index, reportez-vous à la section Index secondaires.

join_operation

Consultez la page Opérations JOIN.

query_expr

( query_expr ) [ [ AS ] alias ] est une sous-requête de table.

field_path

Dans la clause FROM, field_path correspond à un chemin qui renvoie vers un champ dans un type de données. field_path peut pénétrer de manière arbitraire dans une structure de données imbriquée.

Voici quelques exemples de valeurs field_path valides :

SELECT * FROM T1 t1, t1.array_column;

SELECT * FROM T1 t1, t1.struct_column.array_field;

SELECT (SELECT ARRAY_AGG(c) FROM t1.array_column c) FROM T1 t1;

SELECT a.struct_field1 FROM T1 t1, t1.array_of_structs a;

SELECT (SELECT STRING_AGG(a.struct_field1) FROM t1.array_of_structs a) FROM T1 t1;

Les chemins d'accès aux champs dans la clause FROM doivent se terminer par un champ de tableau. De plus, les chemins d'accès aux champs ne peuvent pas contenir de tableaux avant la fin du chemin d'accès. Par exemple, le chemin array_column.some_array.some_array_field n'est pas valide, car il contient un tableau avant la fin du chemin d'accès.

unnest_operator

Consultez la section Opérateur UNNEST.

cte_name

Les expressions de table courantes (CTE) d'une clause WITH agissent comme des tables temporaires auxquelles vous pouvez faire référence n'importe où dans la clause FROM. Dans l'exemple ci-dessous, subQ1 et subQ2 sont des expressions de table courantes (CTE).

Exemple :

WITH
  subQ1 AS (SELECT * FROM Roster WHERE SchoolID = 52),
  subQ2 AS (SELECT SchoolID FROM subQ1)
SELECT DISTINCT * FROM subQ2;

La clause WITH masque toutes les tables permanentes portant le même nom pendant la durée de la requête, sauf si vous qualifiez le nom de la table, par exemple,

db.Roster.

Opérateur UNNEST

unnest_operator:
    {
      UNNEST( array_expression )
      | UNNEST( array_path )
      | array_path
    }
    [ table_hint_expr ]
    [ as_alias ]
    [ WITH OFFSET [ as_alias ] ]

L'opérateur UNNEST prend un élément ARRAY et renvoie une table avec une ligne pour chaque élément de ARRAY. Vous pouvez également utiliser UNNEST en dehors de la clause FROM avec l'opérateur IN.

Pour les éléments ARRAY d'entrée de la plupart des types d'éléments, la sortie de UNNEST contient généralement une colonne. Cette colonne seule contient un alias facultatif, que vous pouvez utiliser pour y faire référence ailleurs dans la requête. Les éléments ARRAYS contenant ces types d'éléments renvoient plusieurs colonnes :

  • STRUCT

UNNEST détruit l'ordre des éléments dans l'élément ARRAY d'entrée. Utilisez la clause WITH OFFSET facultative pour renvoyer une deuxième colonne avec les index des éléments de tableau (voir ci-dessous).

Pour savoir comment utiliser UNNEST, y compris la construction, l'aplatissement et le filtrage, consultez la section Working with arrays.

UNNEST et STRUCT

Pour un élément ARRAY de colonnes STRUCT d'entrée, UNNEST renvoie une ligne pour chaque colonne STRUCT, avec une colonne distincte pour chaque champ de colonne STRUCT. L'alias de chaque colonne est le nom du champ STRUCT correspondant.

Exemple :

SELECT *
FROM UNNEST(ARRAY<STRUCT<x INT64, y STRING>>[(1, 'foo'), (3, 'bar')]);

+---+-----+
| x | y   |
+---+-----+
| 3 | bar |
| 1 | foo |
+---+-----+

Étant donné que l'opérateur UNNEST renvoie une table de valeurs, vous pouvez ajouter un alias UNNEST pour définir une variable de plage, à laquelle vous pouvez faire référence ailleurs dans la requête. Si vous faites référence à la variable de plage dans la liste SELECT, la requête renvoie le type STRUCT contenant tous les champs du type STRUCT d'origine dans la table d'entrée.

Exemple :

SELECT *, struct_value
FROM UNNEST(ARRAY<STRUCT<x INT64, y STRING>>[(1, 'foo'), (3, 'bar')])
       AS struct_value;

+---+-----+--------------+
| x | y   | struct_value |
+---+-----+--------------+
| 3 | bar | {3, bar}     |
| 1 | foo | {1, foo}     |
+---+-----+--------------+

UNNEST explicite et implicite

La séparation de l'élément ARRAY peut être explicite ou implicite. En cas de séparation explicite, array_expression doit renvoyer une valeur ARRAY, mais n'a pas besoin de renvoyer un élément ARRAY, et le mot clé UNNEST est requis.

Exemple :

SELECT * FROM UNNEST ([1, 2, 3]);

En cas de séparation implicite, array_path doit renvoyer un élément ARRAY, et le mot clé UNNEST est facultatif.

Exemple :

SELECT x
FROM mytable AS t,
  t.struct_typed_column.array_typed_field1 AS x;

Dans ce scénario, array_path peut pénétrer de manière arbitraire dans une structure de données, mais le dernier champ doit être du type ARRAY. Aucun champ précédent de l'expression ne peut être du type ARRAY, car il n'est pas possible d'extraire un champ nommé d'un élément ARRAY.

UNNEST et NULL

UNNEST traite les objets NULL comme suit :

  • Les tableaux NULL et vides ne produisent aucune ligne.
  • Un tableau contenant des valeurs NULL génère des lignes contenant des valeurs NULL.

La clause WITH OFFSET facultative renvoie une colonne distincte contenant la valeur de décalage "offset" (en comptant à partir de zéro) pour chaque ligne produite par l'opération UNNEST. Cette colonne comporte un alias facultatif. L'alias par défaut est "offset".

Exemple :

SELECT * FROM UNNEST ( ) WITH OFFSET AS num;

Opérateur TABLESAMPLE

tablesample_clause:
    TABLESAMPLE sample_method (sample_size percent_or_rows )

sample_method:
    { BERNOULLI | RESERVOIR }

sample_size:
    numeric_value_expression

percent_or_rows:
    { PERCENT | ROWS }

partition_by:
    PARTITION BY partition_expression [, ...]

Description

Vous pouvez utiliser l'opérateur TABLESAMPLE pour sélectionner un échantillon aléatoire d'un ensemble de données. Cet opérateur est utile lorsque vous travaillez avec des tables contenant de grandes quantités de données et que vous n'avez pas besoin de réponses précises.

  • sample_method : lorsque vous utilisez l'opérateur TABLESAMPLE, vous devez spécifier l'algorithme d'échantillonnage à utiliser :
    • BERNOULLI : chaque ligne est sélectionnée indépendamment avec la probabilité indiquée dans la clause percent. Par conséquent, vous obtenez environ N * percent/100 lignes.
    • RESERVOIR : prend comme paramètre une taille d'échantillonnage réelle K (exprimée en nombre de lignes). Si l'entrée est inférieure à K, la relation d'entrée entière est générée. Si l'entrée est supérieure à K, l'échantillonnage "réservoir" produit un échantillon de taille exactement égale à K, où tout échantillon de taille K a une probabilité égale.
  • sample_size : taille de l'échantillon.
  • percent_or_rows : l'opérateur TABLESAMPLE nécessite que vous choisissiez ROWS ou PERCENT. Si vous choisissez PERCENT, la valeur doit être comprise entre 0 et 100. Si vous choisissez ROWS, la valeur doit être supérieure ou égale à 0.

Exemples

Les exemples suivants illustrent l'utilisation de l'opérateur TABLESAMPLE.

Sélectionnez le contenu d'une table en utilisant la méthode d'échantillonnage RESERVOIR :

SELECT MessageId
FROM Messages TABLESAMPLE RESERVOIR (100 ROWS);

Sélectionnez le contenu d'une table en utilisant la méthode d'échantillonnage BERNOULLI :

SELECT MessageId
FROM Messages TABLESAMPLE BERNOULLI (0.1 PERCENT);

Utilisez TABLESAMPLE avec une sous-requête :

SELECT Subject FROM
(SELECT MessageId, Subject FROM Messages WHERE ServerId="test")
TABLESAMPLE BERNOULLI(50 PERCENT)
WHERE MessageId > 3;

Utilisez une opération TABLESAMPLE avec une jointure à une autre table.

SELECT S.Subject
FROM
(SELECT MessageId, ThreadId FROM Messages WHERE ServerId="test") AS R
TABLESAMPLE RESERVOIR(5 ROWS),
Threads AS S
WHERE S.ServerId="test" AND R.ThreadId = S.ThreadId;

Opération JOIN

join_operation:
    { cross_join_operation | condition_join_operation }

cross_join_operation:
    from_item cross_join_operator [ join_hint_expr ] from_item

condition_join_operation:
    from_item condition_join_operator [ join_hint_expr ] from_item join_condition

cross_join_operator:
    { CROSS JOIN | , }

condition_join_operator:
    {
      [INNER] [ join_method ] JOIN
      | FULL [OUTER] [ join_method ] JOIN
      | LEFT [OUTER] [ join_method ] JOIN
      | RIGHT [OUTER] [ join_method ] JOIN
    }

join_method:
    { HASH }

join_hint_expr:
    '@{' join_hint_key = join_hint_value [, ...] '}'

join_hint_key:
    { FORCE_JOIN_ORDER | JOIN_METHOD }

join_condition:
    { on_clause | using_clause }

on_clause:
    ON bool_expression

using_clause:
    USING ( join_column [, ...] )

L'opération JOIN fusionne deux éléments from_item afin que la clause SELECT puisse les interroger en tant que source unique. Les clauses join_type, ON, et USING (une "condition de jointure") spécifient comment combiner et supprimer les lignes de deux éléments from_item pour former une source unique.

Optimisations de jointure

Les optimisations suivantes sont compatibles avec JOIN :

Clé d'optimisation Valeurs possibles Description
FORCE_JOIN_ORDER TRUE
FALSE (par défaut)
Si elle est définie sur "true", utilisez l'ordre de jointure spécifié dans la requête.
JOIN_METHOD HASH_JOIN
APPLY_JOIN
MERGE_JOIN
PUSH_BROADCAST_HASH_JOIN
Lorsque vous mettez en œuvre une jointure logique, choisissez une alternative spécifique à utiliser pour la méthode de jointure sous-jacente. Pour en savoir plus, consultez la rubrique Méthodes de jointure.
Pour utiliser une jointure de hachage, utilisez HASH JOIN ou JOIN@{JOIN_METHOD=HASH_JOIN}, mais pas les deux.
HASH_JOIN_BUILD_SIDE BUILD_LEFT
BUILD_RIGHT
Spécifie quel côté de la jointure de hachage est utilisé comme côté build. Ne peut être utilisé qu'avec JOIN_METHOD=HASH_JOIN.
BATCH_MODE TRUE (default)
FALSE
Permet de désactiver la jointure par application par lot pour utiliser une jointure par application par ligne. Ne peut être utilisé qu'avec JOIN_METHOD=APPLY_JOIN.

Méthodes de jointure

Les méthodes de jointure sont des mises en œuvre spécifiques des différents types de jointure logique. Certaines méthodes de jointure ne sont disponibles que pour certains types de jointure. Le choix de la méthode de jointure à utiliser dépend des spécificités de votre requête et des données interrogées. Le meilleur moyen de déterminer si une méthode de jointure donnée contribue à améliorer les performances de votre requête est de l'essayer et d'afficher le plan d'exécution de la requête obtenu. Pour en savoir plus, consultez la page Opérateurs d'exécution de requêtes.

Méthode de jointure Description Opérandes
HASH_JOIN L'opérateur de jointure par hachage crée une table de hachage à partir d'un côté (le côté construction) et y sonde tous les éléments contenus de l'autre côté (le côté vérification). Différentes variantes sont utilisées pour différents types de jointure. Affichez le plan d'exécution de la requête pour voir quelle variante est utilisée. Obtenez plus d'informations sur l'opérateur de jointure par hachage.
APPLY_JOIN L'opérateur de jointure par application extrait chaque élément d'un côté (le côté entrée) et évalue la sous-requête de l'autre côté (le côté mise en correspondance) en utilisant les valeurs de l'élément du côté entrée. Différentes variantes sont utilisées pour différents types de jointure. L'application croisée est utilisée pour la jointure interne et l'application externe pour les jointures gauches. Obtenez plus d'informations sur les opérateurs cross apply et outer apply.
MERGE_JOIN L'opérateur "merge join" combine deux flux de données triées. L'optimiseur ajoute des opérateurs Sort au plan si les données ne fournissent pas déjà la propriété de tri requise pour la condition de jointure donnée. Le moteur fournit un tri distribué par défaut qui, lorsqu'il est associé à une jointure, peut permettre d'effectuer des jointures plus importantes, évitant ainsi les fuites de disques, et améliorant l'évolutivité et la latence. Différentes variantes sont utilisées pour différents types de jointure. Affichez le plan d'exécution de la requête pour voir quelle variante est utilisée. En savoir plus sur l'opérateur Fusionner la jointure.
PUSH_BROADCAST_HASH_JOIN L'opérateur de jointure de hachage de la diffusion push crée un lot de données à partir du côté build de la jointure. Le lot est ensuite envoyé en parallèle à toutes les divisions locales du test de la jointure. Sur chaque serveur local, une jointure de hachage est exécutée entre le lot et les données locales. Cette jointure est plus avantageuse lorsque l'entrée peut tenir dans un lot, mais qu'elle n'est pas stricte. Autre avantage potentiel : les opérations peuvent être distribuées aux serveurs locaux, par exemple une agrégation effectuée après une jointure. Une jointure de hachage de l'annonce transmise peut transmettre une certaine agrégation qu'une jointure de hachage traditionnelle ne peut pas effectuer. Différentes variantes sont utilisées pour différents types de jointure. Affichez le plan d'exécution de la requête pour voir quelle variante est utilisée. Pour en savoir plus, consultez la section concernant l'opérateur de jointure de hachage push de diffusion.

JOINTURE [INTERNE]

Une jointure INNER JOIN, ou simplement JOIN, calcule efficacement le produit cartésien des deux éléments from_item et supprime toutes les lignes qui ne répondent pas à la condition de jointure. "Efficacement" signifie qu'il est possible de mettre en œuvre une jointure INNER JOIN sans calculer réellement le produit cartésien.

FROM A INNER JOIN B ON A.w = B.y

Table A       Table B       Result
+-------+     +-------+     +---------------+
| w | x |  *  | y | z |  =  | w | x | y | z |
+-------+     +-------+     +---------------+
| 1 | a |     | 2 | k |     | 2 | b | 2 | k |
| 2 | b |     | 3 | m |     | 3 | c | 3 | m |
| 3 | c |     | 3 | n |     | 3 | c | 3 | n |
| 3 | d |     | 4 | p |     | 3 | d | 3 | m |
+-------+     +-------+     | 3 | d | 3 | n |
                            +---------------+
FROM A INNER JOIN B USING (x)

Table A       Table B       Result
+-------+     +-------+     +-----------+
| x | y |  *  | x | z |  =  | x | y | z |
+-------+     +-------+     +-----------+
| 1 | a |     | 2 | k |     | 2 | b | k |
| 2 | b |     | 3 | m |     | 3 | c | m |
| 3 | c |     | 3 | n |     | 3 | c | n |
| 3 | d |     | 4 | p |     | 3 | d | m |
+-------+     +-------+     | 3 | d | n |
                            +-----------+

Exemple

Cette requête effectue une jointure INNER JOIN sur les tables Roster et TeamMascot.

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Buchanan   | Lakers       |
| Coolidge   | Lakers       |
| Davis      | Knights      |
+---------------------------+

JOINTURE CROISÉE

CROSS JOIN renvoie le produit cartésien des deux éléments from_item. En d'autres termes, il associe chaque ligne du premier élément from_item à chaque ligne du deuxième élément from_item.

Si les lignes des deux éléments from_item sont indépendantes, le résultat contient M * N lignes, l'un des éléments from_item comportant M lignes et l'autre N lignes. Notez que cela reste valable même lorsque l'élément from_item ne contient aucune ligne.

Dans une clause FROM, un élément CROSS JOIN peut être écrit comme suit:

FROM A CROSS JOIN B

Table A       Table B       Result
+-------+     +-------+     +---------------+
| w | x |  *  | y | z |  =  | w | x | y | z |
+-------+     +-------+     +---------------+
| 1 | a |     | 2 | c |     | 1 | a | 2 | c |
| 2 | b |     | 3 | d |     | 1 | a | 3 | d |
+-------+     +-------+     | 2 | b | 2 | c |
                            | 2 | b | 3 | d |
                            +---------------+

Vous pouvez utiliser une jointure croisée corrélée pour convertir ou aplatir un ARRAY en un ensemble de lignes. Pour en savoir plus, consultez Convertir des éléments d'un tableau en lignes d'une table.

Exemples

Cette requête effectue une jointure CROSS JOIN sur les tables Roster et TeamMascot.

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster CROSS JOIN TeamMascot;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Adams      | Knights      |
| Adams      | Lakers       |
| Adams      | Mustangs     |
| Buchanan   | Jaguars      |
| Buchanan   | Knights      |
| Buchanan   | Lakers       |
| Buchanan   | Mustangs     |
| ...                       |
+---------------------------+

Virgule croisée (,)

Les éléments CROSS JOIN peuvent être écrits implicitement avec une virgule. C'est ce qu'on appelle une jointure croisée séparée par une virgule.

Voici à quoi ressemble une jointure croisée séparée par une virgule dans une clause FROM:

FROM A, B

Table A       Table B       Result
+-------+     +-------+     +---------------+
| w | x |  *  | y | z |  =  | w | x | y | z |
+-------+     +-------+     +---------------+
| 1 | a |     | 2 | c |     | 1 | a | 2 | c |
| 2 | b |     | 3 | d |     | 1 | a | 3 | d |
+-------+     +-------+     | 2 | b | 2 | c |
                            | 2 | b | 3 | d |
                            +---------------+

Vous ne pouvez pas écrire de jointures croisées séparées par des virgules entre parenthèses. Pour en savoir plus, consultez la section Opérations de jointure dans une séquence.

FROM (A, B)  // INVALID

Vous pouvez utiliser une jointure croisée corrélée pour convertir ou aplatir un ARRAY en un ensemble de lignes. Pour en savoir plus, consultez Convertir des éléments d'un tableau en lignes d'une table.

Exemples

Cette requête effectue une jointure croisée séparée par une virgule sur les tables Roster et TeamMascot.

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster, TeamMascot;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Adams      | Knights      |
| Adams      | Lakers       |
| Adams      | Mustangs     |
| Buchanan   | Jaguars      |
| Buchanan   | Knights      |
| Buchanan   | Lakers       |
| Buchanan   | Mustangs     |
| ...                       |
+---------------------------+

JOINTURE [EXTERNE] COMPLÈTE

Une jointure FULL OUTER JOIN (ou simplement FULL JOIN) renvoie tous les champs pour toutes les lignes dans les deux éléments from_item répondant à la condition de jointure.

FULL indique que toutes les lignes des deux éléments from_item sont renvoyées, même si elles ne répondent pas à la condition de jointure.

OUTER indique que si une ligne donnée d'un élément from_item ne rejoint aucune ligne de l'autre from_item, la ligne sera renvoyée avec des valeurs NULL pour toutes les colonnes de l'autre from_item.

FROM A FULL OUTER JOIN B ON A.w = B.y

Table A       Table B       Result
+-------+     +-------+     +---------------------------+
| w | x |  *  | y | z |  =  | w    | x    | y    | z    |
+-------+     +-------+     +---------------------------+
| 1 | a |     | 2 | k |     | 1    | a    | NULL | NULL |
| 2 | b |     | 3 | m |     | 2    | b    | 2    | k    |
| 3 | c |     | 3 | n |     | 3    | c    | 3    | m    |
| 3 | d |     | 4 | p |     | 3    | c    | 3    | n    |
+-------+     +-------+     | 3    | d    | 3    | m    |
                            | 3    | d    | 3    | n    |
                            | NULL | NULL | 4    | p    |
                            +---------------------------+
FROM A FULL OUTER JOIN B USING (x)

Table A       Table B       Result
+-------+     +-------+     +--------------------+
| x | y |  *  | x | z |  =  | x    | y    | z    |
+-------+     +-------+     +--------------------+
| 1 | a |     | 2 | k |     | 1    | a    | NULL |
| 2 | b |     | 3 | m |     | 2    | b    | k    |
| 3 | c |     | 3 | n |     | 3    | c    | m    |
| 3 | d |     | 4 | p |     | 3    | c    | n    |
+-------+     +-------+     | 3    | d    | m    |
                            | 3    | d    | n    |
                            | 4    | NULL | p    |
                            +--------------------+

Exemple

Cette requête effectue une jointure FULL JOIN sur les tables Roster et TeamMascot.

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster FULL JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Buchanan   | Lakers       |
| Coolidge   | Lakers       |
| Davis      | Knights      |
| Eisenhower | NULL         |
| NULL       | Mustangs     |
+---------------------------+

JOINTURE [EXTERNE] GAUCHE

Le résultat d'une jointure LEFT OUTER JOIN (ou simplement de LEFT JOIN) pour deux éléments from_item conserve toujours toutes les lignes de l'élément from_item gauche dans la clause JOIN même si aucune ligne dans l'élément from_item droit ne satisfait le prédicat de jointure.

LEFT indique que toutes les lignes de l'élément from_item left sont renvoyées. Si une ligne donnée de l'élément from_item gauche ne rejoint aucune ligne de l'élément from_item right, la ligne est renvoyée avec des valeurs NULL pour toutes les colonnes de l'élément from_item droit. Les lignes de l'élément from_item droit qui ne rejoignent aucune ligne de l'élément from_item gauche sont supprimées.

FROM A LEFT OUTER JOIN B ON A.w = B.y

Table A       Table B       Result
+-------+     +-------+     +---------------------------+
| w | x |  *  | y | z |  =  | w    | x    | y    | z    |
+-------+     +-------+     +---------------------------+
| 1 | a |     | 2 | k |     | 1    | a    | NULL | NULL |
| 2 | b |     | 3 | m |     | 2    | b    | 2    | k    |
| 3 | c |     | 3 | n |     | 3    | c    | 3    | m    |
| 3 | d |     | 4 | p |     | 3    | c    | 3    | n    |
+-------+     +-------+     | 3    | d    | 3    | m    |
                            | 3    | d    | 3    | n    |
                            +---------------------------+
FROM A LEFT OUTER JOIN B USING (x)

Table A       Table B       Result
+-------+     +-------+     +--------------------+
| x | y |  *  | x | z |  =  | x    | y    | z    |
+-------+     +-------+     +--------------------+
| 1 | a |     | 2 | k |     | 1    | a    | NULL |
| 2 | b |     | 3 | m |     | 2    | b    | k    |
| 3 | c |     | 3 | n |     | 3    | c    | m    |
| 3 | d |     | 4 | p |     | 3    | c    | n    |
+-------+     +-------+     | 3    | d    | m    |
                            | 3    | d    | n    |
                            +--------------------+

Exemple

Cette requête effectue une jointure LEFT JOIN sur les tables Roster et TeamMascot.

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster LEFT JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Buchanan   | Lakers       |
| Coolidge   | Lakers       |
| Davis      | Knights      |
| Eisenhower | NULL         |
+---------------------------+

JOINTURE [EXTERNE] DROITE

Le résultat d'une jointure RIGHT OUTER JOIN (ou simplement RIGHT JOIN) est semblable et symétrique à celui d'une jointure LEFT OUTER JOIN.

FROM A RIGHT OUTER JOIN B ON A.w = B.y

Table A       Table B       Result
+-------+     +-------+     +---------------------------+
| w | x |  *  | y | z |  =  | w    | x    | y    | z    |
+-------+     +-------+     +---------------------------+
| 1 | a |     | 2 | k |     | 2    | b    | 2    | k    |
| 2 | b |     | 3 | m |     | 3    | c    | 3    | m    |
| 3 | c |     | 3 | n |     | 3    | c    | 3    | n    |
| 3 | d |     | 4 | p |     | 3    | d    | 3    | m    |
+-------+     +-------+     | 3    | d    | 3    | n    |
                            | NULL | NULL | 4    | p    |
                            +---------------------------+
FROM A RIGHT OUTER JOIN B USING (x)

Table A       Table B       Result
+-------+     +-------+     +--------------------+
| x | y |  *  | x | z |  =  | x    | y    | z    |
+-------+     +-------+     +--------------------+
| 1 | a |     | 2 | k |     | 2    | b    | k    |
| 2 | b |     | 3 | m |     | 3    | c    | m    |
| 3 | c |     | 3 | n |     | 3    | c    | n    |
| 3 | d |     | 4 | p |     | 3    | d    | m    |
+-------+     +-------+     | 3    | d    | n    |
                            | 4    | NULL | p    |
                            +--------------------+

Exemple

Cette requête effectue une jointure RIGHT JOIN sur les tables Roster et TeamMascot.

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster RIGHT JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Buchanan   | Lakers       |
| Coolidge   | Lakers       |
| Davis      | Knights      |
| NULL       | Mustangs     |
+---------------------------+

Clause ON

Une ligne combinée (le résultat de la jointure de deux lignes) répond à la condition de jointure ON si la condition de jointure renvoie TRUE.

FROM A JOIN B ON A.x = B.x

Table A   Table B   Result (A.x, B.x)
+---+     +---+     +-------+
| x |  *  | x |  =  | x | x |
+---+     +---+     +-------+
| 1 |     | 2 |     | 2 | 2 |
| 2 |     | 3 |     | 3 | 3 |
| 3 |     | 4 |     +-------+
+---+     +---+

Exemple

Cette requête effectue une jointure INNER JOIN sur les tables Roster et TeamMascot.

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Buchanan   | Lakers       |
| Coolidge   | Lakers       |
| Davis      | Knights      |
+---------------------------+

Clause USING

La clause USING requiert une liste de colonnes d'une ou de plusieurs colonnes qui apparaissent dans les deux tables d'entrée. Elle effectue une comparaison d'égalité sur cette colonne et les lignes remplissent la condition de jointure si la comparaison d'égalité renvoie TRUE.

FROM A JOIN B USING (x)

Table A   Table B   Result
+---+     +---+     +---+
| x |  *  | x |  =  | x |
+---+     +---+     +---+
| 1 |     | 2 |     | 2 |
| 2 |     | 3 |     | 3 |
| 3 |     | 4 |     +---+
+---+     +---+

Exemple

Cette requête effectue une jointure INNER JOIN sur les tables Roster et TeamMascot.

Cette instruction renvoie les lignes de Roster et TeamMascot, où Roster.SchooldID est identique à TeamMascot.SchooldID. Les résultats incluent une seule colonne SchooldID.

SELECT * FROM Roster INNER JOIN TeamMascot USING (SchoolID);

+----------------------------------------+
| SchoolID   | LastName   | Mascot       |
+----------------------------------------+
| 50         | Adams      | Jaguars      |
| 52         | Buchanan   | Lakers       |
| 52         | Coolidge   | Lakers       |
| 51         | Davis      | Knights      |
+----------------------------------------+

Équivalence des mots clés ON et USING

Les mots clés ON et USING ne sont pas équivalents, mais ils sont similaires. ON renvoie plusieurs colonnes, et USING en renvoie une.

FROM A JOIN B ON A.x = B.x
FROM A JOIN B USING (x)

Table A   Table B   Result ON     Result USING
+---+     +---+     +-------+     +---+
| x |  *  | x |  =  | x | x |     | x |
+---+     +---+     +-------+     +---+
| 1 |     | 2 |     | 2 | 2 |     | 2 |
| 2 |     | 3 |     | 3 | 3 |     | 3 |
| 3 |     | 4 |     +-------+     +---+
+---+     +---+

Bien que ON et USING ne soient pas équivalents, ils peuvent renvoyer les mêmes résultats si vous spécifiez les colonnes à renvoyer.

SELECT x FROM A JOIN B USING (x);
SELECT A.x FROM A JOIN B ON A.x = B.x;

Table A   Table B   Result
+---+     +---+     +---+
| x |  *  | x |  =  | x |
+---+     +---+     +---+
| 1 |     | 2 |     | 2 |
| 2 |     | 3 |     | 3 |
| 3 |     | 4 |     +---+
+---+     +---+

Opérations de jointure dans une séquence

La clause FROM peut contenir une séquence de plusieurs opérations JOIN. Les opérations JOIN sont liées de gauche à droite. Exemple :

FROM A JOIN B USING (x) JOIN C USING (x)

-- A JOIN B USING (x)        = result_1
-- result_1 JOIN C USING (x) = result_2
-- result_2                  = return value

Vous pouvez également insérer des parenthèses pour regrouper des clauses JOIN :

FROM ( (A JOIN B USING (x)) JOIN C USING (x) )

-- A JOIN B USING (x)        = result_1
-- result_1 JOIN C USING (x) = result_2
-- result_2                  = return value

Les parenthèses permettent de regrouper les JOIN afin qu'elles soient liées dans un ordre différent :

FROM ( A JOIN (B JOIN C USING (x)) USING (x) )

-- B JOIN C USING (x)       = result_1
-- A JOIN result_1          = result_2
-- result_2                 = return value

Lorsque des jointures croisées séparées par une virgule sont présentes dans une requête avec une séquence de clauses JOIN, elles sont regroupées de gauche à droite comme les autres types JOIN :

FROM A JOIN B USING (x) JOIN C USING (x), D

-- A JOIN B USING (x)        = result_1
-- result_1 JOIN C USING (x) = result_2
-- result_2 CROSS JOIN D     = return value

Il ne peut pas y avoir de jointure RIGHT JOIN ou FULL JOIN après une jointure croisée séparée par une virgule, sauf si elle est entre parenthèses:

FROM A, B RIGHT JOIN C ON TRUE // INVALID
FROM A, B FULL JOIN C ON TRUE  // INVALID
FROM A, B JOIN C ON TRUE       // VALID
FROM A, (B RIGHT JOIN C ON TRUE) // VALID
FROM A, (B FULL JOIN C ON TRUE)  // VALID

Opération de jointure corrélée

Une opération de jointure est corrélée lorsque la valeur from_item de droite contient une référence à au moins une variable de plage ou un nom de colonne introduit par la valeur from_item de gauche.

Dans une opération de jointure corrélée, les lignes de l'élément from_item droit sont déterminées par une ligne de l'élément from_item gauche. Par conséquent, les jointures RIGHT OUTER et FULL OUTER ne peuvent pas être mises en corrélation, car les lignes from_item de droite ne peuvent pas être déterminées en l'absence de ligne de la jointure from_item de gauche.

Toutes les opérations de jointure corrélées doivent référencer un tableau situé dans l'élément from_item droit.

Voici un exemple conceptuel d'opération de jointure corrélée qui inclut une sous-requête corrélée:

FROM A JOIN UNNEST(ARRAY(SELECT AS STRUCT * FROM B WHERE A.ID = B.ID)) AS C
  • from_item quitté: A
  • Droite from_item: UNNEST(...) AS C
  • Une sous-requête corrélée: (SELECT AS STRUCT * FROM B WHERE A.ID = B.ID)

Voici un autre exemple de concept d'opération de jointure corrélée. array_of_IDs fait partie de l'élément from_item de gauche, mais il est référencé dans l'élément from_item de droite.

FROM A JOIN UNNEST(A.array_of_IDs) AS C

L'opérateur UNNEST peut être explicite ou implicite. Ces deux méthodes sont autorisées:

FROM A JOIN UNNEST(A.array_of_IDs) AS IDs
FROM A JOIN A.array_of_IDs AS IDs

Dans une opération de jointure corrélée, la valeur from_item de droite est réévaluée par rapport à chaque ligne distincte de la valeur from_item de gauche. Dans l'exemple conceptuel suivant, l'opération de jointure corrélée évalue d'abord A et B, puis A et C:

FROM
  A
  JOIN
  UNNEST(ARRAY(SELECT AS STRUCT * FROM B WHERE A.ID = B.ID)) AS C
  ON A.Name = C.Name

Exemples

Voici un exemple de jointure corrélée, utilisant les tables Roster et PlayerStats:

SELECT *
FROM
  Roster
JOIN
  UNNEST(
    ARRAY(
      SELECT AS STRUCT *
      FROM PlayerStats
      WHERE PlayerStats.OpponentID = Roster.SchoolID
    )) AS PlayerMatches
  ON PlayerMatches.LastName = 'Buchanan'

+------------+----------+----------+------------+--------------+
| LastName   | SchoolID | LastName | OpponentID | PointsScored |
+------------+----------+----------+------------+--------------+
| Adams      | 50       | Buchanan | 50         | 13           |
| Eisenhower | 77       | Buchanan | 77         | 0            |
+------------+----------+----------+------------+--------------+

Clause WHERE

WHERE bool_expression

La clause WHERE filtre les résultats de la clause FROM.

Seules les lignes dont la valeur bool_expression correspond à TRUE sont incluses. Les lignes dont la valeur bool_expression correspond à NULL ou FALSE sont rejetées.

L'évaluation d'une requête avec une clause WHERE est généralement effectuée dans l'ordre suivant :

  • FROM
  • WHERE
  • GROUP BY et agrégation
  • HAVING
  • WINDOW
  • QUALIFY
  • DISTINCT
  • ORDER BY
  • LIMIT

La clause WHERE ne peut référencer que les colonnes disponibles via la clause FROM. Elle ne peut pas faire référence à des alias de liste SELECT.

Exemples

Cette requête renvoie toutes les lignes de la table Roster dont la colonne SchoolID a la valeur 52 :

SELECT * FROM Roster
WHERE SchoolID = 52;

Le champ bool_expression peut contenir plusieurs sous-conditions.

SELECT * FROM Roster
WHERE STARTS_WITH(LastName, "Mc") OR STARTS_WITH(LastName, "Mac");

Les expressions d'une jointure INNER JOIN ont une expression équivalente dans la clause WHERE. Par exemple, une requête utilisant une jointure INNER JOIN et ON possède une expression équivalente à l'aide d'une jointure CROSS JOIN et WHERE. Par exemple, les deux requêtes suivantes sont équivalentes :

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster INNER JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster CROSS JOIN TeamMascot
WHERE Roster.SchoolID = TeamMascot.SchoolID;

Clause GROUP BY

GROUP BY expression [, ...]

La clause GROUP BY regroupe les lignes d'une table contenant des valeurs non distinctes pour la propriété expression de la clause GROUP BY. Si plusieurs lignes sont présentes dans la table source contenant des valeurs non distinctes pour expression, la clause GROUP BY génère une seule ligne combinée. GROUP BY est couramment utilisé lorsque des fonctions d'agrégat sont présentes dans la liste SELECT ou pour éliminer les redondances dans le résultat. Le type de données de la propriété expression doit être groupable.

Exemple :

SELECT SUM(PointsScored), LastName
FROM PlayerStats
GROUP BY LastName;

La clause GROUP BY peut faire référence à des noms d'expression dans la liste SELECT. La clause GROUP BY autorise également les références ordinales aux expressions de la liste SELECT à l'aide de valeurs entières. 1 fait référence à la première expression de la liste SELECT, 2 à la seconde, etc. La liste d'expressions peut combiner des ordinaux et des noms d'expressions.

Exemple :

SELECT SUM(PointsScored), LastName, FirstName
FROM PlayerStats
GROUP BY LastName, FirstName;

La requête ci-dessus est équivalente à :

SELECT SUM(PointsScored), LastName, FirstName
FROM PlayerStats
GROUP BY 2, FirstName;

Les clauses GROUP BY peuvent également faire référence à des alias. Si une requête contient des alias dans la clause SELECT, ceux-ci remplacent les noms dans la clause FROM correspondante.

Exemple :

SELECT SUM(PointsScored), LastName as last_name
FROM PlayerStats
GROUP BY last_name;

Clause HAVING

HAVING bool_expression

La clause HAVING filtre les résultats générés par GROUP BY ou agrégation. GROUP BY ou agrégation doit être présent dans la requête. En cas d'agrégation, la clause HAVING est évaluée une fois pour chaque ligne agrégée dans l'ensemble de résultats.

Seules les lignes dont la valeur bool_expression correspond à TRUE sont incluses. Les lignes dont la valeur bool_expression correspond à NULL ou FALSE sont rejetées.

L'évaluation d'une requête avec une clause HAVING est généralement effectuée dans l'ordre suivant :

  • FROM
  • WHERE
  • GROUP BY et agrégation
  • HAVING
  • WINDOW
  • QUALIFY
  • DISTINCT
  • ORDER BY
  • LIMIT

La clause HAVING peut référencer des colonnes disponibles via la clause FROM, ainsi que des alias de la liste SELECT. Les expressions référencées dans la clause HAVING doivent figurer dans la clause GROUP BY ou être le résultat d'une fonction d'agrégation :

SELECT LastName
FROM Roster
GROUP BY LastName
HAVING SUM(PointsScored) > 15;

Si une requête contient des alias dans la clause SELECT, ceux-ci remplacent les noms dans une clause FROM.

SELECT LastName, SUM(PointsScored) AS ps
FROM Roster
GROUP BY LastName
HAVING ps > 0;

Agrégation obligatoire

L'agrégation ne doit pas nécessairement être présente dans la clause HAVING elle-même, mais doit être présente sous au moins l'une des formes suivantes :

Fonction d'agrégation dans la liste SELECT

SELECT LastName, SUM(PointsScored) AS total
FROM PlayerStats
GROUP BY LastName
HAVING total > 15;

Fonction d'agrégation dans la clause HAVING

SELECT LastName
FROM PlayerStats
GROUP BY LastName
HAVING SUM(PointsScored) > 15;

Agrégation dans la liste SELECT et la clause HAVING

Lorsque des fonctions d'agrégation sont présentes à la fois dans la liste SELECT et dans la clause HAVING, les fonctions d'agrégation et les colonnes auxquelles elles font référence n'ont pas besoin d'être identiques. Dans l'exemple ci-dessous, les deux fonctions d'agrégation, COUNT() et SUM(), sont différentes et utilisent également des colonnes différentes.

SELECT LastName, COUNT(*)
FROM PlayerStats
GROUP BY LastName
HAVING SUM(PointsScored) > 15;

Clause ORDER BY

ORDER BY expression
  [COLLATE collate_string]
  [{ ASC | DESC }]
  [, ...]

La clause ORDER BY spécifie une colonne ou une expression en tant que critère de tri pour l'ensemble de résultats. Si aucune clause ORDER BY n'est présente, l'ordre des résultats d'une requête n'est pas défini. Les alias de colonnes d'une clause FROM ou d'une liste SELECT sont autorisés. Si une requête contient des alias dans la clause SELECT, ceux-ci remplacent les noms dans la clause FROM correspondante. Le type de données de la propriété expression doit pouvoir être trié.

Clauses facultatives

  • COLLATE: affinez l'organisation des données.
  • ASC | DESC : trie les résultats par ordre croissant ou décroissant de valeurs expression. ASC est la valeur par défaut.

Exemples

Utilisez l'ordre de tri par défaut (croissant).

SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
      SELECT 9, true)
ORDER BY x;
+------+-------+
| x    | y     |
+------+-------+
| 1    | true  |
| 9    | true  |
+------+-------+

Utilisez l'ordre de tri décroissant.

SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
      SELECT 9, true)
ORDER BY x DESC;
+------+-------+
| x    | y     |
+------+-------+
| 9    | true  |
| 1    | true  |
+------+-------+

Il est possible de trier à partir de plusieurs colonnes. Dans l'exemple ci-dessous, l'ensemble de résultats est d'abord classé par SchoolID, puis par LastName :

SELECT LastName, PointsScored, OpponentID
FROM PlayerStats
ORDER BY SchoolID, LastName;

Les règles suivantes s'appliquent lors du tri des valeurs :

  • NULL : dans le contexte de la clause ORDER BY, les valeurs NULL sont la plus petite valeur possible, c'est-à-dire qu'elles apparaissent en premier dans les tris ASC et en dernier dans les tris DESC.
  • Types de données avec virgule flottante : consultez la section Sémantique à virgule flottante portant sur le tri et le regroupement.

Lorsqu'elle est utilisée conjointement avec des opérateurs d'ensemble, la clause ORDER BY s'applique à l'ensemble de résultats de la requête entière. Elle ne s'applique pas uniquement à l'instruction SELECT la plus proche. Pour cette raison, il peut être utile (mais ce n'est pas obligatoire) d'utiliser des parenthèses pour indiquer la portée de la clause ORDER BY.

Cette requête sans parenthèses :

SELECT * FROM Roster
UNION ALL
SELECT * FROM TeamMascot
ORDER BY SchoolID;

est équivalente à la requête suivante avec des parenthèses :

( SELECT * FROM Roster
  UNION ALL
  SELECT * FROM TeamMascot )
ORDER BY SchoolID;

mais n'est pas équivalente à la requête ci-dessous, où la clause ORDER BY s'applique uniquement à la deuxième instruction SELECT :

SELECT * FROM Roster
UNION ALL
( SELECT * FROM TeamMascot
  ORDER BY SchoolID );

Vous avez également la possibilité d'utiliser des valeurs littérales entières dans les clauses ORDER BY. Une valeur littérale entière devient un ordinal (par exemple, le comptage commence à 1) dans la liste SELECT.

Par exemple, les deux requêtes suivantes sont équivalentes :

SELECT SUM(PointsScored), LastName
FROM PlayerStats
GROUP BY LastName
ORDER BY LastName;
SELECT SUM(PointsScored), LastName
FROM PlayerStats
GROUP BY 2
ORDER BY 2;

Clause COLLATE

COLLATE collate_string

collate_string:
  language_tag[:collation_attribute]

Vous pouvez utiliser la clause COLLATE pour affiner le mode de tri des données spécifié par une clause ORDER BY. Le classement fait référence à un ensemble de règles qui déterminent la manière dont les chaînes sont comparées selon les conventions et les normes d'une langue, d'une région ou d'un pays donnés. Ces règles peuvent définir la séquence de caractères appropriée, avec des options permettant de spécifier la sensibilité à la casse.

Un élément collate_string contient un nom language_tag et peut avoir un attribut collation_attribute facultatif comme suffixe, séparé par un signe deux-points.

language_tag est un paramètre littéral ou de requête:

  • Chaîne de paramètres régionaux standard. Ce nom est généralement constitué de deux ou trois lettres représentant la langue, éventuellement suivies d'un trait de soulignement ou d'un tiret, et de deux lettres représentant la région, par exemple en_US. Ces noms sont définis par le CLDR (Common Locale Data Repository). Consultez la section "Unicode" ci-dessous.

  • und : chaîne de paramètres régionaux représentant les paramètres régionaux indéterminés. Consultez la section "Unicode" ci-dessous.

  • unicode. Pour en savoir plus, consultez les informations ci-dessous.

En plus du nom language_tag, une clause collate_string peut avoir un suffixe facultatif collation_attribute, séparé par un deux-points. Les valeurs autorisées sont les suivantes : + ci pour les caractères non sensibles à la casse + cs pour les caractères sensibles à la casse (par exemple, "cs" étant la valeur par défaut, le fait de le spécifier n'a jamais d'effet).

Classement Unicode

Pour les éléments language_tag autres que unicode, Cloud Spanner SQL suit l'algorithme de classement Unicode. La norme définit le format des tags de langue, qui inclut des extensions utiles ainsi que l'algorithme utilisé pour la comparaison.

und est un tag de langue spécial défini dans le registre de sous-tags de langue IANA et utilisé pour indiquer des paramètres régionaux non déterminés. Également appelé paramètres régionaux root, le classement Unicode par défaut peut être considéré comme lui. Il définit un classement raisonnablement indépendant des paramètres régionaux. Il diffère considérablement de unicode.

language_tag peut être étendu en ajoutant -u-<extension>. Par exemple, l'extension permettant de spécifier l'ordre numérique est kn-true. Ainsi, en-us-u-kn-true correspond aux paramètres régionaux en anglais américain, avec un tri numérique (abc1 est considéré comme inférieur à abc12). Voici quelques exemples utiles d'extensions:

Extension Nom Exemple
-ks-level2 Non sensible à la casse "a1" < "A2"
-ks-level1 Sensibles à la casse et non sensibles à la casse "ä1" < "a2" < "A3"
-ks-level1-kc-true Non sensible à l'accent "ä1" < "a2"
-kn-true Ordre numérique "a1b" < "a12b"

Pour obtenir la liste complète et les détails techniques détaillés, consultez la partie 5 du langage de balisage de données locales Unicode: classement.

Mises en garde :

  • Les chaînes différentes peuvent être considérées comme égales : par exemple, ẞ (LATIN CAPITAL LETTER SHARP S) est considéré comme étant égal à "SS" au niveau du nœud principal, donc "ẞ1" < "SS2". Ce fonctionnement est semblable à celui de l'insensibilité à la casse.

  • Points de code pouvant être ignorés: le classement Unicode spécifie un large éventail de points de code généralement traités comme s'ils n'y étaient pas. Ainsi, les chaînes avec et sans elles sont triées de manière identique, par exemple U2060 : WORD JOINER.

      SELECT "oran\u2060ge1" UNION ALL SELECT "\u2060orange2" UNION ALL SELECT "orange3"
      ORDER BY 1 COLLATE "und"
      +---------+
      |         |
      +---------+
      | orange1 |
      | orange2 |
      | orange3 |
      +---------+
    
  • L'ordre peut changer: Unicode modifie occasionnellement le classement par défaut ("und") qui, dans de rares cas, peut modifier l'ordre relatif des chaînes. Les ordres de tri pour les langages autres que "und" changent plus fréquemment à mesure que les standards changent ou que de nouvelles informations sont collectées. Si un ordre de tri fixe est requis, utilisez unicode.

En outre, un language_tag de unicode est accepté:

  • unicode: renvoie les données dans l'ordre des points de code Unicode, ce qui est identique au comportement d'ordonnancement lorsque COLLATE n'est pas utilisé. L'ordre de tri sera en grande partie arbitraire pour les utilisateurs humains.
  • unicode:cs: identique à unicode
  • unicode:ci: identique à und:ci

Exemples

Classer les résultats à l'aide du code de langue Anglais–Canada :

SELECT Place
FROM Locations
ORDER BY Place COLLATE "en_CA"

Classer les résultats à l'aide d'un paramètre :

#@collate_param = "arg_EG"
SELECT Place
FROM Locations
ORDER BY Place COLLATE @collate_param

Utiliser plusieurs clauses COLLATE dans une instruction :

SELECT APlace, BPlace, CPlace
FROM Locations
ORDER BY APlace COLLATE "en_US" ASC,
         BPlace COLLATE "ar_EG" DESC,
         CPlace COLLATE "en" DESC

Classement insensible à la casse :

SELECT Place
FROM Locations
ORDER BY Place COLLATE "en_US:ci"

Classement insensible à la casse Unicode par défaut :

SELECT Place
FROM Locations
ORDER BY Place COLLATE "und:ci"

Opérateurs d'ensemble

set_operation:
  query_expr set_operator query_expr

set_operator:
  UNION { ALL | DISTINCT } | INTERSECT { ALL | DISTINCT } | EXCEPT { ALL | DISTINCT }

Les opérateurs d'ensemble combinent les résultats de deux requêtes d'entrée ou plus dans un seul ensemble de résultats. Vous devez spécifier ALL ou DISTINCT. Si vous spécifiez ALL, toutes les lignes sont conservées. Si DISTINCT est spécifié, les lignes en double sont supprimées.

Si une ligne R donnée apparaît exactement m fois dans la première requête d'entrée et n fois dans la deuxième requête d'entrée (m >= 0, n >= 0) :

  • Pour UNION ALL, R apparaît exactement m + n fois dans le résultat.
  • Pour INTERSECT ALL, R apparaît exactement MIN(m, n) fois dans le résultat.
  • Pour EXCEPT ALL, R apparaît exactement MAX(m - n, 0) fois dans le résultat.
  • Pour UNION DISTINCT, la valeur DISTINCT est calculée après celle de UNION. R apparaît donc exactement une fois.
  • Pour INTERSECT DISTINCT, DISTINCT est calculé après le résultat ci-dessus.
  • Pour EXCEPT DISTINCT, la ligne R apparaît une fois dans la sortie si m > 0 et n = 0.
  • S'il y a plus de deux requêtes d'entrée, les opérations ci-dessus se généralisent et le résultat est le même que si les entrées étaient combinées de manière incrémentielle de la gauche vers la droite.

Les règles suivantes s'appliquent :

  • Pour les opérations d'ensemble autres que UNION ALL, tous les types de colonnes doivent permettre la comparaison d'égalité.
  • Les requêtes d'entrée de chaque côté de l'opérateur doivent renvoyer le même nombre de colonnes.
  • Les opérateurs associent les colonnes renvoyées par chaque requête d'entrée en fonction des positions des colonnes dans leurs listes SELECT respectives. Autrement dit, la première colonne de la première requête d'entrée est associée à la première colonne de la deuxième requête d'entrée.
  • L'ensemble de résultats utilise toujours les noms de colonnes de la première requête d'entrée.
  • L'ensemble de résultats utilise toujours les supertypes des types d'entrées dans les colonnes correspondantes. Par conséquent, les colonnes associées doivent également avoir le même type de données ou un supertype commun.
  • Vous devez utiliser des parenthèses pour séparer les différentes opérations d'ensemble. À cet effet, les opérations d'ensemble telles que UNION ALL et UNION DISTINCT sont différentes. Si l'instruction ne fait que répéter la même opération d'ensemble, les parenthèses ne sont pas nécessaires.

Par exemple :

query1 UNION ALL (query2 UNION DISTINCT query3)
query1 UNION ALL query2 UNION ALL query3

Incorrect :

query1 UNION ALL query2 UNION DISTINCT query3
query1 UNION ALL query2 INTERSECT ALL query3;  // INVALID.

UNION

L'opérateur UNION associe les ensembles de résultats de deux requêtes d'entrée ou plus en associant des colonnes de l'ensemble de résultats de chaque requête et en les concaténant verticalement.

INTERSECT

L'opérateur INTERSECT renvoie les lignes trouvées dans les ensembles de résultats des requêtes d'entrée de gauche et de droite. Contrairement à EXCEPT, le positionnement des requêtes d'entrée (à gauche et à droite de l'opérateur INTERSECT) n'a pas d'importance.

EXCEPT

L'opérateur EXCEPT renvoie les lignes de la requête d'entrée de gauche qui ne figurent pas dans la requête d'entrée de droite.

Exemple :

SELECT * FROM UNNEST(ARRAY<int64>[1, 2, 3]) AS number
EXCEPT DISTINCT SELECT 1;

+--------+
| number |
+--------+
| 2      |
| 3      |
+--------+

Clauses LIMIT et OFFSET

LIMIT count [ OFFSET skip_rows ]

LIMIT spécifie une valeur count non négative de type INT64, et pas plus de count lignes seront renvoyées. LIMIT 0 renvoie 0 ligne.

En cas d'opération d'ensemble, LIMIT est appliqué après l'évaluation de l'opération d'ensemble.

OFFSET spécifie un nombre non négatif de lignes à ignorer avant l'application de LIMIT. skip_rows est de type INT64.

Ces clauses n'acceptent que les valeurs littérales ou de paramètres. Les lignes renvoyées par LIMIT et OFFSET ne sont pas spécifiées, sauf si ces opérateurs sont utilisés après ORDER BY.

Exemples :

SELECT *
FROM UNNEST(ARRAY<STRING>['a', 'b', 'c', 'd', 'e']) AS letter
ORDER BY letter ASC LIMIT 2

+---------+
| letter  |
+---------+
| a       |
| b       |
+---------+
SELECT *
FROM UNNEST(ARRAY<STRING>['a', 'b', 'c', 'd', 'e']) AS letter
ORDER BY letter ASC LIMIT 3 OFFSET 1

+---------+
| letter  |
+---------+
| b       |
| c       |
| d       |
+---------+

Clause WITH

WITH cte[, ...]

Une clause WITH contient une ou plusieurs expressions de table courantes (CTE). Une CTE agit comme une table temporaire que vous pouvez référencer dans une seule expression de requête. Chaque CTE lie les résultats d'une sous-requête à un nom de table qui peut être utilisé ailleurs dans la même expression de requête, mais des règles s'appliquent.

CTE

cte:
    cte_name AS ( query_expr )

Une expression de table commune (CTE) contient une sous-requête et un nom associé.

  • Une CTE ne peut pas se référencer elle-même.
  • Une CTE peut être référencée par l'expression de requête qui contient la clause WITH, mais des règles s'appliquent.
Exemples

Dans cet exemple, une clause WITH définit deux CTE référencées dans l'opération d'ensemble associée, où une CTE est référencée par chacune des expressions de requête d'entrée de l'opération d'ensemble :

WITH subQ1 AS (SELECT SchoolID FROM Roster),
     subQ2 AS (SELECT OpponentID FROM PlayerStats)
SELECT * FROM subQ1
UNION ALL
SELECT * FROM subQ2

WITH n'est pas autorisé dans une sous-requête. Cela renvoie une erreur :

SELECT account
FROM (
  WITH result AS (SELECT * FROM NPCs)
  SELECT *
  FROM result)

La clause WITH n'est pas acceptée dans les instructions LMD.

Les tables temporaires définies par la clause WITH sont stockées en mémoire. Cloud Spanner SQL alloue de manière dynamique de la mémoire pour toutes les tables temporaires créées par une requête. Si les ressources disponibles ne sont pas suffisantes, la requête échoue.

Règles et contraintes CTE

Les expressions de table courantes (CTE) peuvent être référencées dans l'expression de requête contenant la clause WITH.

Voici quelques règles et contraintes générales à prendre en compte lorsque vous utilisez des CTE :

  • Chaque CTE de la même clause WITH doit avoir un nom unique.
  • Une CTE définie dans une clause WITH n'est visible que par les autres CTE de la même clause WITH, définies après celle-ci.
  • Une CTE locale remplace une CTE externe ou une table portant le même nom.
  • Une CTE sur une sous-requête ne peut pas faire référence à des colonnes corrélées à partir de la requête externe.

Visibilité CTE

Les références entre les expressions de table (CTE) courantes dans la clause WITH peuvent être renvoyées en arrière, mais pas en avant.

C'est le cas lorsque deux CTE se font référence mutuellement ou dans une clause WITH. Supposons que A soit la première CTE et que B soit la deuxième CTE de la clause :

  • A fait référence à A = non valide
  • A fait référence à B = non valide
  • B fait référence à A = valide
  • A fait référence à B qui fait référence à A = non valide (les cycles ne sont pas autorisés)

Cela génère une erreur. A ne peut pas se référencer elle-même, car les autoréférences ne sont pas compatibles :

WITH
  A AS (SELECT 1 AS n UNION ALL (SELECT n + 1 FROM A WHERE n < 3))
SELECT * FROM A

-- Error

Cela génère une erreur. A ne peut pas faire référence à B, car les références entre CTE peuvent être renvoyées en arrière, mais pas en avant :

WITH
  A AS (SELECT * FROM B),
  B AS (SELECT 1 AS n)
SELECT * FROM B

-- Error

B peut faire référence à A, car les références entre CTE peuvent renvoyer en arrière :

WITH
  A AS (SELECT 1 AS n),
  B AS (SELECT * FROM A)
SELECT * FROM B

+---+
| n |
+---+
| 1 |
+---+

Cela génère une erreur. A et B se font référence mutuellement, ce qui crée un cycle :

WITH
  A AS (SELECT * FROM B),
  B AS (SELECT * FROM A)
SELECT * FROM B

-- Error

Utiliser des alias

Un alias est un nom temporaire attribué à une table, une colonne ou une expression présente dans une requête. Vous pouvez introduire des alias explicites dans la liste SELECT ou la clause FROM, ou bien Cloud Spanner SQL déduira un alias implicite pour certaines expressions. Les expressions sans alias explicite ou implicite sont anonymes et la requête ne peut pas y faire référence par leur nom.

Alias explicites

Vous pouvez introduire des alias explicites dans la clause FROM ou dans la liste SELECT.

Dans une clause FROM, vous pouvez introduire des alias explicites pour tout élément, y compris les tables, les tableaux, les sous-requêtes et les clauses UNNEST, à l'aide d'un [AS] alias. Le mot clé AS est facultatif.

Exemple :

SELECT s.FirstName, s2.SongName
FROM Singers AS s, (SELECT * FROM Songs) AS s2;

Vous pouvez introduire des alias explicites pour toute expression de la liste SELECT en utilisant un [AS] alias. Le mot clé AS est facultatif.

Exemple :

SELECT s.FirstName AS name, LOWER(s.FirstName) AS lname
FROM Singers s;

Alias implicites

Dans la liste SELECT, s'il existe une expression ne comportant pas d'alias explicite, Cloud Spanner SQL attribue un alias implicite conformément aux règles suivantes. La liste SELECT peut contenir plusieurs colonnes avec le même alias.

  • Dans les identifiants, l'alias fait office d'identifiant. Par exemple, SELECT abc implique AS abc.
  • Dans les expressions de chemin, l'alias est le dernier identifiant du chemin. Par exemple, SELECT abc.def.ghi implique AS ghi.
  • Dans l'accès aux champs à l'aide de l'opérateur d'accès au champ membre "dot", l'alias est le nom du champ. Par exemple, SELECT (struct_function()).fname implique AS fname.

Dans tous les autres cas, il n'existe pas d'alias implicite, la colonne est donc anonyme et ne peut pas être référencée par nom. Les données de cette colonne seront tout de même renvoyées et les résultats de la requête affichés peuvent inclure un libellé généré pour cette colonne, mais celui-ci ne pourra pas être utilisé comme alias.

Dans une clause FROM, les éléments from_item ne sont pas obligatoires pour obtenir un alias. Les règles suivantes s'appliquent :

  • Si une expression sans alias explicite existe, Cloud Spanner SQL attribue un alias implicite dans les cas suivants :
    • Dans les identifiants, l'alias fait office d'identifiant. Par exemple, FROM abc implique AS abc.
    • Dans les expressions de chemin, l'alias est le dernier identifiant du chemin. Par exemple, FROM abc.def.ghi implique AS ghi.
    • La colonne produite à l'aide de WITH OFFSET comporte l'alias implicite offset.
  • Les sous-requêtes de table n'ont pas d'alias implicite.
  • FROM UNNEST(x) ne possède pas d'alias implicite.

Visibilité des alias

Une fois que vous avez introduit un alias explicite dans une requête, des restrictions s'appliquent aux autres endroits où vous pouvez y faire référence. Ces restrictions sur la visibilité des alias sont le résultat des règles de champ d'application des noms Cloud Spanner SQL.

Visibilité dans la clause FROM

Cloud Spanner SQL traite les alias dans une clause FROM de gauche à droite, et les alias ne sont visibles que pour les expressions de chemin ultérieures dans une clause FROM.

Exemple :

Supposons que la table Singers contienne une colonne Concerts de type ARRAY.

SELECT FirstName
FROM Singers AS s, s.Concerts;

Incorrect :

SELECT FirstName
FROM s.Concerts, Singers AS s;  // INVALID.

Les alias de la clause FROM ne sont pas visibles pour les sous-requêtes d'une même clause FROM. Les sous-requêtes d'une clause FROM ne peuvent pas contenir de références corrélées à d'autres tables de la même clause FROM.

Incorrect :

SELECT FirstName
FROM Singers AS s, (SELECT (2020 - ReleaseDate) FROM s)  // INVALID.

Vous pouvez utiliser n'importe quel nom de colonne d'une table de FROM comme alias n'importe où dans la requête, avec ou sans qualification de nom de table.

Exemple :

SELECT FirstName, s.ReleaseDate
FROM Singers s WHERE ReleaseDate = 1975;

Si la clause FROM contient un alias explicite, vous devez l'utiliser à la place de l'alias implicite pour le reste de la requête (consultez la section Alias implicites). Un alias de table est utile par souci de brièveté ou pour éliminer toute ambiguïté ; en cas d'autojointures par exemple, lorsque la même table est analysée plusieurs fois au cours du traitement de la requête.

Exemple :

SELECT * FROM Singers as s, Songs as s2
ORDER BY s.LastName

Incorrect : la clause ORDER BY n'utilise pas l'alias de table :

SELECT * FROM Singers as s, Songs as s2
ORDER BY Singers.LastName;  // INVALID.

Visibilité dans la liste SELECT

Les alias de la liste SELECT ne sont visibles que pour les clauses suivantes :

  • Clause GROUP BY
  • Clause ORDER BY
  • Clause HAVING

Exemple :

SELECT LastName AS last, SingerID
FROM Singers
ORDER BY last;

Visibilité dans les clauses GROUP BY, ORDER BY et HAVING

Ces trois clauses, GROUP BY, ORDER BY et HAVING, ne peuvent faire référence qu'aux valeurs suivantes :

  • Aux tables de la clause FROM et à toutes leurs colonnes
  • Aux alias de la liste SELECT

GROUP BY et ORDER BY peuvent également faire référence à un troisième groupe :

  • Les littéraux entiers, qui font référence aux éléments de la liste SELECT. Le nombre entier 1 fait référence au premier élément de la liste SELECT, 2 au deuxième élément, etc.

Exemple :

SELECT SingerID AS sid, COUNT(Songid) AS s2id
FROM Songs
GROUP BY 1
ORDER BY 2 DESC;

La requête précédente est équivalente à :

SELECT SingerID AS sid, COUNT(Songid) AS s2id
FROM Songs
GROUP BY sid
ORDER BY s2id DESC;

Alias en double

Une sous-requête ou une liste SELECT contenant plusieurs alias explicites ou implicites du même nom est autorisée, à condition que le nom de l'alias ne soit pas référencé ailleurs dans la requête, car la référence serait ambiguë.

Exemple :

SELECT 1 AS a, 2 AS a;

+---+---+
| a | a |
+---+---+
| 1 | 2 |
+---+---+

Alias ambigus

Cloud Spanner SQL génère une erreur si l'accès à un nom est ambigu, ce qui signifie qu'il peut être résolu en plusieurs objets uniques dans la requête ou dans un schéma de table, y compris le schéma d'une table de destination.

Par exemple :

Cette requête contient des noms de colonne de table en conflit, car Singers et Songs ont tous les deux une colonne nommée SingerID :

SELECT SingerID
FROM Singers, Songs;

Cette requête contient des alias qui sont ambigus dans la clause GROUP BY, car ils sont dupliqués dans la liste SELECT :

SELECT FirstName AS name, LastName AS name,
FROM Singers
GROUP BY name;

Cette requête contient des alias qui sont ambigus dans la liste SELECT et dans la clause FROM, car ils portent le même nom. Supposons que table comporte les colonnes x, y et z. z est de type STRUCT et comporte les champs v, w et x.

Exemple :

SELECT x, z AS T
FROM table AS T
GROUP BY T.x;

L'alias T est ambigu et génère une erreur, car T.x dans la clause GROUP BY peut faire référence à table.x ou table.z.x.

Un nom n'est pas ambigu dans GROUP BY, ORDER BY ou HAVING s'il s'agit à la fois d'un nom de colonne et d'un alias de liste SELECT, à condition que le nom soit résolu sous la forme d'un objet sous-jacent identique.

Exemple :

SELECT LastName, BirthYear AS BirthYear
FROM Singers
GROUP BY BirthYear;

L'alias BirthYear n'est pas ambigu, car il renvoie vers la même colonne sous-jacente, Singers.BirthYear.

Variables de plage

Dans Cloud Spanner SQL, une variable de plage est un alias d'expression de table dans la clause FROM. Parfois, une variable de plage est appelée table alias. Une variable de plage vous permet de faire référence aux lignes analysées à partir d'une expression de table. Une expression de table représente un élément de la clause FROM qui renvoie une table. Les éléments communs que cette expression peut représenter incluent des tables, des tables de valeurs, des sous-requêtes, des jointures et des jointures entre parenthèses.

En général, une variable de plage fournit une référence aux lignes d'une expression de table. Une variable de plage peut être utilisée pour qualifier une référence à une colonne et identifier sans ambiguïté la table associée, par exemple range_variable.column_1.

Lorsque vous faites référence à une variable de plage seule sans suffixe de colonne spécifié, le résultat d'une expression de table correspond au type de ligne de la table associée. Les tables de valeurs possèdent des types de lignes explicites. Pour les variables de plage liées aux tables de valeurs, le type de résultat correspond au type de ligne de la table de valeurs. Les autres tables n'ont pas de types de lignes explicites. Pour ces tables, le type de variable de plage est STRUCT, qui est défini de manière dynamique et inclut toutes les colonnes de la table.

Exemples

Dans ces exemples, la clause WITH est utilisée pour émuler une table temporaire nommée Grid. Cette table contient les colonnes x et y. Une variable de plage nommée Coordinate fait référence à la ligne actuelle lorsque la table est analysée. Coordinate permet d'accéder à toute la ligne ou aux colonnes de la ligne.

L'exemple suivant sélectionne la colonne x de la variable de plage Coordinate, qui sélectionne en fait la colonne x de la table Grid.

WITH Grid AS (SELECT 1 x, 2 y)
SELECT Coordinate.x FROM Grid AS Coordinate;

+---+
| x |
+---+
| 1 |
+---+

L'exemple suivant sélectionne toutes les colonnes de la variable de plage Coordinate, qui sélectionne en fait toutes les colonnes de la table Grid.

WITH Grid AS (SELECT 1 x, 2 y)
SELECT Coordinate.* FROM Grid AS Coordinate;

+---+---+
| x | y |
+---+---+
| 1 | 2 |
+---+---+

L'exemple suivant sélectionne la variable de plage Coordinate, qui est une référence aux lignes de la table Grid. Étant donné que Grid n'est pas une table de valeurs, le résultat de Coordinate est un type STRUCT contenant toutes les colonnes de Grid.

WITH Grid AS (SELECT 1 x, 2 y)
SELECT Coordinate FROM Grid AS Coordinate;

+--------------+
| Coordinate   |
+--------------+
| {x: 1, y: 2} |
+--------------+

Annexe A : illustrations avec des exemples de données

Ces exemples incluent des instructions qui effectuent des requêtes sur les tables Roster, TeamMascot et PlayerStats.

Clause GROUP BY

Exemple :

SELECT LastName, SUM(PointsScored)
FROM PlayerStats
GROUP BY LastName;
LastName SUM
Adams 7
Buchanan 13
Coolidge 1

UNION

L'opérateur UNION combine les ensembles de résultats d'au moins deux instructions SELECT en associant des colonnes de l'ensemble de résultats de chaque instruction SELECT et en les concaténant verticalement.

Exemple :

SELECT Mascot AS X, SchoolID AS Y
FROM TeamMascot
UNION ALL
SELECT LastName, PointsScored
FROM PlayerStats;

Résultats :

X Y
Jaguars 50
Knights 51
Lakers 52
Mustangs 53
Adams 3
Buchanan 0
Coolidge 1
Adams 4
Buchanan 13

INTERSECT

Cette requête renvoie les noms de famille présents dans Roster et PlayerStats.

SELECT LastName
FROM Roster
INTERSECT ALL
SELECT LastName
FROM PlayerStats;

Résultats :

LastName
Adams
Coolidge
Buchanan

EXCEPT

La requête ci-dessous renvoie les noms de famille de la liste qui ne sont pas présents dans PlayerStats.

SELECT LastName
FROM Roster
EXCEPT DISTINCT
SELECT LastName
FROM PlayerStats;

Résultats :

LastName
Eisenhower
Davis

Si vous inversez l'ordre des instructions SELECT, les noms présents dans PlayerStats et non présents dans Roster seront renvoyés :

SELECT LastName
FROM PlayerStats
EXCEPT DISTINCT
SELECT LastName
FROM Roster;

Résultats :

(empty)