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 en langage SQL standard de Google pour BigQuery.

Syntaxe SQL

query_statement:
    query_expr

query_expr:
    [ WITH [ RECURSIVE ] { non_recursive_cte | recursive_cte }[, ...] ]
    { select | ( query_expr ) | set_operation }
    [ ORDER BY expression [{ ASC | DESC }] [, ...] ]
    [ LIMIT count [ OFFSET skip_rows ] ]

select:
    SELECT
        [ { ALL | DISTINCT } ]
        [ AS { STRUCT | VALUE } ]
        select_list
    [ FROM from_clause[, ...] ]
    [ WHERE bool_expression ]
    [ GROUP BY { expression [, ...] | ROLLUP ( expression [, ...] ) } ]
    [ HAVING bool_expression ]
    [ QUALIFY bool_expression ]
    [ WINDOW window_clause ]

Règles de notation

Les crochets [ ] indiquent les clauses facultatives.
Les parenthèses ( ) correspondent à des parenthèses littérales.
La barre verticale | correspond à un opérateur OR logique.
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.

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

Instruction SELECT

SELECT
    [ { ALL | DISTINCT } ]
    [ AS { STRUCT | VALUE } ]
   select_list

select_list:
    { select_all | select_expression } [, ...]

select_all:
    [ expression. ]*
    [ EXCEPT ( column_name [, ...] ) ]
    [ REPLACE ( expression [ AS ] column_name [, ...] ) ]

select_expression:
    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 |
+---------+------------+

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      |
+----------+-----------+----------+

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.

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.

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 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>.

Clause FROM

FROM from_clause[, ...]

from_clause:
    from_item
    [ { pivot_operator | unpivot_operator } ]
    [ tablesample_operator ]

from_item:
    {
      table_name [ as_alias ] [ FOR SYSTEM_TIME AS OF timestamp_expression ]
      | { join_operation | ( join_operation ) }
      | ( query_expr ) [ as_alias ]
      | field_path
      | unnest_operator
      | cte_name [ as_alias ]
    }

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.

pivot_operator

Consultez la section Opérateur PIVOT.

unpivot_operator

Consultez la section Opérateur UNPIVOT.

tablesample_operator

Consultez la section Opérateur TABLESAMPLE.

table_name

Nom (éventuellement qualifié) d'une table existante.

SELECT * FROM Roster;
SELECT * FROM dataset.Roster;
SELECT * FROM project.dataset.Roster;

FOR SYSTEM_TIME AS OF

FOR SYSTEM_TIME AS OF fait référence aux versions historiques de la définition de table et aux lignes en cours par rapport à timestamp_expression.

Limites :

La table source de la clause FROM contenant FOR SYSTEM_TIME AS OF ne doit pas être l'une des suivantes :

Une analyse de tableau (ARRAY), y compris de tableau aplati, ou la sortie de l'opérateur UNNEST
Une expression de table commune définie par une clause WITH

timestamp_expression doit être une expression constante. Elle ne peut pas contenir les éléments suivants :

Des sous-requêtes
Des références corrélées (références aux colonnes d'une table qui apparaissent à un niveau supérieur de l'instruction de requête, comme dans la liste SELECT)
Des fonctions définies par l'utilisateur

La valeur de timestamp_expression ne peut pas être comprise dans les plages suivantes :

Après l'horodatage actuel (dans le futur)
Plus de sept jours avant l'horodatage actuel.

Une instruction de requête unique ne peut pas faire référence à une table unique à plusieurs périodes, y compris l'heure actuelle. Autrement dit, une requête peut faire référence à une table plusieurs fois au même horodatage, mais pas à la version actuelle avec une version historique, ni à deux versions historiques différentes.

Exemples :

La requête ci-dessous renvoie une version historique de la table d'il y a une heure.

SELECT *
FROM t
  FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);

La requête ci-dessous renvoie une version historique de la table à un moment précis.

SELECT *
FROM t
  FOR SYSTEM_TIME AS OF '2017-01-01 10:00:00-07:00';

La requête ci-dessous renvoie une erreur, car timestamp_expression contient une référence corrélée à une colonne de la requête associée.

SELECT *
FROM t1
WHERE t1.a IN (SELECT t2.a
               FROM t2 FOR SYSTEM_TIME AS OF t1.timestamp_column);

Les opérations suivantes indiquent l'accès à une version historique de la table avant son remplacement.

DECLARE before_replace_timestamp TIMESTAMP;

-- Create table books.
CREATE TABLE books AS
SELECT 'Hamlet' title, 'William Shakespeare' author;

-- Get current timestamp before table replacement.
SET before_replace_timestamp = CURRENT_TIMESTAMP();

-- Replace table with different schema(title and release_date).
CREATE OR REPLACE TABLE books AS
SELECT 'Hamlet' title, DATE '1603-01-01' release_date;

-- This query returns Hamlet, William Shakespeare as result.
SELECT * FROM books FOR SYSTEM_TIME AS OF before_replace_timestamp;

Les opérations suivantes indiquent l'accès à une version historique de la table avant une tâche LMD.

DECLARE JOB_START_TIMESTAMP TIMESTAMP;

-- Create table books.
CREATE OR REPLACE TABLE books AS
SELECT 'Hamlet' title, 'William Shakespeare' author;

-- Insert two rows into the books.
INSERT books (title, author)
VALUES('The Great Gatsby', 'F. Scott Fizgerald'),
      ('War and Peace', 'Leo Tolstoy');

SELECT * FROM books;

SET JOB_START_TIMESTAMP = (
  SELECT start_time
  FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_USER
  WHERE job_type="QUERY"
    AND statement_type="INSERT"
  ORDER BY start_time DESC
  LIMIT 1
 );

