세트 연산자

설정 연산자는 여러 쿼리 블록의 중간 결과를 하나의 결과 세트로 결합합니다.

일반 구문

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

일반적인 사용법 노트

  • 각 쿼리는 자체적으로 쿼리 연산자를 포함할 수 있으므로 여러 쿼리 식을 세트 연산자와 결합할 수 있습니다.

  • ORDER BYLIMIT / FETCH 절을 세트 연산자의 결과에 적용할 수 있습니다.

  • 이들 연산자를 사용할 때 다음 사항이 적용됩니다.

    • UNION BY NAME 또는 UNION ALL BY NAME이 포함된 쿼리를 제외하고 각 쿼리가 동일한 수의 열을 선택하는지 확인합니다.

    • 각 열의 데이터 타입이 서로 다른 원본의 행 전체에서 일치하는지 확인하십시오. UNION 연산자를 사용하여 일치하지 않는 데이터 타입을 형 변환합니다 섹션의 예제 중 하나는 데이터 타입이 일치하지 않을 때 발생할 수 있는 문제와 해결 방법을 보여줍니다.

    • 일반적으로 열의 “의미”와 데이터 타입이 일치하는지 확인하세요. UNION ALL 연산자가 포함된 다음 쿼리는 원하는 결과를 생성하지 않습니다.

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

      예를 들어 다음과 같이 별표를 사용해 테이블의 모든 열을 지정할 때 오류가 발생할 위험이 증가합니다.

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

      테이블의 열 수가 같지만 열의 순서가 같지 않은 경우 이러한 연산자를 사용할 때 쿼리 결과가 올바르지 않을 수 있습니다.

      UNION BY NAME 및 UNION ALL BY NAME 연산자는 이 시나리오에서 예외입니다. 예를 들어, 다음 쿼리는 올바른 결과를 반환합니다.

      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는 우선 순위가 같은 연산자를 왼쪽에서 오른쪽으로 처리합니다.

    괄호를 사용하여 식을 다른 순서로 평가하도록 적용할 수 있습니다.

    모든 데이터베이스 벤더가 set 연산자의 우선 순위에 대해 ANSI/ISO 표준을 따르는 것은 아닙니다. 특히 다른 벤더의 코드를 Snowflake로 포팅하거나 Snowflake뿐 아니라 다른 데이터베이스에서도 실행할 수 있는 코드를 작성하는 경우 괄호를 사용하여 평가 순서를 지정하는 것이 좋습니다.

예제용 샘플 테이블

이 항목의 일부 예제에서는 다음 샘플 테이블을 사용합니다. 두 테이블 모두 우편 번호 열이 있습니다. 한 테이블에는 각 영업소의 우편 번호가 기록되고 다른 테이블에는 각 고객의 우편 번호가 기록됩니다.

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

INTERSECT

한 쿼리의 결과 세트에서 중복 제거와 함께 다른 쿼리의 결과 세트에도 나타나는 행을 반환합니다.

구문

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

INTERSECT 연산자 예제

sales_office_postal_example 테이블 및 customer_postal_example 테이블에 모두 있는 우편 번호를 찾으려면 다음과 같이 샘플 테이블 을 쿼리합니다.

SELECT postal_code FROM sales_office_postal_example
INTERSECT
SELECT postal_code FROM customer_postal_example
ORDER BY postal_code;
Copy
+-------------+
| POSTAL_CODE |
|-------------|
| 94061       |
| 98005       |
+-------------+

MINUS , EXCEPT

첫 번째 쿼리에서 반환한 행 중 두 번째 쿼리에서도 반환되지 않은 행을 반환합니다.

MINUS 및 EXCEPT 키워드는 같은 의미를 가지고 있고 서로 바꾸어 사용할 수 있습니다.

구문

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

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

MINUS 연산자 예제

샘플 테이블<label-operators_set_sample_tables>`을 쿼리하여 ``customer_postal_example` 테이블에도 없는 우편 번호를 sales_office_postal_example 테이블에서 찾습니다.

SELECT postal_code FROM sales_office_postal_example
MINUS
SELECT postal_code FROM customer_postal_example
ORDER BY postal_code;
Copy
+-------------+
| POSTAL_CODE |
|-------------|
| 94070       |
| 98116       |
+-------------+

샘플 테이블<label-operators_set_sample_tables>`을 쿼리하여 ``sales_office_postal_example` 테이블에도 없는 우편 번호를 customer_postal_example 테이블에서 찾습니다.

SELECT postal_code FROM customer_postal_example
MINUS
SELECT postal_code FROM sales_office_postal_example
ORDER BY postal_code;
Copy
+-------------+
| POSTAL_CODE |
|-------------|
| 94066       |
| 98444       |
+-------------+

UNION [ { DISTINCT | ALL } ] [ BY NAME ]

두 쿼리의 결과 세트를 결합합니다.

  • UNION [ DISTINCT ]는 중복 제거를 사용하여 열 위치별로 행을 결합합니다.

  • UNION ALL은 중복 제거 없이 열 위치별로 행을 결합합니다.

  • UNION [ DISTINCT] BYNAME은 중복 제거를 사용하여 열 이름별로 행을 결합합니다.

  • UNION ALL BY NAME은 중복 제거 없이 열 이름별로 행을 결합합니다.

