Mengenoperatoren¶

Allgemeine Syntax¶

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

Allgemeine Nutzungshinweise¶

• Jede Abfrage kann selbst Abfrageoperatoren enthalten, sodass Sie mehrere Abfrageausdrücke mit Mengenoperatoren kombinieren können.

• Sie können die ORDER BY- und LIMIT / FETCH-Klauseln auf das Ergebnis des Mengenoperators anwenden.

• Bei Verwendung dieser Operatoren:

  • Stellen Sie sicher, dass für jede Abfrage die gleiche Anzahl von Spalten ausgewählt wird, mit Ausnahme von Abfragen, die UNION BY NAME oder UNION ALL BY NAME enthalten.

  • Stellen Sie sicher, dass die Datentypen der einzelnen Spalten in allen Zeilen der verschiedenen Quellen konsistent sind. Eines der Beispiele im Abschnitt Verwenden Sie den UNION-Operator und wandeln Sie nicht übereinstimmende Datentypen um veranschaulicht das mögliche Problem und die Lösung, wenn die Datentypen nicht übereinstimmen.

  • Stellen Sie generell sicher, dass die „Bedeutungen“ sowie die Datentypen der Spalten übereinstimmen. Die folgende Abfrage mit dem UNION ALL-Operator führt nicht zu den gewünschten Ergebnissen:

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

    Copy

    Das Fehlerrisiko erhöht sich, wenn Sie ein Sternchen verwenden, um alle Spalten einer Tabelle auszuwählen. Beispiel:

    SELECT * FROM table1
    UNION ALL
    SELECT * FROM table2;
    

    Copy

    Wenn die Anzahl der Spalten in den Tabellen gleich ist, die Spalten jedoch nicht in derselben Reihenfolge angeordnet sind, werden die Abfrageergebnisse wahrscheinlich fehlerhaft ausfallen, wenn Sie diese Operatoren verwenden.

    Die UNION BY NAME- und UNION ALL BY NAME-Operatoren sind Ausnahmen für dieses Szenario. Beispielsweise gibt die folgende Abfrage die richtigen Werte zurück:

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

    Copy

  • Die Namen der Ausgabespalten basieren auf den Namen der Spalten der ersten Abfrage. Betrachten wir beispielsweise die folgende Abfrage:

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

    Copy

    Diese Abfrage verhält sich, als wäre die Abfrage wie folgt:

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

    Copy

• Die Rangfolge der Mengenoperatoren entspricht den ANSI- und ISO SQL-Standards:

  • Die Operatoren UNION [ALL] und MINUS (EXCEPT) haben die gleiche Priorität.

  • Der Operator INTERSECT hat Vorrang vor UNION [ALL] und MINUS (EXCEPT).

  Snowflake verarbeitet Operatoren gleicher Priorität von links nach rechts.

  Sie können Klammern verwenden, um das Auswerten der Ausdrücke in einer anderen Reihenfolge zu erzwingen.

  Nicht alle Datenbankanbieter folgen dem ANSI/ISO-Standard für die Rangfolge von Mengenoperatoren. Snowflake empfiehlt die Verwendung von Klammern zur Angabe der Auswertungsreihenfolge, insbesondere wenn Sie Code von einem anderen Anbieter in Snowflake portieren oder Code schreiben, den Sie möglicherweise auf anderen Datenbanken oder auf Snowflake ausführen.

Beispieltabellen für Beispiele¶

Für einige der Beispiele in diesem Thema werden die folgenden Beispieltabellen verwendet. Beide Tabellen haben eine Spalte für Postleitzahlen. In einer Tabelle wird die Postleitzahl jedes Verkaufsbüros erfasst, in der anderen die Postleitzahl jedes Kunden.

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¶

Gibt Zeilen mit Duplikatlöschung aus dem Resultset einer Abfrage zurück, die auch im Resultset einer anderen Abfrage erscheinen.

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

Gibt die von der ersten Abfrage zurückgegebenen Zeilen zurück, die nicht auch von der zweiten Abfrage zurückgegeben werden.

Die Schlüsselwörter MINUS und EXCEPT haben dieselbe Bedeutung und können austauschbar verwendet werden.

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

Kombiniert die Resultsets aus zwei Abfragen:

• UNION [ DISTINCT ] kombiniert Zeilen nach Spaltenposition mit Löschung von Duplikaten.

• UNION ALL kombiniert Zeilen nach Spaltenposition ohne Löschung von Duplikaten.

• UNION [ DISTINCT ] BY NAME kombiniert Zeilen nach Spaltenname mit Löschung von Duplikaten.

• UNION ALL BY NAME kombiniert Zeilen nach Spaltenname ohne Löschung von Duplikaten.

Der Standardwert ist UNION DISTINCT (d. h. Kombinieren von Zeilen nach Spaltenposition mit Löschung von Duplikaten). Das Schlüsselwort DISTINCT ist optional. Das Schlüsselwort DISTINCT und das Schlüsselwort ALL schließen sich gegenseitig aus.

Verwenden Sie UNION oder UNION ALL, wenn die Spaltenpositionen in den Tabellen, die Sie kombinieren, übereinstimmen. Verwenden Sie UNION BY NAME oder UNION ALL BY NAME für die folgenden Anwendungsfälle:

• Die Tabellen, die Sie kombinieren, haben unterschiedliche Spaltenreihenfolgen.

• Die Tabellen, die Sie kombinieren, haben sich weiterentwickelnde Schemas, in denen Spalten hinzugefügt oder neu angeordnet werden.

• Sie möchten Teilmengen von Spalten kombinieren, die unterschiedliche Positionen in den Tabellen haben.

[ ( ] <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          |
+-------------+