-- This query only returns Hamlet, William Shakespeare as result.
SELECT * FROM books FOR SYSTEM_TIME AS OF JOB_START_TIMESTAMP;

La requête suivante renvoie une erreur car le LMD travaille sur la version actuelle de la table et sur une version historique de la table datant d'il y a un jour.

INSERT INTO t1
SELECT * FROM t1
  FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY);

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 :

dataset.Roster ou project.dataset.Roster.

Opérateur UNNEST

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

as_alias:
    [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.

Pour en savoir plus sur l'utilisation de l'élément UNNEST, y compris en cas de construction, d'aplatissement et de filtrage, consultez la page Utiliser des tableaux.

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,
      z STRUCT<a INT64, b INT64>>>[
        (1, 'foo', (10, 11)),
        (3, 'bar', (20, 21))]);

+---+-----+----------+
| x | y   | z        |
+---+-----+----------+
| 1 | foo | {10, 11} |
| 3 | bar | {20, 21} |
+---+-----+----------+

É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 valeurs NULL comme suit :

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

UNNEST et WITH OFFSET

La clause WITH OFFSET facultative renvoie une colonne distincte contenant la valeur offset, dans laquelle le comptage commence à zéro pour chaque ligne générée par l'opération UNNEST. Cette colonne comporte un alias facultatif. Si l'alias facultatif n'est pas utilisé, le nom de la colonne par défaut est offset.

Exemple :

SELECT * FROM UNNEST ([10,20,30]) as numbers WITH OFFSET;

+---------+--------+
| numbers | offset |
+---------+--------+
| 10      | 0      |
| 20      | 1      |
| 30      | 2      |
+---------+--------+

Opérateur PIVOT

FROM from_item[, ...] pivot_operator

pivot_operator:
    PIVOT(
        aggregate_function_call [as_alias][, ...]
        FOR input_column
        IN ( pivot_column [as_alias][, ...] )
    ) [AS alias]

as_alias:
    [AS] alias

L'opérateur PIVOT fait pivoter les lignes pour en faire des colonnes, à l'aide de l'agrégation. PIVOT fait partie de la clause FROM.

PIVOT permet de modifier n'importe quelle expression de table.
Il n'est pas possible de combiner PIVOT avec FOR SYSTEM_TIME AS OF, bien que les utilisateurs puissent utiliser l'opérateur PIVOT avec une entrée de sous-requête qui utilise elle FOR SYSTEM_TIME AS OF.
Une clause WITH OFFSET qui précède immédiatement l'opérateur PIVOT n'est pas autorisée.

Exemple conceptuel :

-- Before PIVOT is used to rotate sales and quarter into Q1, Q2, Q3, Q4 columns:
+---------+-------+---------+------+
| product | sales | quarter | year |
+---------+-------+---------+------|
| Kale    | 51    | Q1      | 2020 |
| Kale    | 23    | Q2      | 2020 |
| Kale    | 45    | Q3      | 2020 |
| Kale    | 3     | Q4      | 2020 |
| Kale    | 70    | Q1      | 2021 |
| Kale    | 85    | Q2      | 2021 |
| Apple   | 77    | Q1      | 2020 |
| Apple   | 0     | Q2      | 2020 |
| Apple   | 1     | Q1      | 2021 |
+---------+-------+---------+------+

-- After PIVOT is used to rotate sales and quarter into Q1, Q2, Q3, Q4 columns:
+---------+------+----+------+------+------+
| product | year | Q1 | Q2   | Q3   | Q4   |
+---------+------+----+------+------+------+
| Apple   | 2020 | 77 | 0    | NULL | NULL |
| Apple   | 2021 | 1  | NULL | NULL | NULL |
| Kale    | 2020 | 51 | 23   | 45   | 3    |
| Kale    | 2021 | 70 | 85   | NULL | NULL |
+---------+------+----+------+------+------+

Définitions

Définitions de premier niveau :

from_item : table ou sous-requête sur laquelle effectuer une opération de pivot. L'élément from_item doit respecter ces règles.
pivot_operator : opération de pivotement à effectuer sur un élément from_item.
alias : alias à utiliser pour un élément de la requête.

Définitions pivot_operator :

aggregate_function_call : appel de fonction d'agrégation qui agrège toutes les lignes d'entrée afin que input_column corresponde à une valeur particulière dans pivot_column. Chaque agrégation correspondant à une valeur pivot_column différente génère une colonne différente dans le résultat. Suivez ces règles lors de la création d'un appel de fonction d'agrégation.
input_column : prend une colonne et récupère les valeurs des lignes pour la colonne, en suivant ces règles.
pivot_column : colonne de pivot à créer pour chaque appel de fonction d'agrégation. Si aucun alias n'est fourni, un alias par défaut est créé. Le type de valeur de la colonne de pivot doit correspondre au type de valeur spécifié dans input_column, de sorte que les valeurs puissent être comparées. Il est possible qu'une valeur dans pivot_column ne corresponde pas à une valeur dans input_column. Cette option doit être une constante et doit suivre ces règles.

Règles

Règles applicables à la transmission d'un élément from_item à PIVOT :

L'élément from_item peut être constitué de n'importe quel résultat de table ou de sous-requête.
Il se peut que l'élément from_item ne produise pas de table de valeurs.
L'élément from_item peut ne pas être une sous-requête utilisant SELECT AS STRUCT.

Règles applicables à aggregate_function_call :