기본값은 UNION DISTINCT(즉, 중복 제거를 사용하여 열 위치별로 행 결합)입니다. DISTINCT 키워드는 선택 사항입니다. DISTINCT 키워드 및 ALL 키워드는 함께 사용할 수 없습니다.

UNION 또는 UNION ALL은 결합하는 테이블에서 열 위치가 일치하는 경우에 사용합니다. UNION BY NAME 또는 UNION ALL BY NAME은 다음 사용 사례에 사용합니다.

  • 결합하는 테이블의 열 순서는 다양합니다.

  • 결합하는 테이블에는 열이 추가되거나 재정렬되는 진화하는 스키마가 있습니다.

  • 테이블에서 위치가 다른 열의 하위 세트를 결합하려고 합니다.

구문

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

BY NAME 절에 대한 사용법 노트

일반 사용법 노트 외에도 다음 사용법 노트가 UNION BY NAME 및 UNION ALL BY NAME에 적용됩니다.

  • 식별자가 동일한 열이 일치하고 결합됩니다. 따옴표가 없는 식별자의 일치는 대소문자를 구분하지 않으며, 따옴표가 있는 식별자의 일치는 대소문자를 구분합니다.

  • 입력의 열 개수가 같을 필요는 없습니다. 열이 한 입력에는 존재하지만 다른 입력에는 존재하지 않는 경우 누락된 각 행에 대한 결합된 결과 세트에서 NULL 값으로 채워집니다.

  • 결합된 결과 세트의 열 순서는 고유 열이 처음 발견될 때 왼쪽에서 오른쪽으로의 순서에 따라 결정됩니다.

UNION 연산자 예제

다음 예제에서는 UNION 연산자를 사용합니다.

열 위치를 기준으로 두 쿼리의 결과 결합

샘플 테이블 에서 두 쿼리의 열 위치를 기준으로 결과 세트를 결합하려면 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;
Copy
+--------------------+-------------+
| 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);
Copy

두 쿼리의 열 이름을 기준으로 결과 세트를 결합하려면 UNION BY NAME 연산자를 사용합니다.

SELECT * FROM union_demo_column_order1
UNION BY NAME
SELECT * FROM union_demo_column_order2
ORDER BY a;
Copy
+---+-------+
| A | B     |
|---+-------|
| 1 | one   |
| 2 | two   |
| 3 | three |
| 4 | four  |
+---+-------+

출력은 쿼리가 중복 행을 제거했음을 보여줍니다(열 A3, 열 Bthree).

중복 제거 없이 테이블을 결합하려면 UNION ALL BY NAME 연산자를 사용합니다.

SELECT * FROM union_demo_column_order1
UNION ALL BY NAME
SELECT * FROM union_demo_column_order2
ORDER BY a;
Copy
+---+-------+
| A | B     |
|---+-------|
| 1 | one   |
| 2 | two   |
| 3 | three |
| 3 | three |
| 4 | four  |
+---+-------+

두 테이블에서 열 이름의 대소문자가 일치하지 않습니다. 열 이름이 union_demo_column_order1 테이블에서는 소문자이고 union_demo_column_order2 테이블에서는 대문자입니다. 열 이름을 따옴표로 묶은 쿼리를 실행하면 따옴표로 묶인 식별자의 일치는 대/소문자를 구분하므로 오류가 반환됩니다. 예를 들어, 다음 쿼리는 열 이름을 따옴표로 묶습니다.

SELECT 'a', 'b' FROM union_demo_column_order1
UNION ALL BY NAME
SELECT 'B', 'A' FROM union_demo_column_order2
ORDER BY a;
Copy
000904 (42000): SQL compilation error: error line 4 at position 9
invalid identifier 'A'

별칭을 사용하여 열 이름이 다른 두 쿼리의 결과 결합

UNION BY NAME 연산자를 사용하여 샘플 테이블 에서 두 쿼리의 열 이름을 기준으로 결과 세트를 결합하는 경우 열 이름이 일치하지 않기 때문에 결과 세트의 행에는 NULL 값이 있습니다.

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

출력은 식별자가 다른 열이 결합되지 않고, 한 테이블에는 있지만 다른 테이블에는 없는 열의 행 값이 NULL임을 보여줍니다. postal_code 열이 두 테이블에 모두 있으므로 postal_code 열의 출력에는 NULL 값이 없습니다.

다음 쿼리는 쿼리 기간 동안 이름이 다른 열이 같은 이름을 갖도록 별칭 office_or_customer 를 사용합니다.

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;
Copy
+--------------------+-------------+
| OFFICE_OR_CUSTOMER | POSTAL_CODE |
|--------------------+-------------|
| 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 ('Smith, Jane');
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 'Smith, Jane' is not recognized

이제 명시적 캐스팅을 사용해 입력값을 호환 가능한 형식으로 변환합니다.

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