演算子のセット¶

一般的な構文¶

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

一般的な使用上の注意¶

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

• ORDER BY および LIMIT / FETCH 句を集合演算子の結果に適用できます。

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

  • UNIONBYNAME または UNIONALLBYNAME を含むクエリを除き、各クエリは同じ数の列を選択するようにしてください。

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

  • 一般に、列の「意味」とデータ型は一致する必要があります。UNIONALL 演算を使用した次のクエリでは望ましい結果が得られません。

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

    Copy

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

    SELECT * FROM table1
    UNION ALL
    SELECT * FROM table2;
    

    Copy

    テーブルの列数が同じでも、列の順序が同じでなければ、これらの演算子を使用するとクエリ結果が正しくなくなる可能性があります。

    UNIONBYNAME および UNIONALLBYNAME 演算子は、このシナリオの例外です。たとえば、次のクエリは正しい結果を返します。

    SELECT LastName, FirstName FROM employees
    UNION ALL BY NAME
    SELECT FirstName, LastName FROM contractors;
    

    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）よりも優先順位が高くなります。

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

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

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

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

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

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¶

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

[ ( ] <query> [ ) ]
INTERSECT
[ ( ] <query> [ ) ]

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¶

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

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

[ ( ] <query> [ ) ]
MINUS
[ ( ] <query> [ ) ]

[ ( ] <query> [ ) ]
EXCEPT
[ ( ] <query> [ ) ]

SELECT postal_code FROM sales_office_postal_example
MINUS
SELECT postal_code FROM customer_postal_example
ORDER BY postal_code;

+-------------+
| POSTAL_CODE |
|-------------|
| 94070       |
| 98116       |
+-------------+

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 ]¶

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

• UNION [ DISTINCT] は、列位置によって行を結合し、重複排除を行います。

• UNION ALL は、重複排除することなく、列位置で行を結合します。

• UNION [ DISTINCT] BYNAME は、列名で行を結合し、重複排除を行います。

• UNION ALL BY NAME は、重複排除することなく、列名で行を結合します。

デフォルトは UNIONDISTINCT（重複排除を行い列位置で行を結合）です。キーワード DISTINCT はオプションです。DISTINCT キーワードと ALL キーワードは相互に排他的です。

結合するテーブルの列位置が一致する場合、UNION または UNIONALL を使用します。次のユースケースの場合、 UNIONBYNAME または UNIONALLBYNAME を使用します。

• 結合しているテーブルは、列の順序が異なります。

• 結合するテーブルには、列が追加または並べ替えられる進化したスキーマがあります。

• テーブル内で異なる位置にある列のサブセットを結合します。

[ ( ] <query> [ ) ]
UNION [ { DISTINCT | ALL } ] [ BY NAME ]
[ ( ] <query> [ ) ]

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

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);

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

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

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'

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

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

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);

SELECT v FROM union_test1
UNION
SELECT i FROM union_test2;

100038 (22018): Numeric value 'Smith, Jane' is not recognized

SELECT v::VARCHAR FROM union_test1
UNION
SELECT i::VARCHAR FROM union_test2;

+-------------+
| V::VARCHAR  |
|-------------|
| Smith, Jane |
| 42          |
+-------------+