Il doit s'agir d'une fonction d'agrégation. Par exemple, SUM.
Vous pouvez faire référence à des colonnes d'une table transmise à PIVOT, ainsi qu'à des colonnes corrélées, mais vous risquez de ne pas pouvoir accéder à des colonnes définies par la clause PIVOT elle-même.
Il est possible d'accéder à une table transmise à PIVOT via son alias, si celui-ci est fourni.
Vous ne pouvez utiliser qu'une fonction d'agrégation qui utilise un seul argument.
À l'exception de COUNT, vous ne pouvez utiliser que des fonctions d'agrégation qui ignorent les entrées NULL.
Si vous utilisez COUNT, vous pouvez utiliser * en tant qu'argument.

Règles applicables à input_column :

Peut accéder aux colonnes de la table d'entrée, ainsi qu'aux colonnes corrélées, mais pas aux colonnes définies par la clause PIVOT elle-même.
Évaluée à chaque ligne de la table d'entrée ; les appels de fonction d'agrégation et de fenêtrage sont interdits.
Le non-déterminisme est acceptable.
Le type de données doit être un type groupable.
L'accès à la table d'entrée est possible via son alias, si celui-ci est fourni.

Règles applicables à pivot_column :

Une pivot_column doit être une constante.
Les constantes nommées, telles que les variables, ne sont pas acceptées.
Les paramètres de requête ne sont pas acceptés.
Si un nom est requis pour une constante nommée ou un paramètre de requête, spécifiez-le explicitement avec un alias.
Il existe des cas critiques dans lesquels des pivot_column distinctes peuvent se retrouver avec les mêmes noms de colonne par défaut. Par exemple, une colonne d'entrée peut contenir à la fois une valeur NULL et le littéral de chaîne "NULL". Dans ce cas, plusieurs colonnes de pivot sont créées avec le même nom. Pour éviter cette situation, utilisez des alias pour les noms de colonnes de pivot.
Si une pivot_column ne spécifie pas d'alias, le nom de colonne va être construit comme suit :

De	To	Exemple
NULL	NULL	Entrée : NULL Sortie : "NULL"
INT64 NUMERIC BIGNUMERIC	Nombre au format chaîne respectant les règles suivantes : Les nombres positifs sont précédés du préfixe `_`. Les nombres négatifs sont précédés du préfixe `minus_`. Un séparateur décimal est remplacé par `_point_`.	Entrée : 1 Sortie : _1 Entrée : -1 Sortie : minus_1 Entrée : 1.0 Sortie : _1_point_0
BOOL	`TRUE` ou `FALSE`.	Entrée : TRUE Sortie : TRUE Entrée : FALSE Sortie : FALSE
CHAÎNE	Valeur de la chaîne.	Entrée : "PlayerName" Sortie : PlayerName
DATE	Date au format `_YYYY_MM_DD`.	Entrée : DATE '2013-11-25' Sortie : _2013_11_25
ENUM	Nom de la constante d'énumération.	Entrée : COLOR.RED Sortie : RED
STRUCT	Chaîne formée en calculant le nom `pivot_column` pour chaque champ et en regroupant les résultats, séparés par un trait de soulignement. Les règles suivantes s'appliquent : Si le champ est nommé : `<field_name>_<pivot_column_name_for_field_name>`. Si le champ n'a pas de nom : `<pivot_column_name_for_field_name>`. `<pivot_column_name_for_field_name>` est déterminé par l'application des règles de cette table de manière récursive. Si aucune règle n'est disponible pour un champ `STRUCT`, la colonne de pivot dans son ensemble n'a pas de nom. En raison de la coercition de type implicite entre les valeurs de la liste `IN` et le type de `<value-expression>`, les noms de champs doivent être présents dans `input_column` pour avoir un effet sur les noms des colonnes de pivot.	Entrée : STRUCT("one", "two") Sortie : one_two Entrée : STRUCT("one" AS a, "two" AS b) Sortie : one_a_two_b
Tous les autres types de données	Non compatible. Vous devez indiquer un alias.

Exemples

Les exemples suivants font référence à une table appelée Produce qui se présente comme suit :

WITH Produce AS (
  SELECT 'Kale' as product, 51 as sales, 'Q1' as quarter, 2020 as year UNION ALL
  SELECT 'Kale', 23, 'Q2', 2020 UNION ALL
  SELECT 'Kale', 45, 'Q3', 2020 UNION ALL
  SELECT 'Kale', 3, 'Q4', 2020 UNION ALL
  SELECT 'Kale', 70, 'Q1', 2021 UNION ALL
  SELECT 'Kale', 85, 'Q2', 2021 UNION ALL
  SELECT 'Apple', 77, 'Q1', 2020 UNION ALL
  SELECT 'Apple', 0, 'Q2', 2020 UNION ALL
  SELECT 'Apple', 1, 'Q1', 2021)
SELECT * FROM Produce

+---------+-------+---------+------+
| product | sales | quarter | year |
+---------+-------+---------+------|
| Kale    | 51    | Q1      | 2020 |
| Kale    | 23    | Q2      | 2020 |
| Kale    | 45    | Q3      | 2020 |
| Kale    | 3     | Q4      | 2020 |
| Kale    | 70    | Q1      | 2021 |
| Kale    | 85    | Q2      | 2021 |
| Apple   | 77    | Q1      | 2020 |
| Apple   | 0     | Q2      | 2020 |
| Apple   | 1     | Q1      | 2021 |
+---------+-------+---------+------+

Avec l'opérateur PIVOT, les lignes de la colonne quarter sont pivotées pour former ces nouvelles colonnes : Q1, Q2, Q3, Q4. La fonction d'agrégation SUM est implicitement regroupée par toutes les colonnes non agrégées autres que pivot_column : product et year.

SELECT * FROM
  Produce
  PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))

