조인(JOIN) 완전 정복: 등가·비등가·셀프조인과 그룹함수

여러 테이블을 무결성 있게 연결하는 조인의 원리부터 등가·포괄·비등가·셀프 조인까지, 그리고 행 집합을 하나로 요약하는 그룹 함수(AVG·SUM·MIN·MAX·COUNT)와 NULL 처리까지 정리했다.

게시 2025/02/07

By 이소연

27 분읽는 시간

Oracle SQL/DB 학습 노트 시리즈의 네 번째 글이다. 데이터가 여러 테이블에 나뉘어 저장되는 이유와, 그렇게 쪼개진 데이터를 다시 무결성 있게 연결하는 조인을 정리한다. 마지막으로 행 집합을 하나의 결과로 요약하는 그룹 함수까지 다룬다.

[ 목차 ]

조인이란 — 카테시안 곱과 무결성
조인의 규칙과 FROM절 ALIAS
등가 조인과 포괄 조인
비등가 조인과 셀프 조인
그룹 함수 — AVG·SUM·MIN·MAX·COUNT

ㅇ 조인이란 — 카테시안 곱과 무결성

지금까지는 테이블 하나에서만 데이터를 검색했다. 하지만 “우리 회사에 근무하는 사원의 이름과 그 사원이 근무하는 부서 이름을 출력하라” 같은 질문은 한 테이블로 풀리지 않는다. 사원 이름은 employees 테이블에, 부서 이름은 departments 테이블에 있기 때문이다. 이렇게 여러 테이블에서 데이터를 검색하는 방법이 조인(JOIN)이다.

그런데 FROM절에 두 테이블을 그냥 나열하기만 하면 어떻게 될까?

  
SELECT e.last_name, d.department_name
FROM employees e, departments d

연결 조건이 없으면 모든 경우의 수가 조합되어 출력된다. 사원 107명 × 부서 27개 = 2,889개의 행이 쏟아진다. 이렇게 조인 조건을 생략하거나 부적합하게 주었을 때 모든 조합이 출력되는 현상을 카테시안 곱(Cartesian product)이라 한다.

무결성이 보장되어야 행을 연결할 수 있다

행을 아무렇게나 연결하면 안 된다. 두 테이블의 행을 매칭할 때는 무결성이 보장되어야 한다.

앞의 두 사원은 employees.department_id = departments.department_id로 데이터가 일치한다 → 무결성 보장
세 번째 사원은 두 부서번호가 서로 다르다 → 데이터 불일치 → 무결성이 깨진 연결

즉 조건에 맞는 데이터끼리만 행을 연결해야 하고, 이 조건이 바로 조인 조건이다. 위 예에서는 d.department_id = e.department_id가 조인 조건이 된다.

조인 : 여러 개의 테이블에서 데이터를 검색하는 방법. 테이블들의 행을 하나로 연결하되, 무결성을 보장하는 조건(조인 조건)에 맞는 데이터끼리만 연결한다.

ㅇ 조인의 규칙과 FROM절 ALIAS

조인을 사용할 때 지켜야 할 규칙이 두 가지 있다.

첫째, n개 테이블을 조인하려면 WHERE절에 n-1개의 조인 조건을 명시한다. WHERE절이 먼저 실행되어 조건에 맞는 데이터를 먼저 걸러내기 때문이다.

둘째, 조인할 테이블에 같은 이름의 컬럼이 있으면 SELECT절에서 반드시 테이블 이름을 접두어로 붙인다.

last_name은 employees에만 있고 department_name은 departments에만 있어 헷갈릴 일이 없다. 하지만 department_id는 두 테이블 모두에 있다. 접두어 없이 쓰면 “어느 테이블에서 가져와야 하지?”라며 파싱 1단계 검증에서 에러가 난다.

사실 접두어는 고유한 컬럼이라도 항상 붙이는 게 좋다. 접두어가 없으면 시스템이 모든 테이블을 다 뒤지지만, 접두어가 있으면 그 테이블만 뒤지므로 파싱 성능이 좋아진다. 안 그래도 조인은 시스템 성능을 떨어뜨리는 작업(성능을 떨어뜨리는 대표적 기능이 정렬과 조인이다)이라, 그나마 성능을 보장하도록 접두어를 적극 권장한다.

