MySQL에서 다른 열에 지정된 공통 값을 가진 튜플 추출하는 방법

MySQL에서 서로 다른 열에 지정된 공통 값을 가진 튜플(행)을 추출하려면 셀프 조인(Self Join)과 DISTINCT 키워드를 함께 활용하는 것이 가장 효과적입니다. 하나의 테이블에 별칭(alias)을 두 개 부여하여 마치 두 개의 테이블인 것처럼 조인한 뒤, WHERE 절에서 각각 원하는 조건 값을 지정하면 됩니다.

기본 문법

지정된 공통 값으로 튜플을 추출할 때 사용하는 기본 문법은 다음과 같습니다.

SELECT DISTINCT
aliasName.yourColumnName1,
aliasName.yourColumnName2,
aliasName1.yourColumnName1,
aliasName1.yourColumnName2
FROM yourTableName aliasName
INNER JOIN yourTableName aliasName1
ON aliasName.yourColumnName1 = aliasName1.yourColumnName1
WHERE aliasName.yourColumnName2 = 'value1'
AND aliasName1.yourColumnName2 = 'value2';

같은 테이블을 aliasName과 aliasName1이라는 서로 다른 별칭으로 조인하고, ON 절에서 공통 열(예: Id)을 기준으로 연결한 후, WHERE 절에서 각 별칭이 가리키는 행의 특정 열 값이 각각 'value1'과 'value2'가 되도록 조건을 걸어주는 방식입니다.

예제 테이블 생성

문법을 실제로 이해하기 위해 예제 테이블을 먼저 만들어 보겠습니다. 아래 쿼리로 extractTuples 테이블을 생성합니다.

mysql> create table extractTuples
    -> (
    -> Id int,
    -> Name varchar(20),
    -> Comments text
    -> );
Query OK, 0 rows affected (0.77 sec)

샘플 데이터 삽입

INSERT 명령을 사용해 테이블에 몇 개의 레코드를 삽입합니다.

mysql> insert into extractTuples values(1,'John','hi');
Query OK, 1 row affected (0.20 sec)
mysql> insert into extractTuples values(2,'Carol','hello');
Query OK, 1 row affected (0.17 sec)
mysql> insert into extractTuples values(3,'John','Amazing');
Query OK, 1 row affected (0.13 sec)
mysql> insert into extractTuples values(1,'Carol','Good');
Query OK, 1 row affected (0.14 sec)

전체 레코드 조회

SELECT문을 사용해 테이블의 모든 레코드를 확인해 보겠습니다.

mysql> select *from extractTuples;

출력 결과

+------+-------+----------+
| Id   | Name  | Comments |
+------+-------+----------+
|    1 | John  | hi       |
|    2 | Carol | hello    |
|    3 | John  | Amazing  |
|    1 | Carol | Good     |
+------+-------+----------+
4 rows in set (0.00 sec)

테이블에는 John과 Carol의 레코드가 섞여 있으며, Id 값이 중복되어 나타나는 것을 볼 수 있습니다.

공통 값으로 튜플 추출하기

이제 지정된 공통 값을 기준으로 튜플을 추출하는 실제 쿼리입니다. 여기서는 같은 Id를 공유하면서 한 행은 Name이 'John', 다른 행은 Name이 'Carol'인 경우를 찾습니다.

mysql> SELECT DISTINCT tbl.Id,tbl.Name,tbl1.Id,tbl1.Name
    -> FROM extractTuples tbl
    -> INNER JOIN extractTuples tbl1
    -> ON tbl.Id = tbl1.Id
    -> WHERE tbl.Name = 'John' AND tbl1.Name = 'Carol';

출력 결과

+------+------+------+-------+
| Id   | Name | Id   | Name  |
+------+------+------+-------+
|    1 | John |    1 | Carol |
+------+------+------+-------+
1 row in set (0.00 sec)

결과 해석

위 결과를 보면 Id가 1인 행 중에서 Name이 'John'인 튜플과 Name이 'Carol'인 튜플이 함께 조회되었습니다. 즉, 동일한 Id라는 공통 값을 가지면서 서로 다른 Name 조건을 만족하는 행들이 성공적으로 추출된 것입니다.

이처럼 셀프 조인은 하나의 테이블 안에서 특정 열의 값이 서로 연관된 행들을 찾아낼 때 매우 유용합니다. 예를 들어 같은 그룹에 속한 사용자 비교, 상호 참조 관계 분석 등 다양한 실무 시나리오에 응용할 수 있습니다.