+---------+------+----+------+------+------+
| product | year | Q1 | Q2   | Q3   | Q4   |
+---------+------+----+------+------+------+
| Apple   | 2020 | 77 | 0    | NULL | NULL |
| Apple   | 2021 | 1  | NULL | NULL | NULL |
| Kale    | 2020 | 51 | 23   | 45   | 3    |
| Kale    | 2021 | 70 | 85   | NULL | NULL |
+---------+------+----+------+------+------+

Si vous n'incluez pas year, SUM n'est regroupé que par product.

SELECT * FROM
  (SELECT product, sales, quarter FROM Produce)
  PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))

+---------+-----+-----+------+------+
| product | Q1  | Q2  | Q3   | Q4   |
+---------+-----+-----+------+------+
| Apple   | 78  | 0   | NULL | NULL |
| Kale    | 121 | 108 | 45   | 3    |
+---------+-----+-----+------+------+

Vous pouvez sélectionner un sous-ensemble de valeurs dans pivot_column :

SELECT * FROM
  (SELECT product, sales, quarter FROM Produce)
  PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3'))
+---------+-----+-----+------+
| product | Q1  | Q2  | Q3   |
+---------+-----+-----+------+
| Apple   | 78  | 0   | NULL |
| Kale    | 121 | 108 | 45   |
+---------+-----+-----+------+

SELECT * FROM
  (SELECT sales, quarter FROM Produce)
  PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3'))
+-----+-----+----+
| Q1  | Q2  | Q3 |
+-----+-----+----+
| 199 | 108 | 45 |
+-----+-----+----+

Vous pouvez inclure plusieurs fonctions d'agrégation dans PIVOT. Dans ce cas, vous devez spécifier un alias pour chaque agrégation. Ces alias servent à construire les noms de colonnes de la table résultante.

SELECT * FROM
  (SELECT product, sales, quarter FROM Produce)
  PIVOT(SUM(sales) total_sales, COUNT(*) num_records FOR quarter IN ('Q1', 'Q2'))

+--------+----------------+----------------+----------------+----------------+
|product | total_sales_Q1 | num_records_Q1 | total_sales_Q2 | num_records_Q2 |
+--------+----------------+----------------+----------------+----------------+
| Kale   | 121            | 2              | 108            | 2              |
| Apple  | 78             | 2              | 0              | 1              |
+--------+----------------+----------------+----------------+----------------+

Opérateur UNPIVOT

FROM from_item[, ...] unpivot_operator

unpivot_operator:
    UNPIVOT [ { INCLUDE NULLS | EXCLUDE NULLS } ] (
        { single_column_unpivot | multi_column_unpivot }
    ) [unpivot_alias]

single_column_unpivot:
    values_column
    FOR name_column
    IN (columns_to_unpivot)

multi_column_unpivot:
    values_column_set
    FOR name_column
    IN (column_sets_to_unpivot)

values_column_set:
    (values_column[, ...])

columns_to_unpivot:
    unpivot_column [row_value_alias][, ...]

column_sets_to_unpivot:
    (unpivot_column [row_value_alias][, ...])

unpivot_alias and row_value_alias:
    [AS] alias

L'opérateur UNPIVOT fait pivoter les colonnes pour en faire des lignes. UNPIVOT fait partie de la clause FROM.

UNPIVOT permet de modifier n'importe quelle expression de table.
Il n'est pas possible de combiner UNPIVOT avec FOR SYSTEM_TIME AS OF, bien que les utilisateurs puissent utiliser l'opérateur UNPIVOT avec une entrée de sous-requête qui utilise elle FOR SYSTEM_TIME AS OF.
Une clause WITH OFFSET qui précède immédiatement l'opérateur UNPIVOT n'est pas autorisée.
Les agrégations PIVOT ne peuvent pas être inversées avec UNPIVOT.

Exemple conceptuel :

-- Before UNPIVOT is used to rotate Q1, Q2, Q3, Q4 into sales and quarter columns:
+---------+----+----+----+----+
| product | Q1 | Q2 | Q3 | Q4 |
+---------+----+----+----+----+
| Kale    | 51 | 23 | 45 | 3  |
| Apple   | 77 | 0  | 25 | 2  |
+---------+----+----+----+----+

-- After UNPIVOT is used to rotate Q1, Q2, Q3, Q4 into sales and quarter columns:
+---------+-------+---------+
| product | sales | quarter |
+---------+-------+---------+
| Kale    | 51    | Q1      |
| Kale    | 23    | Q2      |
| Kale    | 45    | Q3      |
| Kale    | 3     | Q4      |
| Apple   | 77    | Q1      |
| Apple   | 0     | Q2      |
| Apple   | 25    | Q3      |
| Apple   | 2     | Q4      |
+---------+-------+---------+

Définitions

Définitions de premier niveau :

from_item : table ou sous-requête sur laquelle effectuer une opération de pivot. L'élément from_item doit respecter ces règles.
unpivot_operator : opération de pivotement à effectuer sur un élément from_item.

Définitions unpivot_operator :

INCLUDE NULLS : ajoute au résultat les lignes comportant des valeurs NULL.
EXCLUDE NULLS : les lignes comportant des valeurs NULL ne sont pas ajoutées au résultat. Par défaut, UNPIVOT exclut les lignes comportant des valeurs NULL.
single_column_unpivot : fait pivoter les colonnes pour en faire une seule values_column et une seule name_column.
multi_column_unpivot : fait pivoter les colonnes pour en faire plusieurs values_column et une seule name_column.
unpivot_alias : alias pour les résultats de l'opération UNPIVOT. Il peut être fait référence à cet alias ailleurs dans la requête.

Définitions single_column_unpivot :

