SQL의 집합 연산자: 종합 가이드

SQL에서 집합 연산이란?

SQL의 집합 연산은 두 개 이상의 SELECT 문 결과를 결합하거나 비교하는 기법입니다. 수학의 집합 연산처럼 동작하여, 쿼리가 반환한 행들의 합집합, 교집합, 차집합을 구할 수 있습니다. 이는 여러 소스나 관점에서 데이터를 분석할 때 없어서는 안 될 도구입니다.

핵심 집합 연산을 간단히 살펴보면 다음과 같습니다:

• UNION: 두 개 이상의 SELECT 문에서 반환된 모든 고유 행을 병합하며, 중복을 제거합니다.

• UNION ALL: 두 개 이상의 SELECT 문에서 반환된 모든 행을 중복 포함하여 병합합니다.

• INTERSECT: 두 SELECT 문 모두에 나타나는 행만 반환합니다.

• EXCEPT: 첫 번째 SELECT 문에만 있고 두 번째에는 없는 행을 반환합니다.

관계 대수 연산과의 비교

관계 대수는 데이터베이스 쿼리를 이해하기 위한 이론적 틀입니다. 특정 데이터베이스 시스템과 무관하게 수학적 원리에 기반한 투영, 선택, 조인 같은 추상 연산을 제공합니다. 말하자면 데이터베이스 상호작용을 가능하게 하는 “무대 뒤의” 논리입니다.

SQL의 집합 연산자는 이러한 개념을 데이터베이스 환경에서 실용적으로 구현한 것입니다. SQL 쿼리의 결과 집합에 대해 합집합, 교집합, 차집합 같은 연산을 직접 수행할 수 있게 해 줍니다.

관계 대수가 데이터베이스 연산의 공식적 토대를 제공한다면, SQL의 집합 연산자는 데이터 조작 작업을 위한 표준화되고 사용자 친화적인 인터페이스를 제공합니다.

집합 연산자와 관계 대수 연산의 관계를 이해하면 SQL의 이론적 바탕을 더 잘 파악할 수 있습니다. 이 연결 고리를 알면 내부적으로 무슨 일이 일어나는지 보다 명확한 정신 모델로 쿼리를 작성하는 데 도움이 됩니다.

SQL의 집합 연산자 종류

SQL의 기본 집합 연산자는 세 가지입니다:

• UNION

• INTERSECT

• EXCEPT (일부 방언에서는 MINUS)

이 연산자들은 수학적으로 합집합, 교집합, 차집합 개념에 대응합니다.

집합 연산자 사용 규칙

집합 연산자로 쿼리를 결합하기 전에 네 가지 요건을 충족해야 합니다. 하나라도 어기면 쿼리에 오류가 발생합니다.

• 동일한 열 개수: 각 SELECT는 같은 개수의 열을 반환해야 합니다. 한 쿼리가 3개, 다른 쿼리가 2개의 열을 반환하면 데이터베이스는 오류를 발생시킵니다.

• 호환 가능한 데이터 타입: 대응되는 열의 타입이 일치해야 합니다. 같은 위치에서 VARCHAR 이름 열과 INTEGER ID를 결합할 수 없습니다.

• 동일한 열 순서: SQL은 열 이름이 아니라 위치로 매핑합니다. 첫 번째 쿼리의 첫 번째 열은 두 번째 쿼리의 첫 번째 열에 매핑되며, 열 이름과 무관합니다.

• ORDER BY는 맨 끝에서만: ORDER BY는 결합된 쿼리의 맨 마지막에 한 번만 사용할 수 있습니다. 개별 SELECT 문 내부에서는 사용할 수 없습니다.

추가로, 출력 열 이름은 첫 번째 SELECT 문에서 가져옵니다. 이후 쿼리의 별칭은 무시되므로, 사용자 지정 열 이름이 필요하면 첫 번째 쿼리에만 별칭을 지정하세요.

SQL에서 UNION 연산자 사용 방법

