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 BigQuery.
Syntaxe SQL
query_statement: query_expr query_expr: [ WITH with_query_name AS ( query_expr ) [, ...] ] { select | ( query_expr ) | query_expr set_op query_expr } [ 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_item [, ...] ] [ WHERE bool_expression ] [ GROUP BY { expression [, ...] | ROLLUP ( expression [, ...] ) } ] [ HAVING bool_expression ] [ WINDOW named_window_expression AS { named_window | ( [ window_definition ] ) } [, ...] ] set_op: UNION { ALL | DISTINCT } | INTERSECT DISTINCT | EXCEPT DISTINCT from_item: { table_name [ [ AS ] alias ] [ FOR SYSTEM_TIME AS OF timestamp_expression ] | join | ( query_expr ) [ [ AS ] alias ] | field_path | { UNNEST( array_expression ) | UNNEST( array_path ) | array_path } [ [ AS ] alias ] [ WITH OFFSET [ [ AS ] alias ] ] | with_query_name [ [ AS ] alias ] } join: from_item [ join_type ] JOIN from_item [ { ON bool_expression | USING ( join_column [, ...] ) } ] join_type: { INNER | CROSS | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER] }
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 une virgule.
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 |
| Davis | 52 | 4 |
| Eisenhower | 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 SELECT
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 * 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 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
.
Tables de valeurs
Dans BigQuery, 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.
Remarque : Dans BigQuery, une requête ne peut renvoyer qu'une table de valeurs avec un type STRUCT
.
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 BigQuery, 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 avec l'une des syntaxes suivantes :
SELECT AS STRUCT
SELECT AS STRUCT expr1 [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 t.f1, t.f2 WHERE t.f3=true)
FROM
Table t
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.
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 a, 2 b) xyz FROM Table;
La requête ci-dessus génère une table avec le type de ligne STRUCT<a int64, b int64>
.
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_item: { table_name [ [ AS ] alias ] [ FOR SYSTEM_TIME AS OF timestamp_expression ] | join | ( query_expr ) [ [ AS ] alias ] | field_path | { UNNEST( array_expression ) | UNNEST( array_path ) | array_path } [ [ AS ] alias ] [ WITH OFFSET [ [ AS ] alias ] ] | with_query_name [ [ 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.
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érateurUNNEST
- 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;
participer
Consultez la section Types JOIN.
sélectionner
( select ) [ [ 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
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 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} |
+---+-----+--------------+
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
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;
Consultez la section Arrays topic
pour en savoir plus sur l'utilisation de l'élément UNNEST
, y compris en cas de construction, d'aplatissement et de filtrage.
with_query_name
Les noms de requêtes d'une clause WITH
(consultez la section Clause WITH) agissent comme des noms de tables temporaires auxquels vous pouvez faire référence n'importe où dans la clause FROM
.
Dans l'exemple ci-dessous, subQ1
et subQ2
sont with_query_names
.
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
.
Alias
Consultez la section Utiliser des alias pour en savoir plus sur la syntaxe et la visibilité des alias de la clause FROM
.
Types JOIN
join: from_item [ join_type ] JOIN from_item [ ON bool_expression | USING ( join_column [, ...] ) ] join_type: { INNER | CROSS | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER] }
La clause 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.
Toutes les clauses JOIN
nécessitent un join_type
.
Une clause JOIN
nécessite une condition de jointure, sauf si l'une des conditions suivantes est remplie :
join_type
est défini surCROSS
.- Un élément
from_item
, ou les deux, n'est pas une table, par exemplearray_path
oufield_path
.
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.
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 des jointures CROSS JOIN
corrélées pour aplatir les colonnes ARRAY
. Dans ce cas, les lignes du second élément from_item
varient pour chaque ligne du premier élément from_item
.
FROM A CROSS JOIN A.y
Table A Result
+-------------------+ +-----------+
| w | x | y | -> | w | x | y |
+-------------------+ +-----------+
| 1 | a | [P, Q] | | 1 | a | P |
| 2 | b | [R, S, T] | | 1 | a | Q |
+-------------------+ | 2 | b | R |
| 2 | b | S |
| 2 | b | T |
+-----------+
Les jointures CROSS JOIN
peuvent être écrites explicitement comme suit :
FROM a CROSS JOIN b
Ou implicitement sous la forme d'une jointure croisée séparée par une virgule comme suit :
FROM a, b
Vous ne pouvez pas écrire de jointures croisées séparées par une virgule entre parenthèses :
FROM a CROSS JOIN (b, c) // INVALID
Consultez la rubrique Séquences de clauses JOIN pour en savoir plus sur le comportement d'une jointure croisée séparée par une virgule dans une séquence de clauses JOIN.
Exemples
Cette requête effectue une jointure CROSS JOIN
explicite 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 |
| ... |
+---------------------------+
Cette requête effectue une jointure croisée séparée par une virgule qui produit les mêmes résultats que la jointure CROSS JOIN
explicite ci-dessus :
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster, TeamMascot;
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
La clause ON
contient un élément bool_expression
. Une ligne combinée (le résultat de la jointure de deux lignes) répond à la condition de jointure si bool_expression
renvoie l'attribut 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 column_list
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.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 | +---+
+---+ +---+
Séquences de clauses JOIN
La clause FROM
peut contenir une séquence de plusieurs clauses JOIN
. Les clauses 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
Vous pouvez également omettre les parenthèses pour les clauses ON
et USING
consécutives, à condition qu'il n'y ait pas de jointures séparées par une virgule :
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 séparées par une virgule, 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 pas y avoir de jointure RIGHT JOIN
ou FULL JOIN
après une jointure séparée par une virgule :
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
Clause WHERE
WHERE bool_expression
La clause WHERE
filtre les lignes en évaluant chaque ligne par rapport à l'expression bool_expression
et supprime toutes les lignes qui ne renvoient pas TRUE (c'est-à-dire, toutes les lignes renvoyant FALSE ou NULL).
Exemple :
SELECT * FROM Roster
WHERE SchoolID = 52;
Le champ bool_expression
peut contenir plusieurs sous-conditions.
Exemple :
SELECT * FROM Roster
WHERE STARTS_WITH(LastName, "Mc") OR STARTS_WITH(LastName, "Mac");
Vous ne pouvez pas faire référence à des alias de colonne dans la liste SELECT
de la clause WHERE
.
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, la requête suivante :
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster INNER JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;
équivaut à :
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
est semblable à la clause WHERE
: elle filtre et exclut les lignes qui ne renvoient pas TRUE lorsqu'elles sont évaluées à l'aide de bool_expression
.
À l'instar de la clause WHERE
, l'expression bool_expression
peut être toute expression renvoyant une valeur booléenne et peut contenir plusieurs sous-conditions.
Les clauses HAVING
et WHERE
ont les différences suivantes :
- La clause
HAVING
requiert queGROUP BY
ou qu'une agrégation soit présent dans la requête. - La clause
HAVING
se produit aprèsGROUP BY
et l'agrégation, et avantORDER BY
. Cela signifie que la clauseHAVING
n'est évaluée qu'une fois pour chaque ligne agrégée dans l'ensemble de résultats. Cela diffère de la clauseWHERE
, qui est évaluée avantGROUP BY
et l'agrégation.
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 [{ 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.
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 valeursexpression
.ASC
est la valeur par défaut. Si niNULLS FIRST
, niNULLS 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;
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 trisASC
et en dernier dans les trisDESC
. - 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
ORDER BY LastName;
SELECT SUM(PointsScored), LastName
FROM PlayerStats
ORDER BY 2;
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 analytique. 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 analytique.
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
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
,DISTINCT
est calculé aprèsUNION
, 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
etUNION 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
La clause WITH
contient une ou plusieurs sous-requêtes nommées qui s'exécutent chaque fois qu'une instruction SELECT
ultérieure y fait référence.
Toute clause ou sous-requête peut faire référence à des sous-requêtes que vous définissez dans la clause WITH
.
Cela comprend toutes les instructions SELECT
de chaque côté d'un opérateur d'ensemble, tel que UNION
.
La clause WITH
est surtout utile pour la lisibilité, car BigQuery ne matérialise pas le résultat des requêtes dans la clause WITH
. Si une requête apparaît dans plusieurs clauses WITH
, elle s'exécute dans chacune d'elles.
Exemple :
WITH subQ1 AS (SELECT SchoolID FROM Roster),
subQ2 AS (SELECT OpponentID FROM PlayerStats)
SELECT * FROM subQ1
UNION ALL
SELECT * FROM subQ2;
Vous pouvez utiliser WITH
pour diviser des requêtes plus complexes en une instruction WITH
SELECT
et des clauses WITH
, où l'option la moins souhaitable consiste à écrire des sous-requêtes de table imbriquées. Exemple :
WITH q1 AS (my_query)
SELECT *
FROM
(WITH q2 AS (SELECT * FROM q1) SELECT * FROM q2)
Voici les règles de champ d'application pour les clauses WITH
:
- Les alias introduits dans une clause
WITH
ne sont visibles que dans les sous-requêtes ultérieures de la même clauseWITH
, ainsi que dans la requête sous la clauseWITH
. - Les alias introduits dans la même clause
WITH
doivent être uniques, mais le même alias peut être utilisé dans plusieurs clausesWITH
de la même requête. L'alias local remplace tous les alias externes partout où il est visible. - Les sous-requêtes avec alias dans une clause
WITH
ne peuvent jamais être mises en corrélation. Aucune colonne extérieure à la requête n'est visible. Les seuls noms externes visibles sont les autres aliasWITH
ayant été introduits précédemment dans la même clauseWITH
.
Voici un exemple d'instruction qui utilise des alias dans les sous-requêtes WITH
:
WITH q1 AS (my_query)
SELECT *
FROM
(WITH q2 AS (SELECT * FROM q1), # q1 resolves to my_query
q3 AS (SELECT * FROM q1), # q1 resolves to my_query
q1 AS (SELECT * FROM q1), # q1 (in the query) resolves to my_query
q4 AS (SELECT * FROM q1) # q1 resolves to the WITH subquery
# on the previous line.
SELECT * FROM q1) # q1 resolves to the third inner WITH subquery.
WITH RECURSIVE
n'est pas compatible.
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 BigQuery déduira un alias implicite pour certaines expressions.
Les expressions sans alias explicite ni 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
, si une expression sans alias explicite existe, BigQuery attribue un alias implicite conformément aux règles ci-dessous. 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
impliqueAS abc
. - Dans les expressions de chemin, l'alias est le dernier identifiant du chemin. Par exemple,
SELECT abc.def.ghi
impliqueAS 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
impliqueAS 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 ne comporte pas d'alias explicite, BigQuery attribue un alias implicite dans les cas suivants :
-
Dans les identifiants, l'alias fait office d'identifiant. Par exemple,
FROM abc
impliqueAS abc
. -
Dans les expressions de chemin, l'alias est le dernier identifiant du chemin. Par exemple,
FROM abc.def.ghi
impliqueAS ghi
. - La colonne produite à l'aide de
WITH OFFSET
comporte l'alias impliciteoffset
. - 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 BigQuery.
Visibilité dans la clause FROM
BigQuery 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 entier1
fait référence au premier élément de la listeSELECT
,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ë.
Lorsqu'une liste SELECT
de premier niveau contient des noms de colonnes en double et qu'aucune table de destination n'est spécifiée, toutes les colonnes en double, à l'exception de la première, sont automatiquement renommées pour les rendre uniques. Les colonnes renommées apparaissent dans le résultat de la requête.
Exemple :
SELECT 1 AS a, 2 AS a;
+---+-----+
| a | a_1 |
+---+-----+
| 1 | 2 |
+---+-----+
Les noms de colonnes en double dans une définition de table ou de vue ne sont pas acceptés. Ces instructions comportant des requêtes qui contiennent des noms de colonnes en double échoueront :
CREATE TABLE my_dataset.my_table AS (SELECT 1 AS a, 2 AS a);
CREATE VIEW my_dataset.my_view AS (SELECT 1 AS a, 2 AS a);
Alias ambigus
BigQuery 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
.
Alias implicites
Dans la liste SELECT
, si une expression sans alias explicite existe, BigQuery attribue un alias implicite conformément aux règles ci-dessous. 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
impliqueAS abc
. - Dans les expressions de chemin, l'alias est le dernier identifiant du chemin. Par exemple,
SELECT abc.def.ghi
impliqueAS 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
impliqueAS 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 ne comporte pas d'alias explicite, BigQuery attribue un alias implicite dans les cas suivants :
- Dans les identifiants, l'alias fait office d'identifiant. Par exemple,
FROM abc
impliqueAS abc
. - Dans les expressions de chemin, l'alias est le dernier identifiant du chemin. Par exemple,
FROM abc.def.ghi
impliqueAS ghi
. - La colonne produite à l'aide de
WITH OFFSET
comporte l'alias impliciteoffset
.
- Dans les identifiants, l'alias fait office d'identifiant. Par exemple,
- Les sous-requêtes de table n'ont pas d'alias implicite.
FROM UNNEST(x)
ne possède pas d'alias implicite.
Variables de plage
Dans BigQuery, 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 DISTINCT
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)