values_column : une colonne contenant les valeurs de ligne de columns_to_unpivot. Suivez ces règles lors de la création d'une colonne de valeurs.
name_column : colonne contenant les noms des colonnes, issus de columns_to_unpivot. Suivez ces règles lors de la création d'une colonne de noms.
columns_to_unpivot : colonnes issues de from_item pour renseigner values_column et name_column. Suivez ces règles lors de la création d'une colonne de pivot inverse.
- row_value_alias : alias facultatif pour une colonne, affiché pour la colonne concernée dans name_column. Si cet alias n'est pas spécifié, la valeur de chaîne du nom de colonne est utilisée. Suivez ces règles lors de la création d'un alias de valeur de ligne.

Définitions multi_column_unpivot :

values_column_set : ensemble de colonnes contenant les valeurs de ligne issues de columns_to_unpivot. Suivez ces règles lors de la création d'une colonne de valeurs.
name_column : ensemble de colonnes contenant les noms de colonne issus de columns_to_unpivot. Suivez ces règles lors de la création d'une colonne de noms.
column_sets_to_unpivot : colonnes issues de from_item à soumettre à pivot inverse. Suivez ces règles lors de la création d'une colonne de pivot inverse.
- row_value_alias : alias facultatif pour un ensemble de colonnes, affiché pour l'ensemble de colonnes concerné dans name_column. Si cet alias n'est pas spécifié, une valeur de chaîne pour l'ensemble de colonnes est utilisée et chaque colonne de la chaîne est séparée par un trait de soulignement (_). Par exemple, (col1, col2) génère col1_col2. Suivez ces règles lors de la création d'un alias de valeur de ligne.

Règles

Règles applicables à la transmission d'un élément from_item à UNPIVOT :

L'élément from_item peut être constitué de n'importe quel résultat de table ou de sous-requête.
Il se peut que l'élément from_item ne produise pas de table de valeurs.
Il n'est pas possible de faire référence, dans la clause UNPIVOT, à des colonnes en double au sein d'un élément from_item.

Règles applicables à unpivot_operator :

Les expressions ne sont pas autorisées.
Les noms qualifiés ne sont pas autorisés. Par exemple, mytable.mycolumn n'est pas autorisé.
Dans le cas où le résultat de UNPIVOT comporte des noms de colonne en double :
- SELECT * est autorisé.
- SELECT values_column crée une ambiguïté.

Règles applicables à values_column :

Il ne peut pas s'agir d'un nom utilisé pour name_column ou unpivot_column.
Il peut s'agir du même nom qu'une colonne issue de l'élément from_item.

Règles applicables à name_column :

Il ne peut pas s'agir d'un nom utilisé pour values_column ou unpivot_column.
Il peut s'agir du même nom qu'une colonne issue de l'élément from_item.

Règles applicables à unpivot_column :

Il doit s'agir d'un nom de colonne issu de l'élément from_item.
Il ne peut pas faire référence à des noms de colonne from_item en double.
Toutes les colonnes d'un ensemble de colonnes doivent présenter des types de données équivalents.
- Les types de données ne peuvent pas être forcés en un supertype commun.
- Si les types de données sont des correspondances exactes (par exemple, une structure avec des noms de champ différents), le type de données de la première entrée est le type de données de la sortie.
Vous ne pouvez pas utiliser le même nom dans le même ensemble de colonnes. Par exemple, (emp1, emp1) génère une erreur.
Un même nom peut coexister dans différents ensembles de colonnes. Par exemple, (emp1, emp2), (emp1, emp3) est valide.

Règles applicables à row_value_alias :

Il peut s'agir d'un littéral STRING ou INT64.
Le type de données de toutes les clauses row_value_alias doit être identique.
Si la valeur est un littéral INT64, le row_value_alias pour chaque unpivot_column doit être spécifié.

Exemples

Les exemples suivants font référence à une table appelée Produce qui se présente comme suit :

WITH Produce AS (
  SELECT 'Kale' as product, 51 as Q1, 23 as Q2, 45 as Q3, 3 as Q4 UNION ALL
  SELECT 'Apple', 77, 0, 25, 2)
SELECT * FROM Produce

+---------+----+----+----+----+
| product | Q1 | Q2 | Q3 | Q4 |
+---------+----+----+----+----+
| Kale    | 51 | 23 | 45 | 3  |
| Apple   | 77 | 0  | 25 | 2  |
+---------+----+----+----+----+

Avec l'opérateur UNPIVOT, les colonnes Q1, Q2, Q3 et Q4 sont pivotées. Les valeurs de ces colonnes sont désormais renseignées dans une nouvelle colonne appelée Sales et les noms de ces colonnes sont désormais renseignés dans une nouvelle colonne appelée Quarter. Il s'agit d'une opération de pivot inverse sur une seule colonne.

SELECT * FROM Produce
UNPIVOT(sales FOR quarter IN (Q1, Q2, Q3, Q4))

+---------+-------+---------+
| product | sales | quarter |
+---------+-------+---------+
| Kale    | 51    | Q1      |
| Kale    | 23    | Q2      |
| Kale    | 45    | Q3      |
| Kale    | 3     | Q4      |
| Apple   | 77    | Q1      |
| Apple   | 0     | Q2      |
| Apple   | 25    | Q3      |
| Apple   | 2     | Q4      |
+---------+-------+---------+

Dans cet exemple, nous effectuons un pivot inverse (UNPIVOT) pour transformer quatre trimestres en deux semestres. Il s'agit d'une opération de pivot inverse sur plusieurs colonnes.

SELECT * FROM Produce
UNPIVOT(
  (first_half_sales, second_half_sales)
  FOR semesters
  IN ((Q1, Q2) AS 'semester_1', (Q3, Q4) AS 'semester_2'))

+---------+------------------+-------------------+------------+
| product | first_half_sales | second_half_sales | semesters  |
+---------+------------------+-------------------+------------+
| Kale    | 51               | 23                | semester_1 |
| Kale    | 45               | 3                 | semester_2 |
| Apple   | 77               | 0                 | semester_1 |
| Apple   | 25               | 2                 | semester_2 |
+---------+------------------+-------------------+------------+

