두 연산자 모두 두 쿼리의 결과를 서로 쌓기 때문에 SQL UNION과 UNION ALL은 사소한 선택처럼 보일 수 있습니다. 그렇지 않습니다. 추가 단어 하나가 결과와 데이터베이스 작동 강도를 모두 변경합니다.

여기 SQLite 3.50에서 모든 쿼리를 실행했으며 표시된 결과와 타이밍은 해당 실행에서 나온 것입니다. 기본 동작은 주요 데이터베이스 전반의 표준 SQL입니다. 그러나 타이밍은 사용자의 데이터베이스, 데이터 및 하드웨어에 따라 다를 수 있으므로 예시로 여기십시오.

짧은 대답

UNION 두 개의 결과 세트를 결합하고 고유한 행만 반환합니다. UNION ALL는 그것들을 결합하고 중복을 포함한 모든 것을 반환합니다. 이것이 전체적인 차이점이며, 이 기사의 나머지 부분에서는 이것이 왜 중요한지에 대해 설명합니다.

UNION 대 UNION ALL 나란히

샘플 데이터

온라인 주문과 매장 내 주문을 별도의 테이블에 보관하는 매장을 상상해 보세요. Asha는 두 개의 동일한 온라인 주문과 하나의 동일한 매장 주문을 가지고 있습니다.

CREATE TABLE online_orders (customer TEXT, total INT);
CREATE TABLE store_orders (customer TEXT, total INT);

INSERT INTO online_orders VALUES ('Asha',100), ('Ben',200), ('Asha',100);
INSERT INTO store_orders VALUES ('Asha',100), ('Dev',300);

차이점 보기

SELECT customer, total FROM online_orders
UNION
SELECT customer, total FROM store_orders;

-- Asha | 100
-- Ben  | 200
-- Dev  | 300

SELECT customer, total FROM online_orders
UNION ALL
SELECT customer, total FROM store_orders;

-- Asha | 100
-- Ben  | 200
-- Asha | 100
-- Asha | 100
-- Dev  | 300

5개의 입력 행이 UNION로 3개가 되었고, 5개 모두 UNION ALL에 그대로 유지되었습니다. 주목해야 할 두 가지 사항:

• UNION 첫 번째 테이블 내의 항목을 포함하여 전체 결과에서 중복 항목을 제거했습니다. 단순히 테이블을 비교하는 것이 아닙니다.
• 행은 선택한 모든 열이 일치하는 경우에만 중복으로 간주됩니다.

정말로 별개의 목록을 원한다면 UNION이 딱 맞습니다. 이는 두 채널 중 하나를 통해 구매한 모든 고객에게 다음을 제공합니다.

SELECT customer FROM online_orders
UNION
SELECT customer FROM store_orders;

-- Asha
-- Ben
-- Dev

잘못된 전체 버그

이것이 선택이 정말로 중요한 이유입니다. 총 수익을 원한다고 가정해 보겠습니다. 위의 주문을 합하면 800이 됩니다. 각 연산자가 제공하는 내용을 확인하세요.

SELECT SUM(total) FROM (
  SELECT total FROM online_orders
  UNION
  SELECT total FROM store_orders
);
-- 600

SELECT SUM(total) FROM (
  SELECT total FROM online_orders
  UNION ALL
  SELECT total FROM store_orders
);
-- 800

UNION 버전이 200만큼 틀렸습니다. total 열만 선택했기 때문에 3개의 개별 100루피 주문이 중복되어 하나로 축소되었습니다. 오류 메시지도 없으며 600은 완벽하게 믿을 수 있는 숫자처럼 보입니다. 이러한 유형의 조용한 버그는 몇 달 동안 재무 보고서에 남아 있을 수 있습니다.

둘 다 따라야 하는 규칙

두 연산자 모두 동일한 요구 사항을 갖습니다.

1. 각 SELECT은 동일한 수의 열을 반환해야 합니다.
2. 열은 이름이 아닌 위치로 일치하므로 동일한 순서로 유지하세요.
3. 각 위치의 데이터 유형은 호환되어야 합니다.
4. 결과 열 이름은 첫 번째 SELECT에서 나옵니다.
5. ORDER BY은 맨 마지막에 오고 전체 결과에 적용됩니다.

첫 번째 규칙을 위반하면 오류가 발생합니다. SQLite의 메시지는 다음과 같습니다.

SELECT customer, total FROM online_orders
UNION
SELECT customer FROM store_orders;

-- SELECTs to the left and right of UNION do not have
-- the same number of result columns

두 번째 규칙은 오류 없이 실패할 수 있으므로 더 위험합니다. first_name, last_name 등 동일한 유형의 두 열을 서로 바꾸면 쿼리가 실행되고 데이터가 잘못 출력됩니다. SQLite는 유형에 대해서도 완화된 반면 PostgreSQL과 같은 데이터베이스는 더 엄격하며 명시적인 유형이 필요할 수 있습니다CAST.

소스 라벨 지정

매우 실용적인 방법은 각 행의 출처를 표시하는 상수 열을 추가하는 것입니다. 레이블이 다르기 때문에 서로 다른 테이블의 행은 더 이상 서로 중복된 것으로 처리될 수 없습니다.

SELECT customer, total, 'online' AS channel FROM online_orders
UNION ALL
SELECT customer, total, 'store' FROM store_orders
ORDER BY customer, channel;

