시스매

SQL 집합 연산자 완벽 정복 핸드북 데이터를 자유자재로 합치고 비교하는 기술

16 min read

2025-08-20 22:32

Tags:

SQL 집합 연산자 완벽 정복 핸드북 데이터를 자유자재로 합치고 비교하는 기술

데이터를 다루다 보면 여러 테이블에 나뉘어 저장된 정보를 하나로 합치거나, 두 데이터 그룹 간의 공통점 또는 차이점을 찾아야 하는 경우가 비일비재합니다. 마치 여러 개의 엑셀 시트를 합치거나 비교하는 작업과 같죠. 이때 대부분의 개발자나 분석가들은 JOIN을 먼저 떠올립니다. 하지만 JOIN이 열(column)을 기준으로 데이터를 수평적으로 확장하는 방식이라면, 지금부터 다룰 **집합 연산자(Set Operators)**는 행(row)을 기준으로 데이터를 수직적으로 쌓거나 비교하는, 전혀 다른 차원의 강력한 도구입니다.

이 핸드북에서는 SQL 집합 연산자의 탄생 배경부터 네 가지 핵심 연산자(UNION, UNION ALL, INTERSECT, EXCEPT)의 정확한 사용법, 그리고 JOIN과의 근본적인 차이점과 실전 활용 시나리오까지, 당신이 데이터를 더욱 깊이 있고 유연하게 다룰 수 있도록 모든 것을 알려드릴 것입니다.

1. 집합 연산자는 왜 만들어졌을까? (The ‘Why’)

데이터베이스 설계의 기본 원칙 중 하나는 ‘정규화(Normalization)‘입니다. 데이터의 중복을 최소화하고 일관성을 유지하기 위해 정보를 논리적인 단위로 나누어 여러 테이블에 저장하는 것이죠. 예를 들어, 한 회사에 ‘현역 직원’ 테이블과 ‘퇴사 직원’ 테이블이 따로 존재할 수 있습니다.

어느 날, “지금까지 우리 회사에 다녔던 모든 직원의 명단을 뽑아주세요”라는 요청을 받았다고 가정해 봅시다. JOIN을 사용하려면 두 테이블을 연결할 공통 키가 있어야 하고, 구조가 복잡해질 수 있습니다. 하지만 만약 두 테이블의 구조(컬럼의 수와 데이터 타입)가 같다면, 단순히 두 테이블의 결과를 위아래로 이어 붙이는 것이 훨씬 직관적이고 효율적입니다.

바로 이런 **‘수직적 데이터 결합’**의 필요성 때문에 집합 연산자가 탄생했습니다. 집합 연산자는 여러 SELECT 문의 결과 집합(Result Set)을 마치 수학의 집합처럼 다루게 해줍니다. 두 리스트를 합치고(합집합), 공통된 항목만 찾고(교집합), 한쪽에만 있는 항목을 찾는(차집합) 등의 작업을 SQL 쿼리 한 줄로 간편하게 처리할 수 있게 된 것입니다.

2. SQL 집합 연산의 네 가지 핵심 도구

SQL 집합 연산자는 크게 네 가지로 나뉩니다. 이들을 수학의 벤다이어그램과 함께 생각하면 이해하기 쉽습니다.

Table A와 Table B라는 두 개의 결과 집합이 있다고 상상해 보세요.

가. UNION (합집합 - 중복 제거)

UNION은 두 개 이상의 SELECT 문의 결과 집합을 하나로 합칩니다. 가장 큰 특징은 자동으로 중복된 행을 제거한다는 것입니다.

  • 개념: A와 B의 모든 데이터를 합치되, 양쪽에 모두 존재하는 데이터는 한 번만 보여줍니다. (A ∪ B)

  • 비유: 두 개의 파티 초대 명단(A, B)을 합쳐 최종 초대 명단을 만들 때, 두 명단에 모두 이름이 있는 사람은 한 번만 적는 것과 같습니다.

  • 구문:

SELECT column1, column2 FROM table_A
UNION
SELECT column1, column2 FROM table_B;

나. UNION ALL (합집합 - 중복 포함)

UNION ALL도 UNION처럼 두 결과 집합을 합치지만, 중복을 제거하지 않고 그대로 모두 보여줍니다.

  • 개념: A와 B의 모든 데이터를 그대로 이어 붙입니다.

  • 비유: 두 파티 초대 명단을 그냥 테이프로 이어 붙여서, 중복된 이름이 있더라도 그대로 두는 것입니다.

  • 성능: UNION은 중복을 제거하기 위해 내부적으로 데이터를 정렬하고 비교하는 추가 작업을 수행합니다. 따라서 중복 여부가 중요하지 않거나 중복된 데이터가 없다는 것이 확실하다면, UNION ALL을 사용하는 것이 성능상 훨씬 유리합니다.

  • 구문:

    SELECT column1, column2 FROM table_A