Opérateur TABLESAMPLE

TABLESAMPLE SYSTEM ( percent PERCENT )

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.

L'échantillonnage renvoie divers enregistrements tout en évitant les coûts liés à l'analyse et au traitement d'une table entière. Chaque exécution de la requête peut renvoyer des résultats différents, car chaque exécution traite un échantillon calculé indépendamment. Le langage SQL standard de Google ne met pas en cache les résultats des requêtes qui incluent une clause TABLESAMPLE.

Remplacez percent par le pourcentage de l'ensemble de données que vous souhaitez inclure dans les résultats. La valeur doit être comprise entre 0 et 100. La valeur peut être une valeur littérale ou un paramètre de requête. Il ne peut pas s'agir d'une variable.

Pour en savoir plus, consultez la section Échantillonnage de table.

Exemple

La requête suivante sélectionne environ 10 % des données d'une table :

SELECT * FROM dataset.my_table TABLESAMPLE SYSTEM (10 PERCENT)

Opération JOIN

join_operation:
    { cross_join_operation | condition_join_operation }

cross_join_operation:
    from_item cross_join_operator from_item

condition_join_operation:
    from_item condition_join_operator from_item join_condition

cross_join_operator:
    { CROSS JOIN | , }

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

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.

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 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 objet ARRAY en un ensemble de lignes. Pour en savoir plus, consultez la section 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     |
| ...                       |
+---------------------------+

Jointure croisée séparée par des virgules (,)

Les CROSS JOIN peuvent être écrits implicitement avec une virgule. Il s'agit d'une jointure croisée séparée par des virgules.

Dans une clause FROM, une jointure croisée séparée par des virgules ressemble à ce qui suit :

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

Exemples

Cette requête effectue une jointure croisée séparée par des virgules 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 opération FULL OUTER JOIN (ou simplement FULL JOIN) renvoie tous les champs pour toutes les lignes correspondantes dans les deux éléments from_items satisfaisant la condition de jointure. Si une ligne donnée d'un élément from_item ne rejoint aucune ligne de l'autre from_item, la ligne renvoie 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 contenant une ou plusieurs colonnes qui apparaissent dans les deux tables d'entrée. Elle effectue une comparaison d'égalité sur cette colonne et les lignes remplissant 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.SchoolID est identique à TeamMascot.SchoolID. Les résultats incluent une seule colonne SchoolID.

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 en 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 clauses 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

Une clause FROM peut avoir plusieurs jointures. Si la clause FROM ne comporte pas de jointures croisées séparées par des virgules, les jointures ne nécessitent pas de parenthèses. Cependant, les parenthèses peuvent aider à améliorer la lisibilité :

FROM A JOIN B JOIN C JOIN D USING (w) ON B.x = C.y ON A.z = B.x

Si votre clause contient des jointures croisées séparées par des virgules, vous devez utiliser des parenthèses :

FROM A, B JOIN C JOIN D ON C.x = D.y ON B.z = C.x    // INVALID

FROM A, B JOIN (C JOIN D ON C.x = D.y) ON B.z = C.x  // VALID

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 y avoir de jointure RIGHT JOIN ou FULL JOIN après une jointure croisée séparée par des virgules, 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 l'élément from_item droit contient une référence à une ou plusieurs variables de plage ou un nom de colonne introduit par l'élément from_item 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 corrélées, car les lignes de l'élément from_item droit ne peuvent pas être déterminées en l'absence de ligne provenant de l'élément from_item gauche.

Toutes les opérations de jointure corrélées doivent référencer un tableau 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

Élément from_item gauche : A
Élément from_item droit : UNNEST(...) AS C
Une sous-requête corrélée : (SELECT AS STRUCT * FROM B WHERE A.ID = B.ID)

Il s'agit d'un autre exemple conceptuel d'opération de jointure corrélée. array_of_IDs fait partie de l'élément from_item gauche, mais est référencé dans l'élément from_item droit.

FROM A JOIN UNNEST(A.array_of_IDs) AS C

L'opérateur UNNEST peut être explicite ou implicite. Les deux exemples ci-dessous sont autorisés :

FROM A JOIN UNNEST(A.array_of_IDs) AS IDs

FROM A JOIN A.array_of_IDs AS IDs

Lors d'une opération de jointure corrélée, l'élément from_item droit est réévaluée par rapport à chaque ligne distincte de l'élément from_item 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

Mises en garde

Dans une opération LEFT JOIN corrélée, lorsque la table d'entrée de droite est vide pour une ligne du côté gauche, c'est comme si aucune ligne du côté droit ne remplissait la condition de jointure dans une opération LEFT JOIN standard. En l'absence de lignes conjointes, une ligne contenant les valeurs NULL pour toutes les colonnes de droite est générée pour être jointe à la ligne de gauche.
Dans une opération CROSS JOIN corrélée, lorsque la table d'entrée de droite est vide pour une ligne du côté gauche, c'est comme si aucune ligne du côté droit ne remplissait la condition de jointure dans une opération INNER JOIN standard. Cela signifie que la ligne est supprimée des résultats.

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            |
+------------+----------+----------+------------+--------------+

Un modèle courant pour une opération LEFT JOIN corrélée consiste à disposer d'une opération UNNEST à droite faisant référence au tableau d'une colonne introduite par entrée à gauche. Pour les lignes où ce tableau est vide ou NULL, l'opération UNNEST ne produit aucune ligne sur l'entrée de droite. Dans ce cas, une ligne contenant une entrée NULL dans chaque colonne de l'entrée de droite est créée pour être jointe à la ligne de l'entrée de gauche. Exemple :

