집합 연산자

집합 연산자 (Set operators)

이 문서는 Snowflake의 집합 연산자에 대해 설명해요. INTERSECT, MINUS/EXCEPT, UNION [DISTINCT|ALL] [BY NAME] 연산자가 여러 쿼리 블록의 중간 결과를 하나의 결과 집합으로 결합하는 방법, 문법, 주의사항과 예시를 다루어요.

출처: Snowflake SQL Reference

본문

집합 연산자는 여러 쿼리 블록의 중간 결과를 하나의 결과 집합으로 결합해요.

일반 문법 (General syntax)

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

일반 사용 시 주의사항 (General usage notes)

• 각 쿼리는 자체적으로 쿼리 연산자를 포함할 수 있으므로, 여러 쿼리 표현식을 집합 연산자로 결합할 수 있어요.
• 집합 연산자의 결과에 ORDER BY와 LIMIT / FETCH 절을 적용할 수 있어요.
• 이 연산자들을 사용할 때:
  • UNION BY NAME 또는 UNION ALL BY NAME을 포함하는 쿼리를 제외하면, 각 쿼리가 같은 수의 컬럼을 선택하도록 하세요.
  • 서로 다른 소스의 행들에서 각 컬럼의 데이터 타입이 일관되도록 하세요. "UNION 연산자 사용 및 데이터 타입 불일치 캐스팅" 섹션의 예시 중 하나가 데이터 타입이 일치하지 않을 때의 잠재적 문제와 해결책을 보여줘요.
  • 일반적으로 컬럼의 데이터 타입뿐 아니라 "의미"도 일치하도록 하세요. 다음 UNION ALL 연산자를 사용하는 쿼리는 원하는 결과를 만들지 못해요:

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

별표(*)를 사용해서 테이블의 모든 컬럼을 지정하면 오류 위험이 커져요. 예를 들어:

SELECT * FROM table1
UNION ALL
SELECT * FROM table2;

테이블의 컬럼 수가 같지만 컬럼 순서가 같지 않으면, 이 연산자들을 사용할 때 쿼리 결과가 잘못될 가능성이 커요. UNION BY NAME과 UNION ALL BY NAME 연산자는 이 시나리오에서 예외예요. 예를 들어 다음 쿼리는 올바른 결과를 반환해요:

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

• 출력 컬럼의 이름은 첫 번째 쿼리의 컬럼 이름에 기반해요. 예를 들어 다음 쿼리를 고려해 봐요:

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

이 쿼리는 다음 쿼리처럼 동작해요:

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

• 집합 연산자의 우선순위는 ANSI 및 ISO SQL 표준과 일치해요:
  • UNION [ALL]과 MINUS (EXCEPT) 연산자는 동일한 우선순위를 가져요.
  • INTERSECT 연산자는 UNION [ALL]과 MINUS (EXCEPT)보다 우선순위가 높아요.

Snowflake는 동일한 우선순위의 연산자를 왼쪽에서 오른쪽으로 처리해요.

괄호를 사용해서 표현식이 다른 순서로 평가되도록 강제할 수 있어요.

모든 데이터베이스 벤더가 집합 연산자의 우선순위에 대해 ANSI/ISO 표준을 따르는 것은 아니에요. Snowflake는 특히 다른 벤더에서 Snowflake로 코드를 포팅하거나, Snowflake와 다른 데이터베이스에서 실행할 수 있는 코드를 작성할 때 괄호를 사용해 평가 순서를 지정할 것을 권장해요.

예시용 샘플 테이블 (Sample tables for examples)

이 주제의 일부 예시는 다음 샘플 테이블을 사용해요. 두 테이블 모두 우편 번호 컬럼을 가져요. 한 테이블은 각 영업 사무소의 우편 번호를 기록하고, 다른 테이블은 각 고객의 우편 번호를 기록해요.

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