FROM절 ALIAS로 별칭 주기

테이블 이름을 매번 길게 쓰는 대신, FROM절에서 이 문장이 실행되는 동안만 쓸 별칭(ALIAS)을 줄 수 있다.

  
SELECT e.last_name, d.department_name, e.department_id
FROM employees e, departments d
WHERE e.department_id = d.department_id

employees를 e로, departments를 d로 부르겠다고 선언한 것이다. 현업에서 많이 쓰고 가독성도 좋다. 다만 몇 가지 주의가 있다.

풀네임과 별칭은 혼용할 수 없다. employees e라고 별칭을 주면 이 문장에서 employees라는 이름은 더 이상 존재하지 않는다. 풀네임을 썼으면 끝까지 풀네임, 별칭을 썼으면 끝까지 별칭이다.
실행 순서가 FROM → WHERE → SELECT → ORDER BY라서, WHERE절에서는 컬럼 별칭을 쓸 수 없지만 FROM절에서 테이블 별칭을 주는 것은 가능하다. FROM이 가장 먼저 실행되기 때문이다.

SELECT절에 e.department_id 대신 d.department_id를 써도 결과는 똑같이 나온다. 다만 의도를 드러내는 쪽으로 쓰는 게 좋다. 사원을 나열하며 그들이 근무하는 부서번호를 보고 싶으면 employees 쪽을, 부서를 기준으로 나열하고 싶으면 departments 쪽을 기준으로 쓴다.

참고로 이 시리즈에서는 Oracle 전용 조인 문법을 다룬다. 표준 SQL-1999 조인은 명령문 형태만 다를 뿐 수행하는 일은 같다.

ㅇ 등가 조인과 포괄 조인

조인 조건은 데이터가 어떻게 대응하느냐에 따라 크게 세 종류로 나뉜다.

등가 조인 : 두 테이블에 같은 데이터가 존재할 때. = 연산자 사용
비등가 조인 : 데이터가 다를 때. = 외의 비교 연산자(주로 BETWEEN) 사용
포괄 조인 : 어느 한쪽 테이블에 데이터가 더 많을 때. 부족한 쪽에 (+)를 붙임

여기에 테이블이 하나뿐인데 자기 자신과 조인하는 자체(셀프) 조인이 더해진다. 셀프 조인 안에서도 등가·비등가·포괄이 모두 나타날 수 있다.

등가 조인 (equi join)

두 테이블에 같은 데이터가 존재할 때 =로 연결한다. PK와 FK로 연결된 테이블에서 가장 많이 발생한다. 자식 테이블이 부모를 참조하고 있어 값이 어긋날 수가 없기 때문이다. 현업에서 일어나는 조인의 99%가 등가 조인이고, 그 99%가 PK = FK 형태다.

  
SELECT e.last_name, d.department_name, e.department_id
FROM employees e, departments d
WHERE e.department_id = d.department_id

이 쿼리는 106 rows selected.를 출력한다. 사원은 107명인데 왜 한 명이 빠졌을까? 부서번호가 없는(NULL인) 사원이 있기 때문이다. employees에서 department_id는 FK라 NULL일 수 있지만, departments에서 department_id는 PK라 NULL을 가질 수 없다. 따라서 e.department_id = d.department_id 식 자체가 성립하지 않아 그 사원은 결과에서 제외된다.

FK는 부모의 PK를 “참조하겠다”는 뜻이지 “무조건 사용한다”는 뜻이 아니다. 그래서 어떤 행은 FK 값이 비어 있을 수 있고, 등가 조인에서는 그런 행이 세어지지 않는다. 이런 행까지 살리려면 뒤에 나오는 포괄 조인이 필요하다.

AND로 추가 조건 더하기

“90번 부서에 근무하는 사원의 이름, 급여, 부서 이름을 출력하라”처럼 조건이 붙으면 AND로 이어 준다.

  
SELECT e.last_name, e.salary, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND e.department_id = 90