SELECT A.name, item, ARRAY_LENGTH(A.items) item_count_for_name
FROM
  UNNEST(
    [
      STRUCT(
        'first' AS name,
        [1, 2, 3, 4] AS items),
      STRUCT(
        'second' AS name,
        [] AS items)]) AS A
LEFT JOIN
  A.items AS item;

+--------+------+---------------------+
| name   | item | item_count_for_name |
+--------+------+---------------------+
| first  | 1    | 4                   |
| first  | 2    | 4                   |
| first  | 3    | 4                   |
| first  | 4    | 4                   |
| second | NULL | 0                   |
+--------+------+---------------------+

Dans le cas d'une jointure CROSS JOIN corrélée, lorsque l'entrée de droite est vide pour une ligne du côté gauche, la ligne finale est supprimée des résultats. Exemple :

SELECT A.name, item
FROM
  UNNEST(
    [
      STRUCT(
        'first' AS name,
        [1, 2, 3, 4] AS items),
      STRUCT(
        'second' AS name,
        [] AS items)]) AS A
CROSS JOIN
  A.items AS item;

+-------+------+
| name  | item |
+-------+------+
| first | 1    |
| first | 2    |
| first | 3    |
| first | 4    |
+-------+------+

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

L'ordre d'évaluation ne correspond pas toujours à l'ordre de syntaxe.

La clause WHERE ne référence que les colonnes disponibles via la clause FROM. Elle ne peut pas faire référence aux alias de la 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 [, ...] | ROLLUP ( 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;

GROUP BY ROLLUP renvoie les résultats de GROUP BY pour les préfixes des expressions de la liste ROLLUP, chacun d'entre eux représentant un ensemble de regroupement. Pour la liste ROLLUP (a, b, c), les ensembles de regroupement sont (a, b, c), (a, b), (a), (). Lors de l'évaluation des résultats de GROUP BY pour un ensemble de regroupement spécifique, GROUP BY ROLLUP considère que les expressions ne figurant pas dans l'ensemble de regroupement ont une valeur NULL. Examinez l'instruction SELECT suivante :

SELECT a, b, SUM(c) FROM Input GROUP BY ROLLUP(a, b);

utilise la liste de cumul (a, b). Le résultat inclura les résultats de GROUP BY pour les ensembles de regroupement (a, b), (a) et (), qui comprennent toutes les lignes. Cette méthode renvoie les mêmes lignes que ci-dessous :

SELECT NULL, NULL, SUM(c) FROM Input               UNION ALL
SELECT a,    NULL, SUM(c) FROM Input GROUP BY a    UNION ALL
SELECT a,    b,    SUM(c) FROM Input GROUP BY a, b;

Ainsi, il est possible de calculer des agrégats pour les ensembles de regroupement définis par les expressions de la liste ROLLUP et par les préfixes de cette liste.

Exemple :

WITH Sales AS (
  SELECT 123 AS sku, 1 AS day, 9.99 AS price UNION ALL
  SELECT 123, 1, 8.99 UNION ALL
  SELECT 456, 1, 4.56 UNION ALL
  SELECT 123, 2, 9.99 UNION ALL
  SELECT 789, 3, 1.00 UNION ALL
  SELECT 456, 3, 4.25 UNION ALL
  SELECT 789, 3, 0.99
)
SELECT
  day,
  SUM(price) AS total
FROM Sales
GROUP BY ROLLUP(day);

La requête ci-dessus génère une ligne pour chaque jour en plus du total cumulé pour tous les jours, comme indiqué par un jour NULL :

+------+-------+
| day  | total |
+------+-------+
| NULL | 39.77 |
|    1 | 23.54 |
|    2 |  9.99 |
|    3 |  6.24 |
+------+-------+

Exemple :

WITH Sales AS (
  SELECT 123 AS sku, 1 AS day, 9.99 AS price UNION ALL
  SELECT 123, 1, 8.99 UNION ALL
  SELECT 456, 1, 4.56 UNION ALL
  SELECT 123, 2, 9.99 UNION ALL
  SELECT 789, 3, 1.00 UNION ALL
  SELECT 456, 3, 4.25 UNION ALL
  SELECT 789, 3, 0.99
)
SELECT
  sku,
  day,
  SUM(price) AS total
FROM Sales
GROUP BY ROLLUP(sku, day)
ORDER BY sku, day;

La requête ci-dessus renvoie les lignes rassemblées par les ensembles de regroupement suivants :

Sku et jour (day)
Sku (la valeur jour est NULL)
L'ensemble de regroupement vide (les valeurs jour et sku sont NULL)

Les sommes de ces ensembles de regroupement correspondent au total de chaque combinaison sku-jour, au total de chaque sku pour tous les jours et au total général :

+------+------+-------+
| sku  | day  | total |
+------+------+-------+
| NULL | NULL | 39.77 |
|  123 | NULL | 28.97 |
|  123 |    1 | 18.98 |
|  123 |    2 |  9.99 |
|  456 | NULL |  8.81 |
|  456 |    1 |  4.56 |
|  456 |    3 |  4.25 |
|  789 |    3 |  1.99 |
|  789 | NULL |  1.99 |
+------+------+-------+

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

L'ordre d'évaluation ne correspond pas toujours à l'ordre de syntaxe.

La clause HAVING fait référence aux colonnes disponibles via la clause FROM, ainsi qu'aux 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
  [{ ASC | DESC }]
  [{ NULLS FIRST | NULLS LAST }]
  [, ...]

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

NULLS FIRST | NULLS LAST :
- NULLS FIRST : affiche les valeurs nulles avant les valeurs non nulles.
- NULLS LAST : affiche les valeurs nulles après les valeurs non nulles.
ASC | DESC : trie les résultats par ordre croissant ou décroissant de valeurs expression. ASC est la valeur par défaut. Si ni NULLS FIRST, ni NULLS LAST ne spécifient l'ordre des valeurs nulles, alors :
- NULLS FIRST est appliqué par défaut si le tri est croissant ;
- NULLS LAST est appliqué par défaut si le tri est décroissant.

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 UNION ALL
      SELECT NULL, false)