UNION 연산자는 두 개 이상의 SELECT 쿼리 결과를 하나의 결과 집합으로 결합하며, 기본적으로 중복 행을 제거합니다.

예를 들어 employees와 contractors라는 두 테이블이 있고, 각 테이블에 contractors, department, salary와 같은 유사한 열이 있다고 가정해 봅시다. 학습을 위해 다음 예시 테이블을 사용하겠습니다.

employees:

contractors:

다음 명령으로 두 테이블의 결과를 결합할 수 있습니다.

-- Using UNION to combine all employees and contractors
SELECT name, department, salary FROM employees
UNION
SELECT name, department, salary FROM contractors;

이 쿼리는 employees와 contractors 테이블에서 name, department, salary 열을 선택해 하나의 결과 집합으로 결합합니다. UNION 연산자는 최종 결과 집합에서 중복 행을 자동으로 제거합니다.

두 테이블 모두에 있는 Carol은 결과에서 한 번만 표시됩니다. Carol의 두 행(서로 다른 급여)을 모두 유지하려면 UNION ALL을 사용합니다.

UNION vs UNION ALL

UNION 연산자는 NULL 값을 제거하지 않습니다. 한 결과 집합의 열에 NULL 값이 있고 다른 결과 집합의 해당 열에 NULL이 아닌 값이 있더라도, UNION이 생성하는 최종 결과에는 그 NULL 값이 유지됩니다.

결과 집합에 있는 NULL 값을 포함하고, UNION에 의해 제거되지 않게 하려면 UNION ALL을 사용하면 됩니다. 이 연산자는 중복 여부나 NULL 포함 여부와 상관없이 여러 SELECT 쿼리의 모든 행을 결합합니다.

SQL에서 INTERSECT 연산자 사용 방법

INTERSECT 연산자는 두 결과 집합 모두에 나타나는 행만 반환합니다. 즉, 두 그룹에 모두 속한 사람을 찾는 것과 같습니다.

앞서 본 테이블에 INTERSECT를 적용해 보겠습니다. 예시를 단순화하기 위해 name과 department 열만 조회합니다.

-- Using INTERSECT to find common employees
SELECT name, department FROM employees
INTERSECT
SELECT name, department FROM contractors;

이 쿼리는 employees와 contractors 테이블에서 name과 department 열을 선택하고, 선택된 모든 열을 기준으로 두 테이블 모두에 존재하는 행만 반환합니다.

INTERSECT 연산자는 표준 비교 규칙에 따라 NULL 값을 처리하며, 대응 열을 비교할 때 NULL 값을 동일한 것으로 간주합니다. 또한 빈 결과 집합을 다룰 때는 결과가 빈 집합이 됩니다.

즉, 한 결과 집합에 NULL 값이 있고 다른 결과 집합의 해당 열에 NULL이 아닌 값이 있으면 두 행은 동일하지 않다고 판단되어 교집합 결과에 포함되지 않습니다.

또한 INTERSECT에 제공된 결과 집합 중 하나가 비어 있으면 전체 결과도 비어 있습니다. 빈 집합과 다른 어떤 것 사이에도 공통 행은 없습니다.

SQL에서 EXCEPT 연산자 사용 방법

EXCEPT 연산자는 첫 번째 결과 집합에는 있고 두 번째에는 없는 행을 반환합니다.

Oracle Database에서는 EXCEPT를 MINUS로 표기하며, 그 외 주요 방언(PostgreSQL, SQL Server, MySQL 8.0.31+, SQLite)은 EXCEPT를 사용합니다.

예를 들어, 다음 쿼리를 실행한다고 가정해 봅시다.

-- Using EXCEPT to find employees who are not contractors
SELECT name, department, salary FROM employees
EXCEPT
SELECT name, department, salary FROM contractors;

employees 테이블에서 name, department, salary 열을 선택하고, contractors 테이블에 존재하지 않는 행만 반환합니다.