-- Asha | 100 | online
-- Asha | 100 | online
-- Asha | 100 | store
-- Ben  | 200 | online
-- Dev  | 300 | store

데이터에 라벨이 있으면 GROUP BY를 사용하여 채널별로 판매를 보고하도록 그룹화할 수 있습니다. 여러 단계를 결합해야 하는 경우 결합을 CTE로 묶어 읽기 쉽도록 유지하세요.

성능: 테스트에서 발생한 일

중복 항목을 제거하려면 데이터베이스는 일반적으로 행을 정렬하거나 해싱하여 행을 비교해야 합니다. UNION ALL은 이 단계를 건너뛸 수 있습니다. 우리는 0부터 50,000까지의 임의의 숫자를 포함하는 각각 500,000개의 행을 포함하는 두 개의 테이블을 만들었기 때문에 중복된 항목이 많았습니다. 각 쿼리를 여러 번 실행하여 최적의 시간을 유지했습니다.

먼저, 프로그램으로 행을 다시 보내는 데 소요된 시간이 비교에 영향을 미치지 않도록 결과를 계산했습니다.

큰 격차는 부분적으로 SQLite가 모든 행을 읽지 않고도 해당 개수를 계산할 수 있기 때문에 발생하므로 “수천 배 더 빠르다”고 읽지 마십시오. 그러나 이는 핵심 아이디어를 보여줍니다. UNION은 실제 작업을 수행해야 했지만 UNION ALL는 수행하지 않았습니다.

그런 다음 모든 행을 Python으로 가져왔고 결과가 반전되었습니다.

여기서 UNION ALL은 20배 많은 행을 전달했기 때문에 시간이 더 오래 걸렸습니다. 속도를 늦추는 것은 중복 제거 때문이 아닙니다. 백만 개의 행을 처리했습니다. 따라서 정직한 규칙은 UNION ALL가 데이터베이스 계산에 더 저렴하지만 결과의 크기도 중요하다는 것입니다. 온라인의 많은 차트에서는 이를 “UNION ALL이 항상 더 빠릅니다”로 축소하고 있으며, 우리의 테스트에서는 이것이 왜 그렇게 간단하지 않은지 보여줍니다.

속도만을 기준으로 선택하지 마십시오. 먼저 정답을 제시하는 연산자를 선택한 다음 튜닝하세요. 쿼리가 느린 경우 SQL 쿼리 최적화 가이드를 읽어보세요.

각각을 사용하는 경우

다음과 같은 경우 UNION ALL을 사용하세요.

• 월별 파티션과 같은 유사한 테이블의 데이터를 병합하고 있습니다.
• 나중에 총계나 개수를 계산하게 됩니다.
• 입력이 겹칠 수 없다는 것을 알고 있습니다.
• 중복은 로그나 감사 기록과 마찬가지로 의미가 있습니다.

다음과 같은 경우 UNION을 사용하세요.

• 두 시스템의 모든 이메일 주소와 같은 고유한 목록이 필요합니다.
• 겹치는 소스에는 동일한 사실이 두 번 이상 포함되어 있습니다.

UNION ALL을 기본값으로 취급하고 의도적으로 UNION를 사용하십시오. 조기에 필터링하기 위해 각 분기에 WHERE 절을 추가하면 두 가지 모두에 도움이 됩니다.

교차 및 제외

관련된 두 집합 연산자가 서로 다른 질문에 답합니다. 둘 다 동일한 열 규칙을 따릅니다.

SELECT customer FROM online_orders
INTERSECT
SELECT customer FROM store_orders;
-- Asha  (bought in both channels)

SELECT customer FROM online_orders
EXCEPT
SELECT customer FROM store_orders;
-- Ben   (online only)

Oracle은 전통적으로 EXCEPT 대신 MINUS을 사용했으며 이러한 연산자에 대한 지원은 데이터베이스 및 버전에 따라 조금씩 다릅니다.

UNION 대 JOIN

이는 종종 혼동되지만 데이터를 서로 다른 방향으로 결합합니다.

일반적인 실수

• 습관적으로 UNION을 사용합니다. 속도가 저하되거나 유효한 행이 제거될 수 있습니다.
• 열을 너무 적게 선택했습니다. ID를 생략하면 개별 행이 동일하게 보입니다.
• 열 순서가 일치하지 않습니다. 값이 오류 없이 잘못된 열에 배치될 수 있습니다.
• 각 분기에 ORDER BY를 추가합니다. 마지막에 한 번 정렬합니다.
• UNION을 사용하여 잘못된 조인을 패치합니다. 조인으로 인해 중복이 나타나면 조인을 수정합니다.
• 결과 순서를 가정합니다. ORDER BY가 없으면 두 연산자 모두에 대해 행 순서가 보장되지 않습니다.

마무리

매번 하나의 질문을 하십시오. 동일한 행이 동일한 사실입니까, 아니면 별도의 사실입니까? 분리되어 있거나 중복이 불가능한 경우에는 UNION ALL을 사용하세요. 고유한 목록이 필요한 경우 UNION를 사용하세요. 자신의 보고서를 확인하려면 코드에서 SUM 또는 COUNT 근처에 있는 UNION을 검색하여 해당 합계가 실제로 올바른지 확인하세요.

자주 묻는 질문

계속 학습하세요