UNION ALL
SELECT column1, column2 FROM table_B;

다. INTERSECT (교집합)

INTERSECT는 여러 SELECT 문의 결과 중에서 모든 결과 집합에 공통적으로 존재하는 행만을 반환합니다.

  • 개념: A와 B에 모두 존재하는 데이터만 보여줍니다. (A ∩ B)

  • 비유: 두 파티 초대 명단을 비교하여, 양쪽 명단에 모두 이름이 올라가 있는 ‘핵심 인물’만 찾아내는 것과 같습니다.

  • 활용: 데이터 정합성을 검증할 때 매우 유용합니다. 예를 들어, ‘직원’ 테이블과 ‘급여 지급’ 테이블에 모두 존재하는 직원 목록을 찾아 누락된 직원이 없는지 확인할 수 있습니다.

  • 구문:

    SELECT column1, column2 FROM table_A
INTERSECT
SELECT column1, column2 FROM table_B;

라. EXCEPT 또는 MINUS (차집합)

EXCEPT는 첫 번째 SELECT 문의 결과에서 두 번째 SELECT 문의 결과에 포함된 행을 제외한 나머지 행을 반환합니다.

  • 개념: A에만 있고 B에는 없는 데이터를 보여줍니다. (A - B)

  • 비유: A 파티 초대 명단에 있는 사람들 중에서, B 파티에도 초대된 사람들을 제외하고 ‘A 파티에만 초대된 순수한 손님’ 목록을 만드는 것입니다.

  • 주의: EXCEPT는 순서가 매우 중요합니다. A EXCEPT B와 B EXCEPT A는 완전히 다른 결과를 반환합니다.

  • 명칭:

    • EXCEPT: PostgreSQL, SQL Server, SQLite 등에서 사용

    • MINUS: Oracle DB에서 사용 (기능은 동일)

  • 구문:

    -- table_A에는 있지만 table_B에는 없는 데이터
SELECT column1, column2 FROM table_A
EXCEPT
SELECT column1, column2 FROM table_B;

💡 데이터베이스 호환성: UNION과 UNION ALL은 거의 모든 관계형 데이터베이스에서 지원하는 표준 기능입니다. 하지만 INTERSECT와 EXCEPT는 MySQL에서는 기본적으로 지원하지 않습니다. MySQL에서는 JOIN이나 IN, EXISTS 절을 사용하여 비슷한 결과를 만들어내야 합니다.

3. 집합 연산자를 사용하기 위한 ‘게임의 법칙’

집합 연산자를 사용하려면 반드시 지켜야 할 몇 가지 중요한 규칙이 있습니다. 이 규칙을 어기면 쿼리는 오류를 반환합니다.

  1. 동일한 컬럼 개수: 모든 SELECT 문은 반드시 동일한 개수의 컬럼을 가져야 합니다.

    -- 올바른 예
SELECT name, age FROM A
UNION
SELECT person_name, person_age FROM B;
 
-- 잘못된 예 (컬럼 개수가 다름)
SELECT name, age FROM A
UNION
SELECT person_name, person_age, department FROM B; -- ERROR!

  2. 호환되는 데이터 타입: 각 SELECT 문의 컬럼들은 서로 같은 위치에 있는 것끼리 데이터 타입이 호환되어야 합니다. 예를 들어, 첫 번째 SELECT의 첫 번째 컬럼이 숫자(INTEGER)라면, 두 번째 SELECT의 첫 번째 컬럼도 숫자이거나 숫자로 자동 형 변환이 가능한 타입(예: VARCHAR 형태의 ‘123’)이어야 합니다.

-- 올바른 예 (VARCHAR와 VARCHAR)
SELECT employee_name FROM Active_Employees
UNION
SELECT retiree_name FROM Retired_Employees;
 
-- 잘못된 예 (INTEGER와 DATE는 호환되지 않음)
SELECT employee_id FROM Active_Employees -- INTEGER
UNION
SELECT hire_date FROM Retired_Employees; -- DATE, ERROR!
  1. ORDER BY와 LIMIT는 맨 마지막에: 결과 집합 전체를 정렬하거나 일부만 가져오고 싶다면, ORDER BY나 LIMIT(또는 FETCH FIRST) 절은 마지막 SELECT 문 뒤에 단 한 번만 사용해야 합니다. 이 절은 모든 집합 연산이 끝난 최종 결과에 적용됩니다.