EXCEPT 연산자도 NULL 값 처리에 표준 비교 규칙을 따릅니다. 첫 번째 결과 집합이 비어 있으면 결과는 빈 집합이 되고, 두 번째 결과 집합이 비어 있으면 첫 번째 결과 집합의 모든 행이 포함됩니다.

SQL 집합 연산자: 성능과 최적화

SQL에서 집합 연산자가 쿼리 성능에 미치는 영향은 관련 데이터셋의 크기, 쿼리의 복잡성, 사용 중인 데이터베이스 관리 시스템(DBMS) 등의 요인에 따라 달라질 수 있습니다.

핵심 요인과 최적화 전략을 나눠서 살펴보겠습니다.

데이터 볼륨과 쿼리 복잡도

대용량 데이터를 다룰 때 집합 연산은 결합, 교집합, 비교해야 하는 결과 집합의 크기가 커지므로 해당 연산을 수행하는 데 필요한 처리 시간이 늘어나 성능에 큰 영향을 줄 수 있습니다.

여러 서브쿼리, 조인, 집합 연산자를 포함한 복잡한 쿼리는 추가 오버헤드를 유발해 성능에 영향을 미칠 수 있습니다. 연쇄 연산이나 중첩 집합 연산은 성능 저하를 더욱 악화시킬 수 있습니다.

인덱싱과 최적화 기법

집합 연산에 관련된 열에 적절히 인덱스를 생성하면 쿼리 성능을 크게 개선할 수 있습니다. 인덱스는 데이터베이스 엔진이 관련 행을 더 빠르게 찾고 가져오도록 도와 전체 테이블 스캔을 줄이고 실행 시간을 단축합니다.

집합 연산을 포함하는 쿼리의 성능을 개선하기 위해 DBA와 개발자는 쿼리 리라이팅, 실행 계획 분석, 스키마 최적화 같은 기법을 적용할 수 있습니다. 또한 쿼리 캐싱과 머티리얼라이즈드 뷰를 사용해 복잡한 쿼리의 결과를 미리 계산·저장함으로써 집합 연산의 계산 부담을 줄일 수 있습니다.

데이터베이스 엔진과 하드웨어 자원

집합 연산의 성능은 기본 데이터베이스 엔진과 그 최적화 능력에 따라 달라질 수 있습니다. DBMS마다 집합 연산을 처리하는 최적화 전략과 알고리즘이 달라 성능 차이가 발생할 수 있습니다.

CPU, 메모리, 디스크 I/O 같은 하드웨어 자원의 가용성도 집합 연산이 포함된 쿼리 성능에 영향을 줍니다. 충분한 하드웨어 자원은 병목을 완화하고 효율적인 쿼리 실행을 보장합니다.

실무에서의 SQL 집합 연산자

집합 연산자는 이론적 도구에 그치지 않고, 비즈니스 의사결정에 큰 영향을 미치는 실무 활용이 가능합니다. 한 회사가 타깃 마케팅 캠페인을 위해 고객 기반을 세그먼트하는 과정을 단순화해 살펴보겠습니다.

시나리오

온라인과 오프라인 매장을 모두 운영하는 회사가 있다고 가정해 봅시다. 두 개의 별도 데이터셋이 있습니다:

1. 온라인 구매: 온라인 쇼핑 고객의 고객 ID, 구매 이력, 인구통계, 지역
2. 매장 거래: 오프라인 쇼핑 고객에 대한 유사 정보

집합 연산자 활용

전체 고객을 한눈에 파악하기 위해 먼저 UNION으로 두 데이터셋을 하나의 테이블로 결합하고 중복을 제거합니다. 이렇게 하면 전체 고객 기반에 대한 통합된 뷰를 얻을 수 있습니다.

다음으로 INTERSECT를 사용해 온라인과 오프라인에서 모두 구매한 고객을 식별할 수 있습니다. 이 세그먼트는 여러 채널에서 브랜드와 활발히 상호작용하는 만큼 특히 가치가 높습니다.