일반 조건은 조인 조건과 함께(그리고) 만족해야 하므로 AND로 묶는다. OR로 묶으면 카테시안 곱이 터져 버린다. 조인 조건과 일반 조건의 순서는 상관없지만, 성능을 따지면 조인을 먼저 거는 편이 낫다. 버리고 나서 정리하는 것보다, 정리하고 나서 버리는 게 낫기 때문이다.

세 개 이상의 테이블 조인

테이블이 셋이면 조인 조건은 3 - 1 = 2개가 필요하다.

  
SELECT e.last_name, d.department_name, l.city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id

사원 이름과 근무 부서, 그리고 그 부서가 위치한 도시까지 한 번에 얻는다. 여기서 한 가지 실험. 도시 이름만 알고 싶어서 SELECT에 departments 컬럼이 하나도 없더라도, 두 테이블을 잇는 다리 역할을 하므로 departments는 FROM에 남겨 둬야 한다.

  
SELECT e.last_name, l.city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id

만약 “SELECT에 안 쓰니까” departments를 FROM에서 빼면 에러가 난다. 조인 조건에서 이미 d.location_id라고 그 테이블을 참조했기 때문이다.

  
SELECT e.last_name, l.city
FROM employees e, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
-- ORA-00904: "D"."LOCATION_ID": invalid identifier

그렇다면 애초에 테이블을 쪼개지 않으면 조인할 일도 없지 않을까? 가능은 하지만, 무결성과 중복 제거를 위해 시스템은 테이블을 잘게 나눈다(정규화). 신발 창고·옷 창고·식품 창고를 따로 두듯 쪼개면 중복이 최소화되지만, 그만큼 조인이 많아져 성능은 떨어진다. 합치면 빠르지만 무결성이 낮아진다. 이 트레이드오프를 한 번에 무마하는 방법은… 결국 고사양 장비다.

포괄 조인 (outer join)

포괄 조인은 조인 기준 열의 한쪽이 NULL이어도 강제로 출력하는 방식이다. 부족한 쪽에 아우터 연산자 (+)를 붙여 NULL 행을 끼워 넣는다. 핵심은 빠짐없이 출력하려는 쪽의 반대편, 즉 비워 둬도 괜찮은 쪽에 (+)를 붙인다는 것이다.

“우리 회사에 근무하는 모든 사원을 나열하고, 그 사원이 근무하는 부서 이름을 출력하라”를 풀어 보자. 부서가 배정되지 않은 사원도 빠뜨리지 않아야 한다.

  
SELECT e.last_name, e.salary, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id (+)

이제 107 rows selected. — 부서 이름이 NULL인 사원까지 모두 나온다. (+) 없이 그냥 등가 조인을 하면 부서가 없는 사원 한 명이 날아가 106개만 나온다.

반대로 (+)를 왼쪽으로 옮겨 보자.

  
WHERE e.department_id (+) = d.department_id

이번엔 “모든 부서“가 기준이 된다. 근무하는 사원이 없는 부서까지 모두 출력되어 122 rows selected.가 나온다. 이 숫자들을 해석할 수 있어야 한다.

122 - 106 = 16 : 사원이 없는 부서의 수 (122 = 모든 사원 + 사원 없는 부서, 106 = 근무 부서가 있는 사원 수)
27 - 16 = 11 : 사원이 있는 부서의 수 (전체 부서 27 − 사원 없는 부서 16)

이래서 SELECT 문이 어렵다. 결과 숫자를 해석하려면 테이블 구조를 정확히 이해하고 있다는 전제가 깔린다.

ㅇ 비등가 조인과 셀프 조인

다음 시간에 배울 서브쿼리를 익히면 웬만한 조인은 그것으로 대체된다. 그래서 등가 조인만 확실히 잡고, 여기서부터는 실습보다 개념 이해 위주로 가볍게 짚는다.

비등가 조인 (non-equi join)

데이터가 서로 다를 때 수행한다. 예를 들어 salary는 구체적인 급여 액수(숫자)이고, job_grades는 급여 구간에 매긴 등급(low ~ high)이다. 숫자인 급여를 문자인 등급에 연결하려면 =로는 안 되고, 등호가 아닌 연산자 — 보통 BETWEEN — 를 쓴다.

  
SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal

