PostgreSQL 조인과 서브쿼리: 데이터베이스 쿼리의 핵심을 파헤치다

PostgreSQL을 사용해 데이터를 다루는 개발자나 데이터 분석가라면, SQL 쿼리의 두 기둥인 조인(JOIN) 과 서브쿼리(Subquery) 를 깊이 이해하는 것이 필수입니다. 이 두 요소는 복잡한 데이터베이스 환경에서 필요한 정보를 효율적으로 추출하고 조합하는 데 핵심적인 역할을 합니다. 단순한 테이블 조회를 넘어, 여러 테이블 간의 관계를 연결하거나 동적 조건을 적용하는 데 강력한 도구로 활용됩니다.

이 포스트에서는 조인과 서브쿼리의 기본 개념부터 PostgreSQL에서의 실전 활용법까지 단계적으로 탐구하겠습니다. 초보자부터 중급자까지, 이 내용을 통해 쿼리 작성 능력을 한 단계 업그레이드하세요. 예시 코드는 실제 PostgreSQL 환경에서 바로 테스트할 수 있도록 구성했습니다.

728x90

조인 이해: 여러 테이블을 하나로 연결하기

현대 데이터베이스는 중복을 최소화하고 데이터 무결성을 보장하기 위해 정규화(Normalization) 를 통해 여러 테이블로 데이터를 분산합니다. 하지만 비즈니스 요구사항에 따라 이러한 테이블을 통합해 분석해야 할 때가 많습니다. 이때 조인(JOIN) 이 등장합니다. 조인은 공통 컬럼(예: ID)을 기준으로 테이블의 행을 연결해 새로운 결과 집합을 생성하는 연산입니다.

PostgreSQL은 ANSI SQL 표준을 준수하며, 다양한 조인 유형을 지원합니다. 아래에서 주요 유형을 하나씩 살펴보고, 고객(customers)과 주문(orders) 테이블을 예시로 사용하겠습니다. (가정: customers 테이블에 id, name 컬럼; orders 테이블에 order_id, customer_id 컬럼이 있습니다.)

1. INNER JOIN (내부 조인)

INNER JOIN은 두 테이블에서 공통으로 일치하는 행만 반환합니다. 불일치 행은 결과에서 제외되어, "교집합" 같은 결과를 얻을 수 있습니다. 이는 가장 기본적이고 자주 사용되는 조인 유형으로, 성능이 우수합니다.

예시 시나리오: 주문을 실제로 한 고객의 이름과 주문 ID를 함께 조회. 주문이 없는 고객은 결과에서 사라집니다.

SELECT customers.name, orders.order_id
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;

결과 예시 (가상의 데이터 기준):
| name | order_id |
|----------|----------|
| Alice | 101 |
| Bob | 102 |

2. LEFT JOIN (또는 LEFT OUTER JOIN)

LEFT JOIN은 왼쪽 테이블의 모든 행을 포함하며, 오른쪽 테이블에서 일치하는 행이 없으면 해당 컬럼을 NULL로 채웁니다. "왼쪽 테이블 중심"으로 데이터를 보완할 때 유용합니다.

예시 시나리오: 모든 고객을 나열하되, 주문이 있으면 주문 ID를 추가. 주문 없는 고객은 order_id가 NULL로 표시됩니다.

SELECT customers.name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;

결과 예시:
| name | order_id |
|----------|----------|
| Alice | 101 |
| Bob | 102 |
| Charlie | NULL |

3. RIGHT JOIN (또는 RIGHT OUTER JOIN)

RIGHT JOIN은 LEFT JOIN의 반대 버전으로, 오른쪽 테이블의 모든 행을 포함하며 왼쪽 테이블 불일치 시 NULL을 사용합니다. LEFT JOIN보다 덜 사용되지만, 특정 시나리오에서 유연성을 제공합니다.

예시 시나리오: 모든 주문을 나열하되, 고객 정보가 있으면 이름 추가. 고객 정보 없는 주문(예: 익명 주문)은 name이 NULL입니다.

SELECT customers.name, orders.order_id
FROM customers
RIGHT JOIN orders ON customers.id = orders.customer_id;

결과 예시:
| name | order_id |
|----------|----------|
| Alice | 101 |
| NULL | 103 |
| Bob | 102 |

4. FULL OUTER JOIN (완전 외부 조인)

FULL OUTER JOIN은 LEFT와 RIGHT JOIN을 합친 형태로, 양쪽 테이블의 모든 행을 포함합니다. 불일치 행은 적절히 NULL로 채워집니다. 데이터 불일치 분석에 적합합니다.

예시 시나리오: 고객과 주문의 전체 매칭 상태를 확인. 한쪽에만 존재하는 데이터도 모두 포함합니다.

