[SQL] Join(조인) — 거꾸로 바라본 세상

Join(조인)

조인은 둘 이상의 테이블에서 조건에 맞는 행을 결합해 결과 집합을 만드는 연산이다. 결합한 결과에는 양쪽 테이블의 열을 선택할 수 있다.

조인의 종류

INNER JOIN
CROSS JOIN
OUTER JOIN
SELF JOIN

1. INNER JOIN(내부 조인)

INNER JOIN은 키 값이 있는 테이블의 컬럼 값을 비교 후 조건에 맞는 값을 가져오는 것이다. 간단히 말하면 서로 연관된 내용만 검색하는 조인 방법이다.

(Inner Join)

사용법

SQ은 명시적 조인 표현과 암시적 조인 표현으로 구분을 지정한다.

(1) 명시적 조인 표현

명시적 조인표현 에서는 테이블에 조인을 하라는 것을 지정하기 위해 ‘JOIN’키워드를 사용하고 ON의 키워드를 조인에 대한 구문을 지정하는데 사용한다.

SELECT *
FROM EMPLOYEE INNER JOIN DEPARTMENT

ON EMPLOYEE.DepartmentID = DEPARTMENT.DepartmentID;

(2) 암시적 조인 표현

암시적 조인 표현은 SELECT 구문의 FROM절에서 콤마(,)를 사용하여 단순히 조인을 위한 여러 테이블을 나여할하기만 하면된다.

SELECT *
FROM EMPLOYEE, DEPARTMENT
WHERE EMPLOYEE.DepartmentID = DEPARTMENT.DepartmentID;

(Inner join 결과)

1-1. EQUI JOIN(동등조인)

EQUAL 연산자(=)를 사용해서 EQUI JOIN이라고 한다.

WHERE 절에 기술되는 JOIN 조건을 검사해서 양쪽 테이블에 같은 조건의 값이 존재할 경우 해당 데이터를 가져오는 조인 방법이다.

SELECT *
FROM EMPLOYEE , DEPARTMENT
WHERE EMPLOYEE.DepartmentID = Department.DepartmentID;

SELECT *
FROM EMPLOYEE JOIN DEPARTMENT
ON EMPLOYEE.DEPARTMENTID = DEPARTMENT.DEPARTMENTID;

2. CROSS JOIN(교차조인)

CROSS JOIN은 Cartesian Product(카디션 곱)이라고도 하며 조인되는 두 테이블에서 곱집합을 반환한다.

즉, 첫 번째 테이블의 각 행을 두 번째 테이블의 모든 행과 결합한다. 첫 테이블에 m행, 둘째 테이블에 n행이 있으면 결과는 m×n행이다. 열 수를 곱한다는 뜻이 아니다.

(CROSS JOIN 방법)

사용법

SELECT *
FROM EMPLOYEE CROSS JOIN DEPARTMENT;

employee 테이블의 튜플이 6개이고 department 테이블의 튜플이 4개면 6 X 4 = 24개의 결과가 나오는 것이다.

3. OUTER JOIN

Outer Join은 조인하는 여러테이블에서 한 쪽에는 데이터가 있고 한 쪽에는 데이터가 없는 경우, 데이터가 있는 쪽 테이블의 내용을 전부 출력하는 방법이다. 즉, 조인 조건에 만족하지 않아도 해당 행을 출력하고 싶을 때 사용할 수 있다.

Outer Join에는 LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN이 있다.

3-1. LEFT OUTER JOIN

LEFT OUTER JOIN은 조인문의 왼쪽에 있는 테이블의 모든 결과를 가져온 후 오른쪽 테이블의 데이터를 매칭하고, 매칭되는 데이터가 없는 경우 NULL을 표시한다.

<Left outer join>

사용법

SELECT *
FROM EMPLOYEE E LEFT OUTER JOIN DEPARTMENT D 
ON E.DEPARTMENTID = D.DEPARTMENTID;

<ORACLE>
SELECT *
FROM EMPLOYEE E, DEPARTMENT D

WHERE E.DepartmentID = D.DepartmentID(+);

3-2. RIGHT OUTER JOIN

RIGHT OUTER JOIN은 조인문의 오른쪽에 있는 테이블의 모든 결과를 가져온 후 왼쪽의 테이블의 데이터를 매칭하고, 매칭되는 데이터가 없는 경우 NULL을 표시한다.

<RIGHT OUTER JOIN>

사용법

SELECT *
FROM EMPLOYEE E RIGHT OUTER JOIN DEPARTMENT D
ON E.DepartmentID = D.DepartmentID;

<ORACLE>
SELECT *
FROM EMPLOYEE E , DEPARTMENT D
WHERE E.DepartmentID(+) = D.DepartmentID;

3-3. FULL OUTER JOIN

Full Outer Join은 LEFT OUTER JOIN과 RIGHT OUTER JOIN을 합친 것이다.