SELECT name, age FROM A
UNION
SELECT person_name, person_age FROM B
ORDER BY 1 DESC; -- 최종 결과를 첫 번째 컬럼(name) 기준으로 내림차순 정렬
  1. 컬럼 이름: 최종 결과 집합의 컬럼 이름(헤더)은 첫 번째 SELECT 문의 컬럼 이름을 따릅니다.

4. JOIN vs. 집합 연산자: 결정적 차이

구분JOIN집합 연산자 (Set Operators)
결합 방향수평적 결합 (Horizontal)수직적 결합 (Vertical)
결합 대상테이블의 **컬럼(Column)**을 합침SELECT 결과의 **행(Row)**을 합침
결합 조건ON 절을 사용하여 특정 컬럼 값의 일치 여부컬럼의 개수와 순서, 데이터 타입의 호환성
결과 형태컬럼 개수가 늘어남 (두 테이블의 컬럼 조합)행 개수가 늘어남 (컬럼 개수는 그대로)
비유두 개의 다른 정보를 담은 표를 옆으로 붙여 하나의 큰 표로 만듦구조가 같은 두 개의 표를 아래로 이어 붙여 하나의 긴 표로 만듦

간단히 말해, 관련 있는 ‘다른 정보’를 가져와 옆에 붙이고 싶으면 JOIN을, 구조가 같은 ‘같은 종류의 정보’를 아래로 쌓고 싶으면 집합 연산자를 사용합니다.

5. 실전 시나리오로 배우는 집합 연산자 활용법

2024_sales 테이블과 2025_sales 테이블이 있다고 가정해 보겠습니다. 두 테이블은 product_id, sale_date, amount 컬럼을 가집니다.

  • 시나리오 1: 2년간의 모든 판매 기록 통합 조회

    중복 기록이 있을 수 없으므로 성능이 좋은 UNION ALL을 사용합니다.

    SELECT product_id, sale_date, amount FROM 2024_sales
UNION ALL
SELECT product_id, sale_date, amount FROM 2025_sales
ORDER BY sale_date;

  • 시나리오 2: 2년 연속으로 판매된 제품 목록 찾기

    2024년에도 팔리고, 2025년에도 팔린 제품을 찾습니다. INTERSECT가 완벽한 해결책입니다.

    SELECT product_id FROM 2024_sales
INTERSECT
SELECT product_id FROM 2025_sales;

  • 시나리오 3: 2024년에는 팔렸지만 2025년에는 팔리지 않은 제품 찾기

    2024년 판매 목록에서 2025년 판매 목록을 빼면 됩니다. EXCEPT를 사용합니다.

    SELECT product_id FROM 2024_sales
EXCEPT
SELECT product_id FROM 2025_sales;

  • 시나리오 4: 데이터 정합성 검증

    all_products 테이블과 product_sales_summary 테이블이 있다고 가정합시다. 이론적으로 모든 제품은 판매 요약 테이블에 존재해야 합니다. 만약 빠진 제품이 있다면?

    -- 모든 제품 목록에서, 판매 요약에 있는 제품 목록을 빼서 누락된 제품을 찾는다.
SELECT product_id FROM all_products
EXCEPT
SELECT product_id FROM product_sales_summary;

    이 쿼리의 결과가 비어있다면 데이터가 잘 관리되고 있다는 의미이고, 무언가 나온다면 데이터 누락이 발생했다는 신호입니다.

결론: 데이터 조합의 새로운 관점

JOIN이 데이터 관계의 ‘연결’에 초점을 맞춘다면, 집합 연산자는 데이터 ‘집합’ 자체의 관계를 다루는 강력하고 우아한 방법입니다. UNION, UNION ALL, INTERSECT, EXCEPT를 자유자재로 사용할 수 있게 되면, 복잡한 서브쿼리나 여러 단계의 JOIN으로 해결해야 했던 문제들을 훨씬 더 직관적이고 간결한 코드로 해결할 수 있습니다.

이제 여러분은 데이터를 수평으로만 보던 시각에서 벗어나, 수직으로 쌓고, 비교하고, 걸러내는 새로운 관점을 갖게 되었습니다. 이 핸드북을 시작으로 다양한 데이터셋에 집합 연산자를 적용하며 여러분의 데이터 분석 및 처리 능력을 한 단계 끌어올리시길 바랍니다.

레퍼런스(References)

집합 연산자

그래프 뷰

백링크