演算子のセット

セット演算子は、複数のクエリブロックの中間結果を1つの結果セットにまとめます。

一般的な構文

[ ( ] <query> [ ) ] { INTERSECT | { MINUS | EXCEPT } | UNION [ ALL ] } [ ( ] <query> [ ) ]
[ ORDER BY ... ]
[ LIMIT ... ]
Copy

一般的な使用上の注意

  • 各クエリ自体にクエリ演算子を含めると、任意の数のクエリ式をセット演算子と組み合わせることができます。

  • ORDER BY 句と LIMIT / FETCH 句は、集合演算子の結果に適用されます。

  • これらの演算子を使用する場合:

    • 各クエリが同じ数の列を選択することを確認してください。

    • 各列のデータ型が、異なるソースからの行全体で一貫していることを確認してください。 UNION 演算子を使用し、不一致のデータ型をキャストする セクションにある例の1つは、データ型が一致しない場合に起こりうる問題と解決策を示しています。

    • 一般に、列の「意味」とデータ型は一致する必要があります。次を実行しても、望ましい結果は得られません。

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

      アスタリスクを使用してテーブルのすべての列を指定すると、エラーのリスクが増加します。次に例を示します。

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

      テーブルの列数が同じでも、列の順序が同じでなければ、クエリ結果はおそらく正しくありません。

    • 出力列の名前は、最初のクエリ列の名前に基づいています。たとえば、次のクエリを考慮します。

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

      このクエリは、あたかも以下のように動作します。

      SELECT LastName, FirstName FROM employees
      UNION ALL
      SELECT FirstName AS LastName, LastName AS FirstName FROM contractors;
      
      Copy
  • セット演算子の優先順位は、ANSI および ISO SQL 標準と一致します。

    • UNION [ALL]および MINUS (EXCEPT)演算子の優先順位は同じです。

    • INTERSECT 演算子は、 UNION [ALL]および MINUS (EXCEPT)よりも優先順位が高くなります。

    優先順位が等しい演算子は、左から右に処理されます。

    括弧を使用して、式を強制的に異なる順序で評価することができます。

    一部のデータベースベンダーは、集合演算子の優先順位について ANSI/ISO 標準に従っていません。Snowflakeは、特に別のベンダーからSnowflakeにコードを移植する場合や、Snowflakeだけでなく他のデータベースでも実行する可能性のあるコードを作成する場合に、括弧を使用して評価の順序を指定することをお勧めします。

例向けのサンプルテーブル

このトピックの例では、以下のサンプルテーブルを使用します。どちらのテーブルにも郵便番号列があります。1つのテーブルには各営業所の郵便番号が記録され、もう1つのテーブルには各顧客の郵便番号が記録されます。

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

あるクエリの結果セットから行を返します。この行は別のクエリの結果セットにも表示され、重複を排除します。

構文

SELECT ...
INTERSECT
SELECT ...
Copy

INTERSECT 演算子の例

サンプルテーブル をクエリして、営業所と顧客の両方がある郵便番号を検索します。

SELECT zip FROM sales_office_zip_example
INTERSECT
SELECT zip FROM customer_zip_example;
Copy
+-------+
| ZIP   |
|-------|
| 94061 |
| 98005 |
+-------+

MINUS , EXCEPT

最初のクエリが返した行のうち、2番目のクエリが返さない行を返します。

MINUS および EXCEPT キーワードは同じ意味を持ち、同じ意味で使用できます。

構文

SELECT ...
MINUS
SELECT ...

SELECT ...
EXCEPT
SELECT ...
Copy

MINUS 演算子の例

サンプルテーブル をクエリして、 sales_office_zip_example テーブルにある郵便番号のうち、 customer_zip_example テーブルにもないものを探します。

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

サンプルテーブル をクエリして、 customer_zip_example テーブルにある郵便番号のうち、 sales_office_zip_example テーブルにもないものを探します。

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

UNION [ ALL ]

2つのクエリの結果セットを結合します。

  • UNION は、重複排除と組み合わせます。

  • UNION ALL は、重複排除なしで結合します。

デフォルトは UNION (つまり、重複除去あり)です。

構文

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

UNION 演算子の例

以下の例では、 UNION 演算子を使用します。

UNION 演算子を使用して、2つのクエリの結果を結合する

サンプルテーブル にある2つのクエリからの結果セットを結合するには、 UNION 演算子を使用します。

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

UNION 演算子を使用し、不一致のデータ型をキャストする

この例では、 UNION 演算子でデータ型が一致しない場合に起こりうる問題を示し、その解決策を示します。

テーブルを作成し、いくつかのデータを挿入することから始めます。

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

異なるデータ型(union_test1 に VARCHAR 値、 union_test2 に INTEGER 値)で UNION 操作を実行します。

SELECT v FROM union_test1
UNION
SELECT i FROM union_test2;
Copy

このクエリはエラーを返します。

100038 (22018): Numeric value 'Adams, Douglas' is not recognized

次に、明示的なキャストを使用して、入力を互換性のある型に変換します。

SELECT v::VARCHAR FROM union_test1
UNION
SELECT i::VARCHAR FROM union_test2;
Copy
+----------------+
| V::VARCHAR     |
|----------------|
| Adams, Douglas |
| 42             |
+----------------+