한 쿼리의 결과 집합에 나타나면서 동시에 다른 쿼리의 결과 집합에도 나타나는 행을 중복 제거와 함께 반환해요.

문법 (Syntax)

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

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;

+-------------+
| POSTAL_CODE |
|-------------|
| 94061       |
| 98005       |
+-------------+

MINUS , EXCEPT

첫 번째 쿼리가 반환하는 행 중 두 번째 쿼리도 반환하지 않는 행을 반환해요.

MINUS와 EXCEPT 키워드는 같은 의미를 가지며 서로 바꿔 쓸 수 있어요.

문법 (Syntax)

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

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

MINUS 연산자 예시

sales_office_postal_example 테이블에는 있지만 customer_postal_example 테이블에는 없는 우편 번호를 찾으려면 샘플 테이블을 쿼리해요:

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

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

customer_postal_example 테이블에는 있지만 sales_office_postal_example 테이블에는 없는 우편 번호를 찾으려면 샘플 테이블을 쿼리해요:

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 ]

두 쿼리의 결과 집합을 결합해요:

• UNION [ DISTINCT ]는 행을 컬럼 위치별로 중복 제거와 함께 결합해요.
• UNION ALL은 행을 컬럼 위치별로 중복 제거 없이 결합해요.
• UNION [ DISTINCT ] BY NAME은 행을 컬럼 이름별로 중복 제거와 함께 결합해요.
• UNION ALL BY NAME은 행을 컬럼 이름별로 중복 제거 없이 결합해요.

기본값은 UNION DISTINCT예요 (즉, 행을 컬럼 위치별로 중복 제거와 함께 결합). DISTINCT 키워드는 선택적이에요. DISTINCT 키워드와 ALL 키워드는 상호 배타적이에요.

결합하는 테이블에서 컬럼 위치가 일치하면 UNION 또는 UNION ALL을 사용하세요. UNION BY NAME 또는 UNION ALL BY NAME은 다음 경우에 사용하세요:

• 결합하는 테이블의 컬럼 순서가 제각각인 경우
• 컬럼이 추가되거나 재정렬되는, 진화하는 스키마가 있는 테이블을 결합하는 경우
• 테이블에서 위치가 다른 컬럼의 부분집합을 결합하려는 경우

문법 (Syntax)

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

BY NAME 절 사용 시 주의사항

일반 사용 시 주의사항에 더해 다음 주의사항이 UNION BY NAME과 UNION ALL BY NAME에 적용돼요:

• 같은 식별자를 가진 컬럼이 일치되어 결합돼요. 큰따옴표 없이 쓴 식별자(unquoted identifier)의 일치는 대소문자를 구분하지 않고, 큰따옴표로 감싼 식별자(quoted identifier)의 일치는 대소문자를 구분해요.
• 입력은 같은 수의 컬럼을 가질 필요가 없어요. 한 입력에는 있지만 다른 입력에는 없는 컬럼은 결합된 결과 집합에서 각 행마다 NULL 값으로 채워져요.
• 결합된 결과 집합의 컬럼 순서는 고유 컬럼이 처음 나타나는 순서대로 왼쪽에서 오른쪽으로 결정돼요.

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;

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

두 쿼리의 결과 집합을 컬럼 이름별로 결합하려면 UNION BY NAME 연산자를 사용해요:

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

출력에서 쿼리가 중복 행(컬럼 A가 3, 컬럼 B가 three인 행)을 제거했음을 확인할 수 있어요.

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

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

두 테이블에서 컬럼 이름의 대소문자가 일치하지 않는 것에 주목하세요. 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;

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;

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

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

데이터 타입이 다른 컬럼 위치 기준 UNION 연산을 실행해 봐요 (union_test1의 VARCHAR 값과 union_test2의 INTEGER 값):

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

더 알아보기 (Learn more)

• 쿼리 연산자 개요 (Query operators)
• ORDER BY 절
• LIMIT / FETCH 절