SELECT customers.name, orders.order_id
FROM customers
FULL OUTER JOIN orders ON customers.id = orders.customer_id;

결과 예시:
| name | order_id |
|----------|----------|
| Alice | 101 |
| Bob | 102 |
| Charlie | NULL |
| NULL | 103 |

5. CROSS JOIN (카테시안 조인)

CROSS JOIN은 조건 없이 두 테이블의 모든 행을 조합합니다. 결과 행 수는 왼쪽 행 수 × 오른쪽 행 수로 폭발적으로 증가할 수 있으니, 조심스럽게 사용하세요. (예: 테스트 데이터 생성 시 유용)

예시 시나리오: 제품(products)과 색상(colors) 테이블로 모든 가능한 제품-색상 조합 생성.

SELECT products.product_name, colors.color_name
FROM products
CROSS JOIN colors;

결과 예시 (products: Laptop, Mouse; colors: Red, Blue):
| product_name | color_name |
|--------------|------------|
| Laptop | Red |
| Laptop | Blue |
| Mouse | Red |
| Mouse | Blue |

팁: 조인 성능을 최적화하려면 인덱스를 공통 컬럼에 적용하세요. PostgreSQL의 EXPLAIN 명령어로 쿼리 계획을 분석하는 습관을 들이세요.

서브쿼리 이해: 쿼리 속의 쿼리

서브쿼리(Subquery)는 메인 쿼리 안에 중첩된 또 다른 쿼리로, 동적 조건이나 임시 계산 결과를 제공합니다. 별도의 뷰나 임시 테이블 없이 복잡한 로직을 구현할 수 있어 코드 가독성을 높입니다. PostgreSQL에서 서브쿼리는 WHERE, FROM, SELECT, HAVING 절 등에서 자유롭게 사용 가능합니다.

서브쿼리는 반환 결과에 따라 세 가지 유형으로 나뉩니다. 직원(employees) 테이블(컬럼: name, salary, department_id)을 예시로 들어보겠습니다.

1. 단일 행 서브쿼리 (Single-Row Subquery)

하나의 값(스칼라)만 반환하며, =, >, < 등의 비교 연산자와 함께 사용합니다. 간단한 필터링에 이상적입니다.

예시 시나리오: 전체 평균 급여보다 높은 급여를 받는 직원 조회.

SELECT name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

결과 예시:
| name |
|--------|
| Alice |
| Bob |

2. 다중 행 서브쿼리 (Multi-Row Subquery)

여러 행을 반환하며, IN, ANY, ALL 연산자와 결합합니다. 목록 기반 필터링에 강력합니다.

예시 시나리오: 직원 수가 10명 이상인 부서의 부서 이름 조회.

SELECT department_name
FROM departments
WHERE id IN (
    SELECT department_id
    FROM employees
    GROUP BY department_id
    HAVING COUNT(*) > 10
);

결과 예시:
| department_name |
|-----------------|
| Engineering |
| Sales |

3. 상관 서브쿼리 (Correlated Subquery)

외부 쿼리의 값에 의존해 각 행마다 서브쿼리가 재실행됩니다. (성능 주의: 대규모 데이터에서 느릴 수 있음) EXISTS나 동적 계산에 유용합니다.

예시 시나리오: 자신의 부서 평균 급여보다 높은 직원 조회. (e1은 외부, e2는 내부 테이블 별칭)

SELECT name
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e1.department_id = e2.department_id
);

결과 예시:
| name |
|--------|
| Alice |

팁: 상관 서브쿼리가 느리면 WINDOW 함수나 CTE(Common Table Expression)로 대체하세요. 예: WITH dept_avg AS (SELECT department_id, AVG(salary) AS avg_sal FROM employees GROUP BY department_id) SELECT ...

실제 고려 사항 및 결론

조인과 서브쿼리는 PostgreSQL의 강력한 기능이지만, 남용 시 성능 저하를 초래할 수 있습니다. 고려 사항:

• 인덱싱: 조인 키와 서브쿼리 조건 컬럼에 인덱스 생성.
• CTE 활용: 복잡한 서브쿼리를 CTE로 분리해 가독성 ↑ (WITH 절 사용).
• 성능 튜닝: pg_stat_statements 확장으로 쿼리 모니터링.
• 보안: 서브쿼리에서 사용자 입력을 피하고, 파라미터화된 쿼리 사용.

결론적으로, 조인은 정규화된 테이블을 효과적으로 통합하고, 서브쿼리는 유연한 데이터 조작을 가능하게 합니다. 이 두 도구를 마스터하면 PostgreSQL에서 복잡한 비즈니스 쿼리를 효율적으로 작성할 수 있습니다. 데이터베이스 전문가로서 이 기술을 활용해 숨겨진 인사이트를 발굴하세요.