JOIN: INNER·OUTER·SELF·CROSS

JOIN은 조건에 맞는 행의 쌍을 만든다

조인(JOIN)은 둘 이상의 테이블 또는 테이블 식에서 관련 행을 결합해 하나의 중간 결과를 만드는 연산이다. 단순히 두 표를 옆으로 붙이는 것이 아니라, 먼저 가능한 행의 쌍을 생각한 뒤 조인 조건이 TRUE가 되는 쌍과 조인 종류가 보존하는 불일치 행을 결과에 남긴다.

왼쪽 입력 × 오른쪽 입력
    → ON 또는 USING으로 일치 여부 판단
    → INNER·LEFT·RIGHT·FULL 규칙 적용
    → WHERE로 조인 결과 필터링
    → SELECT·GROUP BY·ORDER BY 등 후속 처리

JOIN 관련 용어는 서로 다른 분류 축에 속한다. 같은 수준의 조인 종류로만 외우면 SELF JOIN과 INNER JOIN의 관계를 잘못 판단하기 쉽다.

따라서 SELF JOIN은 INNER JOIN의 반대말이 아니다. 같은 테이블을 INNER JOIN할 수도 있고, 상위 행이 없는 최상위 조직원을 보존하기 위해 LEFT JOIN할 수도 있다. 또한 동등 조인은 조건이 =인 조인이고, INNER JOIN은 불일치 행을 버리는 보존 방식이므로 두 용어도 같은 분류가 아니다.

기본 문법

SELECT 출력_열
FROM 왼쪽_입력 AS l
     INNER JOIN 오른쪽_입력 AS r
       ON 조인_조건;

아래 예제는 SQL 표준의 JOIN 의미를 중심으로 작성했다. 테이블 별칭에 AS를 허용하는지, RIGHT·FULL JOIN을 직접 지원하는지는 DBMS마다 다를 수 있다. INNER는 생략할 수 있어 JOIN만 쓰면 일반적으로 INNER JOIN을 뜻한다. LEFT·RIGHT·FULL 뒤의 OUTER도 생략할 수 있으므로 LEFT JOIN과 LEFT OUTER JOIN은 같은 논리적 의미다.

조인 조건은 보통 기본키와 외래키처럼 관계를 나타내는 열을 비교하지만 반드시 등호만 사용해야 하는 것은 아니다. 예를 들어 급여가 어느 등급 구간에 포함되는지를 찾는 다음 문장은 비동등 INNER JOIN이다.

SELECT e.employee_name, g.grade_name
FROM employee AS e
     INNER JOIN salary_grade AS g
       ON e.salary BETWEEN g.min_salary AND g.max_salary;

결과 행 수는 키별 일치 개수로 계산한다

다음 예시는 중복 키, 불일치 키, NULL 키가 JOIN 결과에 미치는 영향을 한 번에 확인하기 위한 데이터다. left_item과 right_item은 설명용 독립 테이블이며 join_key에는 외래키 제약을 두지 않았다.

동등 조인 l.join_key = r.join_key에서 특정 값이 왼쪽에 a행, 오른쪽에 b행 있으면 그 값은 a × b개의 일치 행을 만든다.

• 키 10: 1 × 1 = 1행
• 키 20: 2 × 2 = 4행
• 키 30: 오른쪽에 없으므로 일치 0행
• 키 40: 왼쪽에 없으므로 일치 0행
• NULL: NULL = NULL이 TRUE가 아니므로 일치 0행

따라서 INNER JOIN의 총결과는 5행이다. SQL의 조회 결과는 기본적으로 중복을 자동 제거하지 않으므로, 관계의 다중성으로 같은 왼쪽 행이 여러 번 나타나는 것은 정상일 수 있다.

조인별 결과 행 수

이 계산에서 외부 조인의 불일치 행은 각 입력 행마다 한 번씩 NULL 확장된다. 반면 일치하는 행은 상대쪽 일치 개수만큼 반복되므로, “LEFT JOIN의 결과 행 수는 항상 왼쪽 행 수와 같다”는 설명은 틀리다. 후속 WHERE를 적용하기 전 LEFT JOIN 연산 자체는 왼쪽 각 행을 최소 한 번 보존할 뿐이며, 일치 상대가 여러 개면 결과는 왼쪽 행 수보다 커질 수 있다. 왼쪽 한 행이 만드는 결과 수는 일치 행이 없을 때 1이고, 일치 행이 있으면 그 일치 행 수와 같다.