ORDER BY x;
+------+-------+
| x    | y     |
+------+-------+
| NULL | false |
| 1    | true  |
| 9    | true  |
+------+-------+

Utilisez l'ordre de tri par défaut (croissant), mais en renvoyant les valeurs nulles en dernier.

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

Utilisez l'ordre de tri décroissant.

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

Utilisez l'ordre de tri décroissant, mais en renvoyant les valeurs nulles en premier.

SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
      SELECT 9, true UNION ALL
      SELECT NULL, false)
ORDER BY x DESC NULLS FIRST;
+------+-------+
| x    | y     |
+------+-------+
| NULL | false |
| 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;

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 QUALIFY

QUALIFY bool_expression

La clause QUALIFY filtre les résultats des fonctions de fenêtrage. Une fonction de fenêtrage doit être présente dans la clause QUALIFY ou dans la liste SELECT.

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 QUALIFY est généralement effectuée dans l'ordre suivant :

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

L'ordre d'évaluation ne correspond pas toujours à l'ordre de syntaxe.

Exemples

La requête suivante renvoie les légumes les plus populaires dans la table Produce, accompagnés de leur classement.

SELECT
  item,
  RANK() OVER (PARTITION BY category ORDER BY purchases DESC) as rank
FROM Produce
WHERE Produce.category = 'vegetable'
QUALIFY rank <= 3

+---------+------+
| item    | rank |
+---------+------+
| kale    | 1    |
| lettuce | 2    |
| cabbage | 3    |
+---------+------+

Vous n'avez pas besoin d'inclure de fonction de fenêtrage dans la liste SELECT pour utiliser QUALIFY. La requête suivante renvoie les légumes les plus populaires dans la table Produce.

SELECT item
FROM Produce
WHERE Produce.category = 'vegetable'
QUALIFY RANK() OVER (PARTITION BY category ORDER BY purchases DESC) <= 3

+---------+
| item    |
+---------+
| kale    |
| lettuce |
| cabbage |
+---------+

Clause WINDOW

WINDOW named_window_expression [, ...]

named_window_expression:
  named_window AS { named_window | ( [ window_specification ] ) }

Une clause WINDOW définit une liste de fenêtres nommées. Une fenêtre nommée représente un groupe de lignes dans une table sur laquelle appliquer une fonction de fenêtrage. Une fenêtre nommée peut être définie avec une spécification de fenêtre ou faire référence à une autre fenêtre nommée. Si une autre fenêtre nommée est référencée, la définition de la fenêtre référencée doit précéder la fenêtre de référence.

Exemples

Ces exemples font référence à une table appelée Produce. Ils renvoient tous le même résultat. Notez les différentes manières de combiner des fenêtres nommées et de les utiliser dans une clause OVER d'une fonction de fenêtrage.

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (item_window) AS most_popular
FROM Produce
WINDOW item_window AS (
  PARTITION BY category
  ORDER BY purchases
  ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (d) AS most_popular
FROM Produce
WINDOW
  a AS (PARTITION BY category),
  b AS (a ORDER BY purchases),
  c AS (b ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING),
  d AS (c)

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (c ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popular
FROM Produce
WINDOW
  a AS (PARTITION BY category),
  b AS (a ORDER BY purchases),
  c AS b

Opérateurs d'ensemble

set_operation:
  query_expr set_operator query_expr

set_operator:
  UNION { ALL | DISTINCT } | INTERSECT DISTINCT | EXCEPT 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 UNION DISTINCT,

Syntaxe des requêtes

Syntaxe SQL

Exemples de tables

Table Roster

Table PlayerStats

Table TeamMascot

Instruction SELECT

SELECT *

SELECT expression

SELECT expression.*

SELECT * EXCEPT

SELECT * REPLACE

SELECT DISTINCT

SELECT ALL

SELECT AS STRUCT

SELECT AS VALUE

Clause FROM

pivot_operator

unpivot_operator

tablesample_operator

table_name

FOR SYSTEM_TIME AS OF

join_operation

query_expr

field_path

unnest_operator

cte_name

Opérateur UNNEST

UNNEST et STRUCT

UNNEST explicite et implicite

UNNEST et NULL

UNNEST et WITH OFFSET

Opérateur PIVOT

Opérateur UNPIVOT

Opérateur TABLESAMPLE

Opération JOIN

JOINTURE [INTERNE]

JOINTURE CROISÉE

Jointure croisée séparée par des virgules (,)

JOINTURE [EXTERNE] COMPLÈTE

JOINTURE [EXTERNE] GAUCHE

JOINTURE [EXTERNE] DROITE

Clause ON

Clause USING

Équivalence des mots clés ON et USING

Opérations de jointure en séquence

Opération de jointure corrélée

Clause WHERE

Clause GROUP BY

Clause HAVING

Agrégation obligatoire

Fonction d'agrégation dans la liste SELECT

Fonction d'agrégation dans la clause HAVING

Agrégation dans la liste SELECT et la clause HAVING

Clause ORDER BY

Clause QUALIFY

Clause WINDOW

Opérateurs d'ensemble

SELECT `expression`

SELECT `expression.*`

Fonction d'agrégation dans la liste `SELECT`

Agrégation dans la liste `SELECT` et la clause `HAVING`