양쪽 모두 조건이 일치하지 않는 것들까지 모두 결합하여 출력한다.

사용법

SELECT *
FROM EMPLOYEE E FULL OUTER JOIN DEPARTMENT D
ON E.DepartmentID = D.DepartmentID;

MySql에서는 Full Outer Join 키워드가안되므로 이런식으로 해준다.(물론 방법은 다양하지만..)

SELECT *
FROM EMPLOYEE E LEFT OUTER JOIN department D
ON E.DepartmentID = D.DepartmentID
UNION
SELECT *
FROM EMPLOYEE E RIGHT OUTER JOIN DEPARTMENT D
ON E.DEPARTMENTID = D.DEPARTMENTID;

5. SELF JOIN

SELF JOIN은 테이블에서 자기자신을 조인을 시키는 것이다.

사용법

select F.empno, F.ename, S.empno, S.ename, S.job
from emp F inner join Emp S
on F.job = S.job
where F.empno < S.empno
order by F.empno, S.empno;

참고사이트 :

http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

https://ko.wikipedia.org/wiki/Join_(SQL)#.EC.99.B8.EB.B6.80_.EC.A1.B0.EC.9D.B8

작은 표로 결과 행을 세어 보기

고객 테이블에 (1, 민수), (2, 지연), (3, 수진) 세 행이 있고, 주문 테이블에 (101, 고객 1), (102, 고객 1), (103, 고객 2) 세 행이 있다고 가정한다. 고객 1이 주문 두 건을 했으므로 INNER JOIN 결과는 고객 수 3행이 아니라 주문에 연결된 행 3개다. 고객 3은 주문이 없어 빠진다.

SELECT c.customer_id, c.name, o.order_id
FROM customers AS c
INNER JOIN orders AS o
  ON o.customer_id = c.customer_id;

결과는 (1, 민수, 101), (1, 민수, 102), (2, 지연, 103)이다. 반대로 고객 목록을 유지하면서 주문이 없는 고객도 보고 싶으면 LEFT JOIN을 사용한다. 이때 수진의 order_id는 NULL이 되어 결과가 4행이다. SELF JOIN은 별도의 종류의 결합 알고리즘이 아니라 같은 테이블을 두 별칭으로 참조하는 방식이다. 원문의 직원 예시도 직급이 같은 서로 다른 두 행을 조합한 것이다.

flowchart LR
  C[customers 3행] --> J[customer_id로 결합]
  O[orders 3행] --> J
  J --> I[INNER: 매칭된 3행]
  J --> L[LEFT: 고객을 보존한 4행]

`ON`과 `WHERE` 위치가 중요한 경우

-- 주문이 없어도 고객 3을 남긴다.
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id AND o.status = 'paid';

-- WHERE가 오른쪽 테이블의 NULL 행을 걸러 고객 3을 제외한다.
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
WHERE o.status = 'paid';

첫 번째는 “모든 고객과 그 고객의 유료 주문”이고, 두 번째는 “유료 주문을 가진 고객”이다. 오른쪽 테이블의 필터를 WHERE에 놓으면 매칭이 없는 행에 생긴 NULL이 조건을 통과하지 않아 의도한 외부 조인이 무너질 수 있다. WHERE o.order_id IS NULL은 연결된 주문이 없는 고객을 찾는 별도 패턴이다. 결합 키에 중복이 있으면 결과 행이 늘어나므로 “중복이 생겼다”고 바로 DISTINCT를 붙이기보다 각 테이블의 키와 관계가 1:1, 1:N, N:M 중 무엇인지 확인한다.

원문의 암시적 조인(FROM A, B WHERE ...)은 조건을 빠뜨리면 예상치 못한 교차 곱이 되기 쉽다. 현대 SQL에서는 JOIN ... ON으로 관계를 명시하는 편이 읽기와 검토에 좋다. CROSS JOIN은 모든 조합이 필요한 경우에만 쓴다. MySQL에서 FULL OUTER JOIN을 흉내 낸 원문의 LEFT JOIN UNION RIGHT JOIN은 UNION이 중복 행을 제거한다는 점까지 검토해야 하므로 모든 데이터에서 무조건 동일한 대체라고 단정할 수 없다.

느린 조인을 확인할 때

먼저 WHERE로 얼마나 많은 행을 읽는지와 조인 키의 자료형·인덱스를 본다. EXPLAIN으로 실행 계획을 확인하되, “인덱스를 썼다”는 사실만으로 충분하지 않다. 조인 전에 넓은 범위를 읽거나 한쪽 행마다 다른 테이블을 여러 번 탐색하면 여전히 느릴 수 있다. 필요한 결과 컬럼만 고르고, 결합 조건과 필터 조건이 정확한지 검증한 뒤 인덱스 순서나 쿼리 형태를 바꿔 실제 데이터에서 비교한다.

참고: PostgreSQL 조인 테이블, MySQL JOIN 절.