좌우로 이동해 그림을 확인하세요.그림 크게 보기

JOIN은 행의 쌍을 만든다

키 10인 왼쪽 L1·L2와 오른쪽 R1·R2는 2×2=4개 행쌍을 만든다. LEFT JOIN에서는 대응이 없는 키 20의 L3를 NULL로 확장하여 총 5행이 된다.

INNER JOIN: 일치한 행의 쌍만 남긴다

INNER JOIN은 조인 조건이 TRUE인 행 쌍만 결과에 남긴다. 조건이 FALSE이거나 UNKNOWN이면 그 쌍은 제외된다.

SELECT l.left_id, r.right_id
FROM left_item AS l
     INNER JOIN right_item AS r
       ON l.join_key = r.join_key
ORDER BY l.left_id, r.right_id;

키 20의 두 왼쪽 행과 두 오른쪽 행이 서로 모두 짝지어져 4행이 된다는 점이 핵심이다. INNER JOIN은 일치하는 키를 한 번씩만 보여 주는 연산이 아니며, 필요할 때만 DISTINCT나 집계로 별도의 중복 제거·요약을 수행한다.

오래된 쉼표 조인 문법도 동등 INNER JOIN과 같은 결과를 만들 수 있다.

SELECT l.left_id, r.right_id
FROM left_item AS l, right_item AS r
WHERE l.join_key = r.join_key;

그러나 관계 조건과 일반 필터가 모두 WHERE에 섞이므로 현대적인 JOIN ... ON 문법이 의도를 구분하기 쉽다. 또한 “INNER JOIN에서 ON만 빼면 항상 카티션 곱이 된다”라고 단정하면 부정확하다. 명시적 INNER JOIN이 조인 조건을 요구하는 DBMS에서는 문법 오류가 날 수 있고, 쉼표로 여러 테이블을 나열한 뒤 관계 조건을 빠뜨렸을 때에는 의도하지 않은 카티션 곱이 만들어질 수 있다. 모든 조합이 목적이라면 CROSS JOIN을 명시한다.

OUTER JOIN: 보존할 쪽의 불일치 행을 NULL로 확장한다

외부 조인(OUTER JOIN)은 먼저 조건이 TRUE인 행 쌍을 만든 뒤, 지정된 쪽에서 상대와 한 번도 일치하지 않은 행을 추가한다. 추가된 행에서 상대쪽 열은 NULL이 된다. 이 NULL은 원본에 저장된 NULL일 수도 있지만, 외부 조인이 불일치 행을 보존하면서 새로 채운 NULL 확장(null extension)일 수도 있다.

LEFT JOIN

SELECT l.left_id, r.right_id
FROM left_item AS l
     LEFT JOIN right_item AS r
       ON l.join_key = r.join_key
ORDER BY l.left_id, r.right_id;

키 10과 20의 일치 결과 5행에 왼쪽 4번과 5번의 불일치 행이 추가되어 7행이 된다. 왼쪽 5번의 NULL 키는 오른쪽 105번의 NULL 키와 일반 등호 비교로 일치하지 않으므로, 오른쪽 열이 NULL인 별도 행으로 보존된다.

RIGHT JOIN과 FULL OUTER JOIN

RIGHT JOIN은 오른쪽 입력을 보존하는 LEFT JOIN의 반대 방향이다. 다음 두 구조는 선택 열의 순서를 맞추면 같은 행 집합을 표현할 수 있다.

-- 오른쪽 입력을 보존
FROM left_item AS l
RIGHT JOIN right_item AS r
  ON l.join_key = r.join_key

-- 입력 위치를 바꾸어 LEFT JOIN으로 표현
FROM right_item AS r
LEFT JOIN left_item AS l
  ON l.join_key = r.join_key

FULL OUTER JOIN은 일치한 5행, 불일치한 왼쪽 2행, 불일치한 오른쪽 2행을 모두 반환하여 예제에서는 9행이 된다. RIGHT JOIN과 FULL OUTER JOIN의 지원 여부 및 대체 구문은 DBMS마다 다를 수 있으므로 제품이 지정된 문제에서는 해당 방언을 확인한다.

SELECT l.left_id, r.right_id
FROM left_item AS l
     FULL OUTER JOIN right_item AS r
       ON l.join_key = r.join_key;