각 사원의 급여가 어느 등급 구간에 속하는지 매겨진다.

자체 참조와 셀프 조인 (self join)

셀프 조인을 이해하려면 먼저 자체 참조를 알아야 한다. 자체 참조는 테이블 내에서 자기 자신을 향해 FK를 설정한 것이다.

원래 사원 테이블(employees)과 관리자 테이블(manager)이 따로 있었다고 하자. 그런데 관리자 테이블은 결국 사원 중 관리자만 모아 둔 것이다(사원번호에 없는 번호가 관리자 번호가 될 수는 없다). 두 테이블을 따로 두면 사원 테이블에서 이름을 바꿀 때 관리자 테이블에서도 또 바꿔야 한다 — 데이터 중복이자 불일치다. 그래서 하나로 통합한다. 결국 employees 테이블의 manager_id는 같은 테이블의 employee_id를 참조하는 FK가 된다.

자체 참조 컬럼의 첫 번째 행은 무조건 NULL이다. 아무것도 없는 데서 값을 가져올 수 없기 때문이다. 어떤 컬럼의 첫 행이 NULL이라면 자체 참조를 의심해 볼 만하다.

이제 자체 참조 테이블을 조인하려면, ALIAS로 한 테이블을 여러 개인 것처럼 만든다. 이것이 셀프 조인이다. FROM절 별칭으로 하나의 테이블을 여러 개로 쪼개 쓰는 이 기능은 대단히 강력하니 꼭 기억해 두자.

“우리 회사에 근무하는 사원의 번호와 이름, 그 사원을 관리하는 관리자의 번호와 이름을 출력하라”를 풀어 보자. 관리자도 결국 employees에 저장된 한 명의 사원이다. 찾으려는 데이터가 같은 테이블 안에 있으니 셀프 조인이다.

  
SELECT e.employee_id, e.last_name, e.manager_id, m.last_name
FROM employees e, employees m
WHERE e.manager_id = m.employee_id

employees를 e(사원 역할)와 m(관리자 역할)으로 두 번 부르고, 사원 테이블의 관리자 번호 = 관리자 테이블의 사원 번호로 연결한다. 짜기가 까다롭고 테이블 구조를 완전히 꿰고 있어야 가능하다. 다행히 셀프 조인 역시 대부분 서브쿼리로 대체되므로, 지금은 개념만 이해하고 넘어가자.

ㅇ 그룹 함수 — AVG·SUM·MIN·MAX·COUNT

함수는 하나 또는 다수의 값(인수)을 받아 자기가 가진 계산식으로 처리해 1개의 결과를 반환하는 것이다. 예컨대 SUM은 100, 200, 100, 200 네 값을 받아 600이라는 하나의 결과를 돌려준다.

그룹 함수는 행 집합에 작용하여 그룹당 하나의 결과를 생성한다. 여기서 그룹은 받아들이는 데이터의 범위다. 테이블 전체를 한 그룹으로 받을 수도, 사용자가 지정한 특정 그룹별로 처리할 수도 있다.

함수	의미
AVG	평균
SUM	합계
MIN	최솟값
MAX	최댓값
COUNT	개수
STDDEV / VARIANCE	표준편차 / 분산 (잘 쓰지 않음)

가장 중요한 규칙 하나. 그룹 함수는 NULL 값을 무시한다. (단, COUNT(*)는 예외.) 그래서 그룹 함수를 쓸 때는 항상 NULL을 신경 써야 한다.

  
SELECT AVG(컬럼), SUM(컬럼),
       MIN(컬럼), MAX(컬럼)
FROM 테이블
WHERE 조건식

AVG, SUM — 숫자만 가능

