Définir les opérateurs¶
Les opérateurs définis combinent les résultats intermédiaires de plusieurs blocs de requête en un seul jeu de résultats.
Syntaxe générale¶
[ ( ] <query> [ ) ]
{
INTERSECT |
{ MINUS | EXCEPT } |
UNION [ { DISTINCT | ALL } ] [ BY NAME ]
}
[ ( ] <query> [ ) ]
[ ORDER BY ... ]
[ LIMIT ... ]
Notes générales sur l’utilisation¶
Chaque requête peut elle-même contenir des opérateurs de requête, de sorte que vous pouvez combiner plusieurs expressions de requête avec des opérateurs Set.
Vous pouvez appliquer les clauses:doc:
/sql-reference/constructs/order-by
et LIMIT / FETCH au résultat de l’opérateur Set.Lors de l’utilisation de ces opérateurs :
Assurez-vous que chaque requête sélectionne le même nombre de colonnes, à l’exception des requêtes qui incluent UNION BY NAME ou UNION ALL BY NAME.
Lorsque vous utilisez ces opérateurs, assurez-vous que les types de données de chaque colonne sont cohérents d’une ligne à l’autre à partir de sources différentes. L’un des exemples de la section Utiliser l’opérateur UNION et convertir les types de données non concordants illustre le problème potentiel et la solution adéquate lorsque les types de données ne correspondent pas.
En général, les « significations », ainsi que les types de données, des colonnes doivent correspondre. La requête suivante avec l’opérateur UNION ALL ne produira pas les résultats souhaités :
SELECT LastName, FirstName FROM employees UNION ALL SELECT FirstName, LastName FROM contractors;
Le risque d’erreur augmente lorsque vous utilisez l’astérisque pour spécifier toutes les colonnes d’une table, par exemple :
SELECT * FROM table1 UNION ALL SELECT * FROM table2;
Si les tables ont le même nombre de colonnes, mais que les colonnes ne sont pas dans le même ordre, les résultats de la requête seront probablement incorrects lorsque que vous utiliserez ces opérateurs.
Les opérateurs UNION BY NAME et UNION ALL BY NAME sont des exceptions dans ce scénario. Par exemple, la requête suivante renvoie les résultats corrects :
SELECT LastName, FirstName FROM employees UNION ALL BY NAME SELECT FirstName, LastName FROM contractors;
Les noms des colonnes de sortie sont basés sur les noms des colonnes de la première requête. Par exemple, considérons la requête suivante :
SELECT LastName, FirstName FROM employees UNION ALL SELECT FirstName, LastName FROM contractors;
Cette requête se comporte comme si la requête était la suivante :
SELECT LastName, FirstName FROM employees UNION ALL SELECT FirstName AS LastName, LastName AS FirstName FROM contractors;
La priorité des opérateurs Set correspond aux normes ANSI et ISO SQL :
Les opérateurs UNION [ALL] et MINUS (EXCEPT) ont la même priorité.
L’opérateur INTERSECT a une priorité plus élevée que UNION [ALL] et MINUS (EXCEPT).
Snowflake traite les opérateurs de même ordre de précédence de gauche à droite.
Vous pouvez utiliser des parenthèses pour forcer les expressions à être évaluées dans un ordre différent.
Tous les fournisseurs de bases de données ne suivent pas la norme ANSI/ISO pour la priorité des opérateurs Set. Snowflake recommande d’utiliser des parenthèses pour spécifier l’ordre d’évaluation, en particulier si vous portez du code d’un autre fournisseur vers Snowflake ou si vous écrivez du code que vous pourriez exécuter sur d’autres bases de données ainsi que sur Snowflake.
Tables d’échantillon pour les exemples¶
Certains des exemples de cette rubrique utilisent les tables d’échantillon suivantes. Les deux tables comportent une colonne de code postal. Une table enregistre le code postal de chaque bureau de vente et l’autre enregistre le code postal de chaque client.
CREATE OR REPLACE TABLE sales_office_postal_example(
office_name VARCHAR,
postal_code VARCHAR);
INSERT INTO sales_office_postal_example VALUES ('sales1', '94061');
INSERT INTO sales_office_postal_example VALUES ('sales2', '94070');
INSERT INTO sales_office_postal_example VALUES ('sales3', '98116');
INSERT INTO sales_office_postal_example VALUES ('sales4', '98005');
CREATE OR REPLACE TABLE customer_postal_example(
customer VARCHAR,
postal_code VARCHAR);
INSERT INTO customer_postal_example VALUES ('customer1', '94066');
INSERT INTO customer_postal_example VALUES ('customer2', '94061');
INSERT INTO customer_postal_example VALUES ('customer3', '98444');
INSERT INTO customer_postal_example VALUES ('customer4', '98005');
INTERSECT¶
Retourne les lignes d’un jeu de résultats d’une requête qui apparaissent également dans le jeu de résultats d’une autre requête, avec élimination des doublons.
Syntaxe¶
[ ( ] <query> [ ) ]
INTERSECT
[ ( ] <query> [ ) ]
Exemples d’opérateurs INTERSECT¶
Pour trouver les codes postaux qui sont à la fois dans la table``sales_office_postal_example`` et la table customer_postal_example
, interrogez les:ref:tables d'exemple <label-operators_set_sample_tables>
:
SELECT postal_code FROM sales_office_postal_example
INTERSECT
SELECT postal_code FROM customer_postal_example
ORDER BY postal_code;
+-------------+
| POSTAL_CODE |
|-------------|
| 94061 |
| 98005 |
+-------------+
MINUS , EXCEPT¶
Renvoie les lignes renvoyées par la première requête qui ne sont pas non plus renvoyées par la seconde requête.
Les mots clés MINUS et EXCEPT ont la même signification et peuvent être utilisés de manière interchangeable.
Syntaxe¶
[ ( ] <query> [ ) ]
MINUS
[ ( ] <query> [ ) ]
[ ( ] <query> [ ) ]
EXCEPT
[ ( ] <query> [ ) ]
Exemples d’opérateurs MINUS¶
Interrogez les tables d’échantillon pour trouver les codes postaux dans la table sales_office_postal_example
qui ne sont pas non plus dans la table customer_postal_example
:
SELECT postal_code FROM sales_office_postal_example
MINUS
SELECT postal_code FROM customer_postal_example
ORDER BY postal_code;
+-------------+
| POSTAL_CODE |
|-------------|
| 94070 |
| 98116 |
+-------------+
Interrogez les tables d’échantillon pour trouver les codes postaux dans la table customer_postal_example
qui ne sont pas non plus dans la table sales_office_postal_example
:
SELECT postal_code FROM customer_postal_example
MINUS
SELECT postal_code FROM sales_office_postal_example
ORDER BY postal_code;
+-------------+
| POSTAL_CODE |
|-------------|
| 94066 |
| 98444 |
+-------------+
UNION [ { DISTINCT | ALL } ] [ BY NAME ]¶
Combine les ensembles de résultats de deux requêtes :
UNION [ DISTINCT ] combine les lignes par position de colonne avec l’élimination des doublons.
UNION ALL combine les lignes par position de colonne sans élimination des doublons.
UNION [ DISTINCT ] BY NAME combine les lignes par nom de colonne avec l’élimination des doublons.
UNION ALL BY NAME combine les lignes par nom de colonne sans élimination des doublons.
La valeur par défaut est UNION DISTINCT (c’est-à-dire combiner les lignes par position de colonne avec élimination des doublons). Le mot clé DISTINCT est facultatif. Le mot-clé DISTINCT et le mot-cléALL s’excluent mutuellement.
Utiliser UNION ou UNION ALL lorsque les positions des colonnes correspondent dans les tables que vous combinez. Utiliser UNION BY NAME ou UNION ALL BY NAME pour les cas d’utilisation suivants :
Les tables que vous combinez ont des ordres de colonnes variables.
Les tables que vous combinez ont des schémas en constante évolution, dans lesquels des colonnes sont ajoutées ou réorganisées.
Vous souhaitez combiner des sous-ensembles de colonnes qui ont différentes positions dans les tables.
Syntaxe¶
[ ( ] <query> [ ) ]
UNION [ { DISTINCT | ALL } ] [ BY NAME ]
[ ( ] <query> [ ) ]
Notes sur l’utilisation de la clause BY NAME¶
En plus des notes générales sur l’utilisation :ref:` <label-operators_query_general_usage_notes>`, les notes sur l’utilisation suivantes s’appliquent à UNION BY NAME et UNION ALL BY NAME :
Les colonnes ayant les mêmes identificateurs sont comparées et combinées. La correspondance des identificateurs non cités est insensible à la casse, et la correspondance des identificateurs cités est sensible à la casse.
Les entrées ne sont pas tenues d’avoir le même nombre de colonnes. Si une colonne existe dans une entrée mais pas dans l’autre, elle est remplie avec des valeursNULL dans le jeu de résultats combiné pour chaque ligne où il est absent.
L’ordre des colonnes dans le jeu de résultats combiné est déterminé par l’ordre des colonnes uniques de gauche à droite, lors de la première rencontre.
Exemples d’opérateurs UNION¶
Les exemples suivants utilisent l’opérateur UNION :
Combiner les résultats de deux requêtes par position de colonne
Utiliser un alias pour combiner les résultats de deux requêtes avec des noms de colonnes différents
Utiliser l’opérateur UNION et convertir les types de données non concordants
Combiner les résultats de deux requêtes par position de colonne¶
Pour combiner les jeux de résultats par position de colonne à partir de deux requêtes sur la table d’échantillon <label-operators_set_sample_tables>, utilisez l’opérateur UNION.
SELECT office_name office_or_customer, postal_code FROM sales_office_postal_example
UNION
SELECT customer, postal_code FROM customer_postal_example
ORDER BY postal_code;
+--------------------+-------------+
| OFFICE_OR_CUSTOMER | POSTAL_CODE |
|--------------------+-------------|
| sales1 | 94061 |
| customer2 | 94061 |
| customer1 | 94066 |
| sales2 | 94070 |
| sales4 | 98005 |
| customer4 | 98005 |
| sales3 | 98116 |
| customer3 | 98444 |
+--------------------+-------------+
Combiner les résultats de deux requêtes par nom de colonne¶
Créez deux tables avec un ordre différent des colonnes et insérez les données :
CREATE OR REPLACE TABLE union_demo_column_order1 (
a INTEGER,
b VARCHAR);
INSERT INTO union_demo_column_order1 VALUES
(1, 'one'),
(2, 'two'),
(3, 'three');
CREATE OR REPLACE TABLE union_demo_column_order2 (
B VARCHAR,
A INTEGER);
INSERT INTO union_demo_column_order2 VALUES
('three', 3),
('four', 4);
Pour combiner les jeux de résultats par nom de colonne de deux requêtes, utilisez les opérateursUNION BY NAME :
SELECT * FROM union_demo_column_order1
UNION BY NAME
SELECT * FROM union_demo_column_order2
ORDER BY a;
+---+-------+
| A | B |
|---+-------|
| 1 | one |
| 2 | two |
| 3 | three |
| 4 | four |
+---+-------+
La sortie montre que la requête a supprimé la ligne en double (avec 3
dans la colonne A
et three
dans la colonne B
).
Pour combiner les tables sans l’élimination des doublons, utilisez l’opérateur UNION ALL BY NAME :
SELECT * FROM union_demo_column_order1
UNION ALL BY NAME
SELECT * FROM union_demo_column_order2
ORDER BY a;
+---+-------+
| A | B |
|---+-------|
| 1 | one |
| 2 | two |
| 3 | three |
| 3 | three |
| 4 | four |
+---+-------+
Notez que les cas des noms de colonnes ne correspondent pas dans les deux tables. Les noms des colonnes sont en minuscules dans la table union_demo_column_order1
et majuscules dans la table union_demo_column_order2
. Si vous exécutez une requête avec des guillemets autour des noms des colonnes, une erreur est renvoyée, car la correspondance des identificateurs cités est sensible à la casse. Par exemple, la requête suivante place des guillemets autour des noms de colonnes :
SELECT 'a', 'b' FROM union_demo_column_order1
UNION ALL BY NAME
SELECT 'B', 'A' FROM union_demo_column_order2
ORDER BY a;
000904 (42000): SQL compilation error: error line 4 at position 9
invalid identifier 'A'
Utiliser un alias pour combiner les résultats de deux requêtes avec des noms de colonnes différents¶
Lorsque vous utilisez l’opérateur UNION BY NAME pour combiner les jeux de résultats par nom de colonne à partir des tables d’échantillons <label-operators_set_sample_tables>, les lignes du jeu de résultats ont des valeurs NULLcar les noms de colonnes ne correspondent pas :
SELECT office_name, postal_code FROM sales_office_postal_example
UNION BY NAME
SELECT customer, postal_code FROM customer_postal_example
ORDER BY postal_code;
+-------------+-------------+-----------+
| OFFICE_NAME | POSTAL_CODE | CUSTOMER |
|-------------+-------------+-----------|
| sales1 | 94061 | NULL |
| NULL | 94061 | customer2 |
| NULL | 94066 | customer1 |
| sales2 | 94070 | NULL |
| sales4 | 98005 | NULL |
| NULL | 98005 | customer4 |
| sales3 | 98116 | NULL |
| NULL | 98444 | customer3 |
+-------------+-------------+-----------+
La sortie montre que les colonnes avec des identificateurs différents ne sont pas combinées et que les lignes ont des valeurs NULL pour les colonnes qui sont dans une table mais pas dans l’autre. La colonne postal_code
se trouve dans les deux tables, il n’y a donc pas de valeurs NULL dans la sortie pour la colonne postal_code
.
La requête suivante utilise l’alias office_or_customer
afin que les colonnes portant des noms différents aient le même nom pendant la durée de la requête :
SELECT office_name AS office_or_customer, postal_code FROM sales_office_postal_example
UNION BY NAME
SELECT customer AS office_or_customer, postal_code FROM customer_postal_example
ORDER BY postal_code;
+--------------------+-------------+
| OFFICE_OR_CUSTOMER | POSTAL_CODE |
|--------------------+-------------|
| sales1 | 94061 |
| customer2 | 94061 |
| customer1 | 94066 |
| sales2 | 94070 |
| sales4 | 98005 |
| customer4 | 98005 |
| sales3 | 98116 |
| customer3 | 98444 |
+--------------------+-------------+
Utiliser l’opérateur UNION et convertir les types de données non concordants¶
Cet exemple illustre un problème potentiel lié à l’utilisation de l’opérateur UNION lorsque les types de données ne correspondent pas, puis fournit la solution.
Commencez par créer des tables et à insérer des données :
CREATE OR REPLACE TABLE union_test1 (v VARCHAR);
CREATE OR REPLACE TABLE union_test2 (i INTEGER);
INSERT INTO union_test1 (v) VALUES ('Smith, Jane');
INSERT INTO union_test2 (i) VALUES (42);
Exécutez un UNION par opération de position de colonne avec différents types de données (une valeur VARCHAR dans union_test1
et une valeur INTEGER dans union_test2
) :
SELECT v FROM union_test1
UNION
SELECT i FROM union_test2;
Cette requête renvoie une erreur :
100038 (22018): Numeric value 'Smith, Jane' is not recognized
Maintenant, utilisez la conversion explicite pour convertir les entrées en un type compatible :
SELECT v::VARCHAR FROM union_test1
UNION
SELECT i::VARCHAR FROM union_test2;
+-------------+
| V::VARCHAR |
|-------------|
| Smith, Jane |
| 42 |
+-------------+