ON과 WHERE는 외부 조인에서 역할이 다르다

ON은 두 입력에서 어떤 행 쌍을 일치한 것으로 볼지 정한다. 외부 조인의 NULL 확장 행은 이 판단 뒤에 추가된다. WHERE는 그렇게 만들어진 조인 결과 전체에 다시 적용된다.

다음 문장은 오른쪽의 활성 행만 일치 대상으로 삼되 왼쪽 행은 모두 보존한다.

SELECT l.left_id, r.right_id
FROM left_item AS l
     LEFT JOIN right_item AS r
       ON l.join_key = r.join_key
      AND r.enabled = 'Y'
ORDER BY l.left_id, r.right_id;

예제 결과는 5행이다. 왼쪽 1·2·3번은 활성 오른쪽 행과 일치하고, 4·5번은 오른쪽 열이 NULL인 채 남는다.

반면 다음 문장은 먼저 모든 오른쪽 행과 LEFT JOIN한 뒤 WHERE에서 활성 행만 남긴다.

SELECT l.left_id, r.right_id
FROM left_item AS l
     LEFT JOIN right_item AS r
       ON l.join_key = r.join_key
WHERE r.enabled = 'Y'
ORDER BY l.left_id, r.right_id;

불일치 행의 r.enabled는 NULL이므로 r.enabled = 'Y'가 TRUE가 되지 않는다. 따라서 왼쪽 4·5번이 제거되고 결과는 3행이다. 이처럼 외부 조인의 NULL 확장 쪽에 NULL을 거부하는 조건을 WHERE에 두면 보존 효과가 사라져 INNER JOIN과 같은 행만 남는 경우가 많다. 다만 WHERE r.right_id IS NULL처럼 NULL 확장 행을 의도적으로 선택하는 조건도 있으므로, “오른쪽 조건이 WHERE에 있으면 무조건 INNER JOIN이 된다”라고 외우면 안 된다.

일치 상대가 없는 행 찾기

LEFT JOIN 결과에서 오른쪽의 NOT NULL 키가 NULL인 행만 고르면 왼쪽의 불일치 행을 찾을 수 있다.

SELECT l.left_id, l.left_name
FROM left_item AS l
     LEFT JOIN right_item AS r
       ON l.join_key = r.join_key
WHERE r.right_id IS NULL
ORDER BY l.left_id;

결과는 왼쪽 4번과 5번이다. 판별 열에는 원래 NULL일 수 있는 일반 열보다 기본키처럼 NULL이 될 수 없는 열을 사용해야 “일치했지만 해당 열의 원본 값이 NULL인 행”과 혼동하지 않는다.

좌우로 이동해 그림을 확인하세요.그림 크게 보기

외부 조인의 ON과 WHERE

왼쪽 ID가 1·2이고 오른쪽에는 (1,N)만 있다고 하자. LEFT JOIN의 ON에 active='Y'를 넣으면 (1,NULL)·(2,NULL)이 남는다. ID로 조인한 뒤 WHERE에 같은 조건을 넣으면 통과 행이 없다. 모든 오른쪽 WHERE 조건이 INNER JOIN과 같아지는 것은 아니다.

NULL 조인 키는 일반 등호로 서로 일치하지 않는다

SQL의 일반 비교에서 NULL = NULL은 TRUE가 아니라 UNKNOWN이다. ON 조건이 TRUE인 행 쌍만 일치하므로 다음 동등 조인에서는 양쪽 NULL 키가 서로 연결되지 않는다.

ON l.join_key = r.join_key

업무 규칙상 양쪽 NULL을 같은 그룹으로 연결해야 한다면 그 의도를 조건에 직접 표현해야 한다.

ON l.join_key = r.join_key
OR (l.join_key IS NULL AND r.join_key IS NULL)

이 조건을 사용하면 예제의 왼쪽 5번과 오른쪽 105번도 일치한다. 다만 양쪽에 NULL 키가 여러 개 있으면 그 개수의 곱만큼 행이 늘어난다. 일부 DBMS는 NULL 안전 비교 연산자를 별도로 제공하지만 문법이 제품마다 다르므로 범용 SQL로 단정하지 않는다.

SELF JOIN: 같은 테이블에 서로 다른 역할을 부여한다

