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 [ ALL ] } [ ( ] <query> [ ) ]
[ ORDER BY ... ]
[ LIMIT ... ]
Copy

Notes générales sur l’utilisation

  • Chaque requête peut elle-même contenir des opérateurs de requête, ce qui permet de combiner un nombre quelconque d’expressions de requête avec des opérateurs Set.

  • Les clauses ORDER BY et LIMIT / FETCH sont appliquées 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.

    • 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. Ce qui suit ne produira pas les résultats souhaités :

      SELECT LastName, FirstName FROM employees
      UNION ALL
      SELECT FirstName, LastName FROM contractors;
      
      Copy

      Le risque d’erreur augmente lorsque vous utilisez l’astérisque pour spécifier toutes les colonnes d’un tableau, par exemple :

      SELECT * FROM table1
      UNION ALL
      SELECT * FROM table2;
      
      Copy

      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.

    • 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;
      
      Copy

      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;
      
      Copy
  • 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).

    Les opérateurs de même priorité sont traités 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

Les exemples de cette rubrique utilisent les tables d’échantillon suivantes. Les deux tables comportent une colonne Code postal. Une table enregistre le code postal de chaque bureau de vente, tandis que l’autre enregistre le code postal de chaque client.

CREATE OR REPLACE TABLE sales_office_zip_example(
  office_name VARCHAR,
  zip VARCHAR);

INSERT INTO sales_office_zip_example VALUES ('sales1', '94061');
INSERT INTO sales_office_zip_example VALUES ('sales2', '94070');
INSERT INTO sales_office_zip_example VALUES ('sales3', '98116');
INSERT INTO sales_office_zip_example VALUES ('sales4', '98005');

CREATE OR REPLACE TABLE customer_zip_example(
  customer VARCHAR,
  zip VARCHAR);

INSERT INTO customer_zip_example VALUES ('customer1', '94066');
INSERT INTO customer_zip_example VALUES ('customer2', '94061');
INSERT INTO customer_zip_example VALUES ('customer3', '98444');
INSERT INTO customer_zip_example VALUES ('customer4', '98005');
Copy

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

SELECT ...
INTERSECT
SELECT ...
Copy

Exemples d’opérateurs INTERSECT

Interrogez les tables d’échantillon pour trouver les codes postaux qui ont à la fois un bureau de vente et un client :

SELECT zip FROM sales_office_zip_example
INTERSECT
SELECT zip FROM customer_zip_example;
Copy
+-------+
| ZIP   |
|-------|
| 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

SELECT ...
MINUS
SELECT ...

SELECT ...
EXCEPT
SELECT ...
Copy

Exemples d’opérateurs MINUS

Interrogez les tables d’échantillon pour trouver les codes postaux dans la table sales_office_zip_example qui ne sont pas non plus dans la table customer_zip_example :

SELECT zip FROM sales_office_zip_example
MINUS
SELECT zip FROM customer_zip_example;
Copy
+-------+
| ZIP   |
|-------|
| 98116 |
| 94070 |
+-------+

Interrogez les tables d’échantillon pour trouver les codes postaux dans la table customer_zip_example qui ne sont pas non plus dans la table sales_office_zip_example :

SELECT zip FROM customer_zip_example
MINUS
SELECT zip FROM sales_office_zip_example;
Copy
+-------+
| ZIP   |
|-------|
| 98444 |
| 94066 |
+-------+

UNION [ ALL ]

Combine les ensembles de résultats de deux requêtes :

  • UNION se combine avec l’élimination des doublons.

  • UNION ALL se combine avec l’élimination des doublons.

La valeur par défaut est UNION (c’est-à-dire avec élimination des doublons).

Syntaxe

SELECT ...
UNION [ ALL ]
SELECT ...
Copy

Exemples d’opérateurs UNION

Les exemples suivants utilisent l’opérateur UNION.

Utiliser l’opérateur UNION pour combiner les résultats de deux requêtes

Utiliser l’opérateur UNION pour combiner les jeux de résultats de deux requêtes dans les tables d’échantillons :

SELECT office_name office_or_customer, zip FROM sales_office_zip_example
UNION
SELECT customer, zip FROM customer_zip_example
ORDER BY zip;
Copy
+--------------------+-------+
| OFFICE_OR_CUSTOMER | ZIP   |
|--------------------+-------|
| 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 ('Adams, Douglas');
INSERT INTO union_test2 (i) VALUES (42);
Copy

Exécutez une opération UNION 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;
Copy

Cette requête renvoie une erreur :

100038 (22018): Numeric value 'Adams, Douglas' 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;
Copy
+----------------+
| V::VARCHAR     |
|----------------|
| Adams, Douglas |
| 42             |
+----------------+