Computer >> 컴퓨터 >  >> 프로그래밍 >> MySQL

MySQL에서 CROSS JOIN을 제대로 활용하는 방법: 개념부터 실전 예제까지

MySQL의 CROSS JOIN은 조인되는 두 테이블의 행들에 대한 카티션 곱(Cartesian Product)을 반환합니다. 즉, 첫 번째 테이블의 각 행이 두 번째 테이블의 모든 행과 한 번씩 결합된 결과를 만들어냅니다.

이번 글에서는 실제 테이블을 생성하고 데이터를 삽입한 뒤, CROSS JOIN을 활용해 두 컬럼 값의 모든 조합 중 아직 존재하지 않는 조합만 추출하는 실전 예제를 살펴보겠습니다.

1. 예제 테이블 생성하기

먼저 사용자 이름과 관계자 이름을 저장할 테이블을 생성합니다. 다음 쿼리를 실행하세요.

mysql> create table PairDemo
-> (
-> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> UserName varchar(20),
-> UserRelationshipName varchar(20)
-> );
Query OK, 0 rows affected (0.56 sec)

위 테이블은 세 개의 컬럼으로 구성됩니다.

  • UserId: 자동 증가하는 기본 키
  • UserName: 사용자 이름
  • UserRelationshipName: 해당 사용자와 연결된 관계자 이름

2. 샘플 데이터 삽입하기

INSERT 명령어를 사용해 몇 개의 레코드를 추가합니다.

mysql> insert into PairDemo(UserName,UserRelationshipName) values('John','James');
Query OK, 1 row affected (0.15 sec)
mysql> insert into PairDemo(UserName,UserRelationshipName) values('Carol','James');
Query OK, 1 row affected (0.20 sec)
mysql> insert into PairDemo(UserName,UserRelationshipName) values('Carol','David');
Query OK, 1 row affected (0.25 sec)
mysql> insert into PairDemo(UserName,UserRelationshipName) values('Sam','David');
Query OK, 1 row affected (0.19 sec)

3. 전체 데이터 확인하기

SELECT 문으로 테이블에 저장된 모든 레코드를 조회합니다.

mysql> select *from PairDemo;

출력 결과

+--------+----------+----------------------+
| UserId | UserName | UserRelationshipName |
+--------+----------+----------------------+|      2 |    Carol |                James |
|      3 |    Carol |                David |
|      4 |      Sam |                David |
+--------+----------+----------------------+
4 rows in set (0.00 sec)

현재 데이터에는 John–James, Carol–James, Carol–David, Sam–David 네 가지 조합이 존재합니다.

4. CROSS JOIN 적용하기

이제 CROSS JOIN을 활용하여 UserName과 UserRelationshipName의 모든 가능한 조합을 만든 후, 그중 기존 테이블에 존재하지 않는 조합만 추출하는 쿼리를 작성해 보겠습니다.

mysql> SELECT U.UserName, UR.UserRelationshipName
-> FROM (
-> (SELECT DISTINCT UserName from PairDemo) U
-> CROSS JOIN
-> (SELECT DISTINCT UserRelationshipName from PairDemo) UR
-> ) WHERE NOT EXISTS (
-> SELECT * FROM PairDemo tbl
-> WHERE tbl.UserName = U.UserName
-> AND tbl.UserRelationshipName = UR.UserRelationshipName
-> ) ;

쿼리 동작 원리

  1. DISTINCT 서브쿼리: UserName과 UserRelationshipName에서 각각 중복을 제거한 고유한 값 목록을 추출합니다.
  2. CROSS JOIN: 두 목록의 모든 조합(카티션 곱)을 생성합니다. 이 예제에서는 John, Carol, Sam × James, David = 총 6가지 조합이 만들어집니다.
  3. NOT EXISTS: 생성된 조합 중 원본 테이블에 이미 존재하는 조합을 걸러내고, 없는 조합만 반환합니다.

실행 결과

+----------+----------------------+
| UserName | UserRelationshipName |
+----------+----------------------+
|      Sam |                James |
|     John |                David |
+----------+----------------------+
2 rows in set (0.03 sec)

결과 분석

원본 테이블에 존재하지 않던 두 가지 조합이 출력되었습니다.

  • Sam – James: Sam의 관계자로 James가 등록되어 있지 않음
  • John – David: John의 관계자로 David가 등록되어 있지 않음

이처럼 CROSS JOIN은 두 집합의 모든 조합을 빠짐없이 생성할 수 있기 때문에, 누락된 데이터 조합을 찾거나 매핑 테이블의 빈칸을 확인하는 작업에 매우 유용하게 활용됩니다.

마무리 정리

CROSS JOIN은 단독으로 사용하면 결과 행 수가 급격히 늘어날 수 있으므로 주의가 필요하지만, DISTINCT와 NOT EXISTS 같은 절과 함께 사용하면 '누락된 조합 찾기'와 같은 실무적인 문제를 효과적으로 해결할 수 있습니다. MySQL에서 CROSS JOIN의 동작 원리를 정확히 이해하고 활용한다면 데이터 분석과 검증 작업의 생산성을 크게 높일 수 있습니다.