SELF JOIN은 SELF JOIN이라는 별도 키워드가 아니라, 같은 테이블을 두 번 이상 참조하면서 각각 다른 별칭을 부여하는 작성 방식이다. 조직도의 직원과 상사, 상품의 상위 분류와 하위 분류처럼 한 테이블 안의 행끼리 관계를 맺을 때 사용한다.

CREATE TABLE employee (
    employee_id   INTEGER PRIMARY KEY,
    employee_name VARCHAR(20) NOT NULL,
    manager_id    INTEGER
);

INSERT INTO employee VALUES
    (1, '대표',     NULL),
    (2, '개발팀장', 1),
    (3, '개발자',   2),
    (4, '기획팀장', 1);

입력 직원 데이터는 (1, 대표, NULL), (2, 개발팀장, 1), (3, 개발자, 2), (4, 기획팀장, 1)이며 마지막 값은 manager_id다.

최상위 직원까지 보존하려면 직원 역할 e를 왼쪽에 두고 상사 역할 m을 LEFT JOIN한다.

SELECT e.employee_id,
       e.employee_name AS employee_name,
       m.employee_name AS manager_name
FROM employee AS e
     LEFT JOIN employee AS m
       ON e.manager_id = m.employee_id
ORDER BY e.employee_id;

같은 문장을 INNER JOIN으로 바꾸면 manager_id가 NULL인 대표는 일치 상대가 없어서 사라진다. 별칭은 단순 축약이 아니라 각 참조의 역할을 구분한다. 또한 한 번의 SELF JOIN은 직원과 직속 상사처럼 한 단계의 관계를 연결하며, 깊이가 정해지지 않은 전체 계층 탐색은 별도의 재귀 질의 기법이 필요하다.

CROSS JOIN: 모든 조합을 만든다

CROSS JOIN은 조인 조건 없이 왼쪽 각 행과 오른쪽 모든 행을 짝지어 카티션 곱(Cartesian product)을 만든다.

SELECT l.left_id, r.right_id
FROM left_item AS l
     CROSS JOIN right_item AS r;

왼쪽이 N행이고 오른쪽이 M행이면 결과는 N × M행이다. 예제는 5 × 5 = 25행이며, 어느 한쪽이 0행이면 결과도 0행이다. 상품과 색상·사이즈의 모든 가능한 조합을 만들 때처럼 모든 조합 자체가 목적일 때 사용한다.

두 테이블만 놓고 보면 CROSS JOIN은 INNER JOIN ... ON TRUE 또는 조인 조건 없는 쉼표 나열과 같은 행 조합을 만들 수 있다. 그러나 여러 조인이 섞이면 결합 우선순위와 가독성 문제가 생길 수 있으므로, 의도한 연산을 CROSS JOIN과 JOIN ... ON으로 명시하는 편이 안전하다.

조인 조건을 적는 ON·USING·NATURAL

시험에서는 ON이 가장 일반적인 조건 방식이라는 점과 USING이 같은 이름의 열에 대한 동등 조건의 축약이라는 점을 구분한다. NATURAL JOIN은 현재 우연히 이름이 같은 열까지 모두 조건으로 사용하므로, 의도하지 않은 열이 추가되면 결과가 바뀔 수 있다. 실무 예시와 답안에서는 연결 열을 명시하는 방식이 판독하기 쉽다.

조인 조건의 분류와 출력 열

세타 조인은 비교 조건 θ를 사용하며 동등 조인은 그 조건이 등호인 경우다. 따라서 동등 조인은 세타 조인의 특수한 형태다. 자연 조인은 공통 이름의 속성들을 동등 조건으로 묶고 중복 출력 열도 정리한다는 별도의 규칙을 갖는다.

a(k,x)와 b(k,y)를 JOIN … USING(k)로 결합한 뒤 SELECT *로 조회하면 공통 열 k는 한 번만 출력되므로 열은 k·x·y 세 개다. ON으로 동일 조건을 적은 경우의 출력 열과 혼동하지 않는다.

일반 동등 비교에서 NULL과 NULL은 TRUE가 아니다. 업무가 NULL끼리도 연결하도록 명시한다면 (a.k=b.k OR (a.k IS NULL AND b.k IS NULL))처럼 별도의 조건이 필요하다. 이때 양쪽에 NULL 키가 여러 개 있으면 서로의 모든 일치 쌍이 생겨 결과가 증가할 수 있다.