세트 연산자¶
설정 연산자는 여러 쿼리 블록의 중간 결과를 하나의 결과 세트로 결합합니다.
일반 구문¶
[ ( ] <query> [ ) ]
{
INTERSECT |
{ MINUS | EXCEPT } |
UNION [ { DISTINCT | ALL } ] [ BY NAME ]
}
[ ( ] <query> [ ) ]
[ ORDER BY ... ]
[ LIMIT ... ]
일반적인 사용법 노트¶
각 쿼리는 자체적으로 쿼리 연산자를 포함할 수 있으므로 여러 쿼리 식을 세트 연산자와 결합할 수 있습니다.
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는 우선 순위가 같은 연산자를 왼쪽에서 오른쪽으로 처리합니다.
괄호를 사용하여 식을 다른 순서로 평가하도록 적용할 수 있습니다.
모든 데이터베이스 벤더가 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');
INTERSECT¶
한 쿼리의 결과 세트에서 중복 제거와 함께 다른 쿼리의 결과 세트에도 나타나는 행을 반환합니다.
구문¶
[ ( ] <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 키워드는 같은 의미를 가지고 있고 서로 바꾸어 사용할 수 있습니다.
구문¶
[ ( ] <query> [ ) ]
MINUS
[ ( ] <query> [ ) ]
[ ( ] <query> [ ) ]
EXCEPT
[ ( ] <query> [ ) ]
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;
+-------------+
| 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;
+-------------+
| 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> [ ) ]
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;
+--------------------+-------------+
| 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_test1
의 VARCHAR 값 및 union_test2
의 INTEGER 값)을 사용하여 열 위치별 UNION 작업을 실행합니다.
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 |
+-------------+