채널 간 프로모션 기회를 찾기 위해 EXCEPT를 사용할 수 있습니다. 예를 들어 SELECT * FROM online_purchases EXCEPT SELECT * FROM in_store_transactions는 온라인에서만 구매하고 매장에서는 구매하지 않은 고객을 찾습니다. 이후 이들에게 매장 방문을 유도하는 프로모션을 진행할 수 있습니다.

세분화 그 이후

이렇게 세그먼트를 식별한 뒤에는 인구통계나 구매 이력 같은 추가 요인으로 더 정교하게 다듬을 수 있습니다. 고객에 대한 세밀한 이해를 바탕으로 보다 정밀하게 마케팅 캠페인을 설계할 수 있습니다.

집합 연산자 vs. JOIN

집합 연산자와 SQL JOIN은 모두 여러 쿼리의 데이터를 결합하지만 동작 방식이 다릅니다. 핵심은 행을 결합할지, 열을 결합할지입니다.

집합 연산자의 제한 사항과 고려 사항

SQL에서 집합 연산자를 사용할 때는 쿼리 성능, 결과 정확성, 전반적 사용성을 좌우하는 여러 제한과 요소를 고려해야 합니다.

데이터 타입 호환성과 NULL 값

결과 집합의 대응 열은 호환 가능한 데이터 타입이어야 합니다. 쿼리를 결합하기 전에 대응 열의 데이터 타입이 호환되는지 확인하세요. 타입 불일치는 눈치채기 쉬운 오류 원인입니다.

집합 연산자는 DBMS와 특정 연산자에 따라 NULL 값을 다르게 취급할 수 있습니다. 오류를 피하려면 NULL 값의 처리 방식을 이해해야 합니다.

성능 영향과 중복 행

특히 대규모나 복잡한 데이터셋을 다룰 때 집합 연산은 쿼리 성능에 큰 영향을 줄 수 있습니다. 인덱싱, 쿼리 최적화, 하드웨어 자원 같은 요소가 성능에 영향을 미칩니다. 성능 병목을 완화하려면 최적화 기법과 튜닝 전략이 필수입니다.

기본적으로 집합 연산자는 결과 집합에서 중복 행을 제거합니다. 그러나 경우에 따라 중복 행을 유지해야 할 수 있습니다. 중복 처리에 대한 집합 연산자의 동작을 이해하고, 필요한 경우 적절한 기법을 사용하세요.

결과 정렬과 메모리 제약

집합 연산자는 최종 출력의 결과 순서를 보장하지 않습니다. 결합된 결과를 정렬하려면 전체 쿼리의 맨 끝, 마지막 SELECT 문 뒤에 ORDER BY 절을 한 번만 추가하세요.

대용량 데이터셋을 다룰 때 집합 연산은 상당한 메모리와 자원을 소비할 수 있습니다. 성능 저하나 시스템 불안정을 피하려면 메모리 제약과 자원 한계를 고려해야 합니다.

복잡성, 유지보수성, 교차 DBMS 호환성

여러 집합 연산자, 서브쿼리, 조인이 얽힌 복잡한 쿼리는 이해·유지보수·디버깅이 어렵습니다. 가독성과 유지보수성을 높이려면 쿼리를 간결하고, 잘 문서화하며, 모듈화해야 합니다.

집합 연산자는 DBMS마다 문법과 동작에 차이가 있을 수 있습니다. 크로스 플랫폼 호환성을 염두에 두고 SQL을 작성하려면 이러한 차이를 숙지하는 것이 중요합니다.

마무리

집합 연산자는 어떤 행이 겹치는지 미리 알지 못한 채 결과 집합을 결합하거나 비교하는 특정 문제를 해결합니다.

즉, 결과 집합을 결합, 비교, 차감하는 세 연산자 UNION, INTERSECT, EXCEPT를 포괄합니다.

더 배우고 싶다면 Joining Data in SQL 과정을 확인하세요.