[조인] 조인을 상관서브쿼리로 표현

조인(JOIN)과 동일한 논리적 결과(교집합 또는 특정 조건을 만족하는 연관 데이터 추출)를 얻기 위해 IN이나 EXISTS 외에 사용할 수 있는 SQL 구문들을 설명해 드리겠습니다.

 

이해를 돕기 위해 실무에서 자주 다루는 릴레이션을 예시로 사용하겠습니다.

[예시 릴레이션 정의]

• PROJECT (프로젝트 릴레이션):
  • pid (기본키): 프로젝트 ID
  • pname: 프로젝트명
  • status: 상태 (예: 'Active', 'Closed')
• TASK (작업 릴레이션):
  • tid (기본키): 작업 ID
  • pid (외래키): 프로젝트 ID (PROJECT 참조)
  • assignee: 담당자 이름

목표 질의: "현재 'Active'(진행 중) 상태인 프로젝트에 할당된 작업 담당자(assignee)의 이름을 중복 없이 조회하라."

 

비교를 위한 표준 명시적 조인(Explicit Join) 구문은 다음과 같습니다.

SELECT DISTINCT t.assignee
FROM TASK t
INNER JOIN PROJECT p ON t.pid = p.pid
WHERE p.status = 'Active';

---

1. 비명시적 조인 (Implicit Join / Theta Join)

가장 전통적인 방식입니다. FROM 절에 조인할 테이블을 콤마(,)로 나열하여 카테시안 곱(Cartesian Product)을 생성한 뒤, WHERE 절에서 두 테이블의 연결 고리(외래키=기본키)를 조건으로 주어 동등 조인(Equi-Join)과 같은 결과를 만듭니다.

• SQL 구문:

  SELECT DISTINCT t.assignee
  FROM TASK t, PROJECT p
  WHERE t.pid = p.pid
    AND p.status = 'Active';
  

• 동작 원리: 두 테이블의 모든 가능한 조합을 만든 후, t.pid와 p.pid가 같은 행만 필터링합니다. 논리적으로 INNER JOIN과 100% 동일한 실행 계획을 가집니다.

 

2. IN 연산자를 활용한 중첩 질의 (Nested Query)

IN 연산자는 하위 질의(Subquery)의 결과 집합에 메인 질의의 특정 값이 포함되어 있는지 확인합니다. 일반적으로 내부 질의가 먼저 단독으로 실행되고, 그 결과를 외부 질의가 사용하는 비상관 서브쿼리(Non-correlated Subquery) 형태로 작성됩니다.

SELECT DISTINCT assignee
FROM TASK
WHERE pid IN (
    SELECT pid
    FROM PROJECT
    WHERE status = 'Active'
);

• 동작 원리:
  1. 내부 질의가 먼저 실행되어 PROJECT 테이블에서 status가 'Active'인 모든 pid 목록을 추출하여 집합을 구성합니다. (예: ('P01', 'P03'))
  2. 외부 질의는 TASK 테이블을 순차적으로 스캔하면서, 각 행의 pid가 앞서 만들어진 집합 안에 존재하는지(IN) 검사합니다.
  3. 조건이 일치하는 행의 assignee 값을 추출하고, DISTINCT를 통해 최종 결과에서 중복을 제거합니다.

 

3. EXISTS 연산자를 활용한 상관 서브쿼리 (Correlated Subquery)

EXISTS 연산자는 하위 질의를 만족하는 튜플이 존재하는지(한 건이라도 반환되는지) 여부만 판단하여 참(TRUE) 또는 거짓(FALSE)을 반환합니다. 외부 질의의 값을 내부 질의의 조건절에서 참조하는 상관 서브쿼리 형태로 주로 작성됩니다.

SELECT DISTINCT assignee
FROM TASK t
WHERE EXISTS (
    SELECT 1
    FROM PROJECT p
    WHERE p.pid = t.pid
      AND p.status = 'Active'
);

• 동작 원리:
  1. 외부 질의의 TASK 테이블에서 첫 번째 행을 읽고, 해당 행의 t.pid 값을 내부 질의로 전달합니다.
  2. 내부 질의는 전달받은 t.pid와 일치하면서 status가 'Active'인 행이 PROJECT 테이블에 존재하는지 검색합니다.
  3. 조건을 만족하는 레코드를 하나라도 발견하면 즉시 검색을 멈추고 참(TRUE)을 반환하며, 이때 외부 질의는 해당 assignee를 결과 집합에 포함시킵니다.
  4. TASK 테이블의 모든 행에 대해 위 과정을 반복한 후, 최종적으로 DISTINCT로 중복을 제거합니다.

 

4. ANY / SOME 연산자 활용

ANY와 SOME은 SQL에서 완전히 동일한 기능을 수행하는 다중행 비교 연산자입니다. = ANY 연산은 본질적으로 IN 연산자와 완벽하게 동일한 논리로 동작하지만, 키워드 자체가 다릅니다. 서브쿼리가 반환하는 값 집합 중 하나라도 일치하면 참(True)을 반환합니다.

• SQL 구문:

  SELECT DISTINCT assignee
  FROM TASK
  WHERE pid = ANY (
      SELECT pid
      FROM PROJECT
      WHERE status = 'Active'
  );
  -- '= SOME'을 사용해도 결과는 동일합니다.
  

• 동작 원리: 먼저 서브쿼리에서 상태가 'Active'인 프로젝트의 pid 목록을 추출합니다. 메인 질의의 TASK 테이블을 스캔하면서 해당 작업의 pid가 추출된 목록 중 하나와 일치(= ANY)하는지 검사하여 assignee를 출력합니다.

 

5. 집합 연산자 (INTERSECT)

INTERSECT는 두 릴레이션의 교집합을 구하는 관계대수 연산($\cap$)의 직접적인 구현체입니다. 단, 집합 연산자는 참여하는 두 질의의 차수(속성의 개수)와 도메인(데이터 타입)이 일치해야 한다는 제약이 있습니다.

따라서 담당자 이름(assignee)을 직접 추출하기보다는, "Active 상태인 프로젝트의 ID"와 "작업이 할당된 프로젝트의 ID"의 교집합을 찾는 질의로 조인과 유사한 목적을 달성할 때 사용됩니다.

• SQL 구문 (진행 중이면서 작업이 존재하는 프로젝트 ID 조회):

  SELECT pid 
  FROM PROJECT 
  WHERE status = 'Active'
  INTERSECT
  SELECT pid 
  FROM TASK;
  

• 동작 원리: 위쪽 질의의 결과 집합과 아래쪽 질의의 결과 집합을 비교하여, 양쪽 모두에 존재하는 pid만 반환합니다. INTERSECT 자체에 내장된 집합의 특성상 별도의 DISTINCT가 없어도 자동으로 중복이 제거된 결과를 도출합니다.