관계형 데이터베이스와 JOIN 연산

관계형 데이터베이스 환경에서 데이터는 정규화 과정을 거쳐 여러 테이블에 분산 저장되는 것이 일반적입니다. 이러한 분산된 데이터에서 의미 있는 인사이트를 도출하거나 비즈니스 로직을 처리하기 위해서는 테이블 간의 관계를 기반으로 데이터를 결합해야 합니다. 이때 사용되는 핵심 연산이 바로 JOIN입니다. 본 글에서는 SQL의 다양한 JOIN 방식과 서브쿼리의 개념을 살펴보고, 실제 백엔드 개발 및 데이터 분석 환경에서 쿼리 성능을 최적화하는 방법까지 심층적으로 다룹니다.

1. JOIN의 주요 유형

JOIN은 결합 조건과 결과 집합의 포함 범위에 따라 여러 가지 방식으로 분류됩니다.

1.1 내부 조인 (INNER JOIN)

내부 조인은 가장 기본적이고 빈번하게 사용되는 방식입니다. 두 테이블에서 조인 조건을 만족하는 행만을 결과로 반환합니다. 어느 한쪽 테이블에만 존재하는 데이터는 결과 집합에서 제외됩니다.

SELECT s.full_name, d.division_name
FROM staff s
INNER JOIN divisions d ON s.division_id = d.division_id;

• staff 테이블의 division_id와 divisions 테이블의 division_id가 일치하는 레코드만 조회됩니다.
• 소속 부서 정보가 누락된 직원은 결과에서 자동으로 제외됩니다.

1.2 왼쪽 조인 (LEFT JOIN)

왼쪽 조인(왼쪽 외부 조인)은 기준이 되는 왼쪽 테이블의 모든 행을 유지하면서, 오른쪽 테이블에서 조건에 부합하는 데이터를 추가합니다. 오른쪽 테이블에 매칭되는 데이터가 없다면 해당 열은 NULL로 채워집니다.

SELECT s.full_name, d.division_name
FROM staff s
LEFT JOIN divisions d ON s.division_id = d.division_id;

• 부서에 배정되지 않은 직원이 있더라도 모든 직원의 정보가 출력됩니다. 이때 매칭되지 않는 부서명 컬럼은 NULL로 표현됩니다.

1.3 오른쪽 조인 (RIGHT JOIN)

오른쪽 조인은 왼쪽 조인과 반대로, 오른쪽 테이블의 모든 행을 결과에 포함합니다. 왼쪽 테이블에 매칭되는 행이 없는 경우, 왼쪽 테이블의 컬럼 값은 NULL이 됩니다.

SELECT s.full_name, d.division_name
FROM staff s
RIGHT JOIN divisions d ON s.division_id = d.division_id;

• 직원이 한 명도 배정되지 않은 부서라 하더라도 모든 부서 정보가 결과에 포함됩니다. 해당 부서에 소속된 직원이 없으므로 직원 이름은 NULL로 표시됩니다.

1.4 완전 조인 (FULL OUTER JOIN)

완전 조인은 왼쪽과 오른쪽 테이블의 모든 행을 결과 집합에 포함시킵니다. 양쪽 테이블 모두 매칭 여부와 상관없이 데이터를 보존하며, 짝이 맞지 않는 부분은 NULL로 대체됩니다.

SELECT s.full_name, d.division_name
FROM staff s
FULL OUTER JOIN divisions d ON s.division_id = d.division_id;

• 부서 정보가 없는 직원과 직원이 없는 부서 모두 결과에 나타납니다. 서로 매칭되지 않는 필드는 NULL 값을 갖게 됩니다.

2. 다중 테이블 조인과 서브쿼리 활용

실제 서비스 환경에서는 두 개 이상의 테이블을 결합하거나, 특정 조건을 동적으로 계산하기 위해 서브쿼리를 활용하는 복잡한 쿼리가 빈번하게 요구됩니다.

2.1 다중 테이블 조인 (Multiple Joins)

세 개 이상의 테이블에 분산된 데이터를 한 번에 가져와야 할 때는 JOIN 절을 연속적으로 연결하여 사용합니다.

SELECT s.full_name, d.division_name, a.task_name
FROM staff s
INNER JOIN divisions d ON s.division_id = d.division_id
INNER JOIN assignments a ON s.staff_id = a.staff_id;

• staff, divisions, assignments 세 테이블을 순차적으로 결합하여, 직원의 이름과 소속 부서, 그리고 담당 중인 업무명을 하나의 결과 집합으로 추출합니다.

2.2 서브쿼리 (Subqueries)

서브쿼리는 메인 쿼리 내부에 중첩된 또 다른 쿼리를 의미합니다. 주로 WHERE, FROM, SELECT 절에서 조건을 제한하거나 임시 테이블을 생성하는 목적으로 사용됩니다.

SELECT full_name
FROM staff
WHERE staff_id IN (SELECT staff_id FROM assignments);

• 내부 서브쿼리가 업무가 할당된 직원의 ID 목록을 먼저 추출하고, 외부 쿼리가 이 목록을 기반으로 직원의 이름을 조회합니다.

2.3 상관 서브쿼리 (Correlated Subqueries)

상관 서브쿼리는 내부 쿼리가 외부 쿼리의 컬럼 값을 참조하는 형태입니다. 외부 쿼리의 각 행이 처리될 때마다 내부 서브쿼리가 반복적으로 실행되므로, 일반적인 서브쿼리보다 성능에 유의해야 합니다.

SELECT full_name, compensation
FROM staff s1
WHERE compensation > (
    SELECT AVG(compensation) 
    FROM staff s2 
    WHERE s2.division_id = s1.division_id
);

• 각 직원의 보상을 평가할 때, 해당 직원이 소속된 부서의 평균 보상을 동적으로 계산하여 이보다 높은 보상을 받는 인원만 필터링합니다.

2.4 서브쿼리와 JOIN의 성능 비교 및 최적화

동일한 결과를 도출하더라도 서브쿼리와 JOIN 중 어떤 방식을 선택하느냐에 따라 데이터베이스 엔진의 실행 계획과 성능이 크게 달라질 수 있습니다. 일반적으로 대용량 데이터 처리 시에는 JOIN을 사용하는 것이 데이터베이스 옵티마이저가 효율적인 실행 계획을 수립하는 데 유리합니다.

-- JOIN 활용
SELECT s.full_name, d.division_name
FROM staff s
INNER JOIN divisions d ON s.division_id = d.division_id
WHERE d.division_name = 'Research';

-- 서브쿼리 활용
SELECT full_name
FROM staff
WHERE division_id = (SELECT division_id FROM divisions WHERE division_name = 'Research');

반복적으로 실행되는 서브쿼리가 존재할 경우, 이를 JOIN으로 재작성하면 불필요한 테이블 스캔을 줄여 쿼리 응답 시간을 크게 단축할 수 있습니다.

-- 최적화 전 (서브쿼리 중복 사용)
SELECT full_name
FROM staff
WHERE division_id = (SELECT division_id FROM divisions WHERE division_name = 'Research')
  AND compensation > (SELECT AVG(compensation) FROM staff WHERE division_id = 3);

-- 최적화 후 (JOIN으로 변환)
SELECT s.full_name
FROM staff s
INNER JOIN divisions d ON s.division_id = d.division_id
WHERE d.division_name = 'Research'
  AND s.compensation > (SELECT AVG(compensation) FROM staff WHERE division_id = 3);