평균과 합은 산술 연산을 계산식으로 쓰므로 숫자 데이터만 받는다. 문자나 날짜를 넣으면 에러다(입사일의 총합은 의미가 없다). WHERE절로 대상을 좁힐 수 있다.

  
SELECT AVG(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%'

업무 이름에 REP가 들어가는 사원들의 급여 평균과 합을 구한다.

MIN, MAX — 모든 데이터 유형 가능

최소·최대의 기준이 아스키 코드라서 숫자·날짜·문자 모두에 쓸 수 있다.

SELECT MIN(salary), MAX(salary)     -- 최저급여 2100 / 최고급여 24000
FROM employees

SELECT MIN(hire_date), MAX(hire_date)  -- 가장 오래된 입사 / 가장 최근 입사
FROM employees

SELECT MIN(last_name), MAX(last_name)  -- 알파벳 맨 앞 Abel / 맨 뒤 Zlotkey
FROM employees

NULL 처리에 관한 주의

AVG, SUM, MIN, MAX, COUNT(컬럼)은 모두 NULL을 무시한다. 컬럼을 인수로 받아 그 안의 ‘값’을 다루기 때문이다. 반면 COUNT(*)는 행 자체를 세므로 NULL을 포함한다.

이 차이가 의도와 다른 결과를 낳을 수 있다. “평균 = 총합 ÷ 사람 수”가 맞는지 보너스 비율로 확인해 보자.

SELECT AVG(commission_pct), SUM(commission_pct)/107
FROM employees
-- AVG: .222857143    SUM/107: .072897196

AVG(commission_pct) : 보너스를 받는 35명으로만 나눈 값 (NULL 제외)
SUM(commission_pct)/107 : 보너스 안 받는 사람까지 전체 107명으로 나눈 값

전체 107명 기준 평균을 구하려면 NULL을 0으로 되살리는 NVL 함수를 중첩한다.

  
SELECT AVG(NVL(commission_pct,0)), SUM(commission_pct)/107
FROM employees
-- 둘 다 .072897196 로 일치

중첩 함수는 안쪽이 먼저 실행된다. AVG(NVL(commission_pct,0))는 NVL이 먼저 NULL을 0으로 바꾸고, 그제야 NULL이 사라져 107명 전체로 평균을 낸다.

COUNT — 행의 개수

COUNT는 출력되는 행의 개수를 반환한다. WHERE절이 먼저 실행되어 조건에 맞는 행만 그룹 함수의 대상이 된다.

COUNT(*) : 테이블의 행 개수. NULL·중복 행 모두 포함
COUNT(컬럼) : 해당 컬럼이 NULL이 아닌 행의 개수

  
SELECT COUNT(*)
FROM employees
WHERE department_id = 50
-- 45  (50번 부서 근무 사원 수)

SELECT COUNT(commission_pct)
FROM employees
-- 35  (보너스를 받는 사원 수)

DISTINCT — 중복 제거하고 세기

COUNT(DISTINCT 컬럼)은 중복을 제거하고 개수를 센다. 여기서 SELECT절의 DISTINCT와 구분해야 한다. SELECT의 DISTINCT는 출력되는 데이터에서 중복을 제거하고, 그룹 함수 안의 DISTINCT는 값을 받아들일 때 중복을 제거한다.

  
SELECT COUNT(department_id)
FROM employees
-- 106  (중복 부서번호까지 모두)

SELECT COUNT(DISTINCT department_id)
FROM employees
-- 11   (사원이 실제 근무하는 부서의 종류)

COUNT(컬럼)은 사실 COUNT(ALL 컬럼)이며, 기본값 ALL이 생략된 것이다. 참고로 DISTINCT를 COUNT 바깥(SELECT DISTINCT COUNT(...))으로 옮기면 COUNT 결과 하나를 중복 제거하라는 뜻이 되어 아무 의미가 없다. 그냥 원래 COUNT 값이 그대로 나온다.

조인은 쪼개진 테이블을 무결성 있게 다시 잇는 작업이고, 그룹 함수는 흩어진 행을 하나의 값으로 요약하는 작업이다. 그런데 지금까지의 그룹 함수는 테이블 전체를 한 덩어리로만 묶었다. 다음 글에서는 GROUP BY로 원하는 그룹을 나누고, HAVING으로 그 그룹에 조건을 거는 법을 다룬다.

CS, Database