✅ JOIN
: SQL 에서 JOIN 이란 두 개 이상의 table 들에 있는 데이터를 한 번에 조회하는 것 입니다.
: 여러 종류의 JOIN 이 존재합니다.
✅ Implicit JOIN vs Explicit JOIN
먼저, implicit join 과 explicit join 에 대해 알아봅시다.
아래와 같은 statement가 있을 때, 쿼리는 아래와 같습니다.
id 가 1 인 임직원이 속한 부서 이름은?
SELECT D.name
FROM EMPLOYEE AS E, DEPARTMENT AS D
WHERE E.id = 1 AND E.dept_id = D.id;
SELECT 문에 대한 포스팅에서도 JOIN 이 동작하는 것을 다뤘는데요, 이렇게 WHERE 절에 join_condition이 같이 있는 것을
"implicit join" 이라고 합니다. FROM 절에는 테이블들만 나열하고 WHERE 절에 join_condition을 명시하는 방식인 것이죠.
하지만, implicit join 은 조금 old-style 구문입니다.
WHERE 절에 selection_condition과 join_condition 이 같이 있기 때문에 가독성이 떨어져 보입니다.
지금은 간단한 쿼리이지만, 복잡한 JOIN 쿼리를 작성하다 보면, 실수로 잘못된 쿼리를 작성할 가능성도 큽니다.
그래서, 이후의 SQL 표준에서는 JOIN을 명시할 수 있는 문법이 추가가 됩니다.
그런 JOIN을 바로 "explicit join" 이라고 합니다. 위의 똑같은 쿼리를 explicit join 으로 표현하면, 아래와 같습니다.
SELECT D.name
FROM EMPLOYEE AS E JOIN DEPARTMENT AS D ON E.dept_id = D.id
WHERE E.id = 1;
이번에는 FROM 절에 JOIN ON이라는 키워드를 사용해서 직접 명시를 하고, join_condition 도 WHERE 절이 아닌 FROM 절에다가 썼습니다.
이처럼, explicit join 은 FROM 절에 JOIN 키워드와 함께 joined table들을 명시하는 방식입니다.
FROM 절에서 ON 뒤에 join_condition이 오기 때문에 가독성이 훨씬 좋아서 복잡한 JOIN 쿼리 작성 중에도 실수할 가능성이 적습니다.
✅ Inner JOIN vs Outer JOIN
이제는, inner join 과 outer join 에 대해 알아봅시다.
아래와 같은 쿼리가 있다고 해봅시다.
SELECT *
FROM EMPLOYEE E JOIN DEPARTMENT D ON E.dept_id = D.id;
사실 여기서 JOIN은 (INNER) JOIN 과 같습니다. 즉, 쿼리를 작성할 때, JOIN 이라고 그냥 쓰면 디폴트가 INNER JOIN 이라는 말이죠.
EMPLOYEE 테이블을 살펴보면, 'JULIA' 는 아직 부서를 배정받지 못하거나 누락이 돼서 dept_id 가 NULL 인 것을 확인할 수 있습니다.
DEPARTMENT 테이블을 살펴보면, 1006 이라는 id 값을 가진 'legal' 법률 부서의 leader_id 가 NULL 인 것을 확인할 수 있습니다. 즉, EMPLOYEE 테이블에서의 어떤 직원도 법무팀에는 속해있지 않습니다.
JOIN 구문을 다시 보면, EMPLOYEE 테이블에 DEPARTMENT 테이블을 INNER JOIN 할 것인데, join_condition 이 E.dept_id 와 D.id 가 같은 경우를 만족하는 튜플들에 대해서만 그 튜플들을 조합해서 가져오겠다는 뜻입니다.
그러면, 지금은 직원'JULIA' 와 'legal'부서는 연결할 수 없기 때문에 JOIN을 한 테이블 결과에는 해당 튜플이 누락됩니다.
왼쪽에는 EMPLOYEE 테이블에 대한 튜플이, 오른쪽에는 DEPARTMENT 테이블에 대한 튜플이 합쳐져서 하나의 테이블로 반환을 해주는데, 이 때 D.dept_id = E.id 인 튜플들만 조합해서 가져온 셈입니다.
이처럼, INNER JOIN은 두 테이블에서 join_condition을 만족하는 튜플들로 결과 테이블을 만드는 JOIN 입니다.
구문은 FROM table1 (INNER) JOIN table2 ON join_condition 이고, join_condition 에는 여러 비교 연산자들이 올 수 있습니다.
join_condition에서 NULL 값을 가지는 tuple 들은 결과 테이블에 포함되지 못한다는 포인트를 꼭 기억하시길 바랍니다.
(※ 이렇게 되는 이유는 7장의 NULL 파트를 다시 보면 됩니다.)
그럼, OUTER JOIN 은 무엇인지 바로 살펴보겠습니다.
OUTER JOIN은 두 테이블에서 join_condition을 만족하지 않는 튜플들도 결과 테이블에 포함하는 JOIN 입니다.
구문은
FROM table1 LEFT (OUTER) JOIN table2 ON join_condition
FROM table1 RIGHT (OUTER) JOIN table2 ON join_condition
FROM table1 FULL (OUTER) JOIN table2 ON join_condition
이고, join_condition에는 역시 여러 비교 연산자들이 올 수 있습니다.
그럼, OUTER JOIN 중에서 LEFT (OUTER) JOIN 예시를 한 번 살펴보겠습니다.
SELECT *
FROM EMPLOYEE E LEFT JOIN DEPARTMENT D ON E.dept_id = D.id;
아까 INNER JOIN 과 바뀐 부분은 JOIN을 LEFT JOIN 으로 바꾼 것 밖에 없습니다.
결과 테이블이 어떻게 되는지 살펴보면, 아래와 같습니다.
결과 테이블을 보면, 왼쪽에는 EMPLOYEE 테이블이, 오른쪽에는 DEPARTMENT 테이블이 합쳐져서 반환됐는데, 이번에는 'JULIA' 에 대한 정보가 포함이 된 것을 확인할 수 있습니다.
LEFT (OUTER) JOIN 이기 때문에 여기서 LEFT는 왼쪽테이블 즉, EMPLOYEE 테이블을 의미하는 것입니다.
# 왼쪽 테이블 = EMPLOYEE, 오른쪽 테이블 = DEPARTMENT => EMPLOYEE 테이블 기준으로 붙임.
FROM EMPLOYEE E LEFT JOIN DEPARTMENT D ON E.dept_id = D.id;
# 왼쪽 테이블 = DEPARTMENT, 오른쪽 테이블 = EMPLOYEE => DEPARTMENT 테이블 기준으로 붙임.
FROM DEPARTMENT D LEFT JOIN EMPLOYEE E ON E.dept_id = D.id;
즉, OUTER JOIN 에서는 join_condition인 E.dept_id = D.id 를 만족하지 않는 튜플들도 함께 결과테이블에 포함시켜야 하기 때문에, 'JULIA' 가 포함이 된 것입니다.
이 때, 'JULIA'에 대한 행을 보면, EMPLOYEE 테이블에서 가져온 'JULIA' 에 대한 정보는 그대로 가져오는데, DEPARTMENT 테이블에서 가져온 속성에 대한 값은 다 NULL 로 채워지게 됩니다.
여기서 'legal'부서는 결과테이블에 포함되어 있지 않은데, 왜냐하면 LEFT OUTER JOIN 이기 때문에 EMPLOYEE 테이블에 대해서만 그 어떤 튜플도 누락하지 않고 표현을 해주기 때문입니다.
그럼, RIGHT OUTER JOIN 예시도 한 번 살펴봅시다.
SELECT *
FROM EMPLOYEE E RIGHT JOIN DEPARTMENT D ON E.dept_id = D.id;
RIGHT (OUTER) JOIN 에서는 JOIN을 기준으로 오른쪽에 있는 테이블에서 매칭되지 않는 모든 튜플들까지도 함께 반환해줍니다.
결과 테이블을 보면, 이번에는 'JULIA' 에 대한 행이 없고, 대신 'legal'부서에 대한 행이 생겼습니다.
RIGHT JOIN 에서는 오른쪽 테이블인 DEPARTMENT 테이블을 기준으로 누락되는 행 없이 결과테이블에 반환하기 때문입니다.
# 오른쪽 테이블 = DEPARTMENT, 왼쪽 테이블 = EMPLOYEE => DEPARTMENT 테이블 기준으로 붙임.
FROM EMPLOYEE E RIGHT JOIN DEPARTMENT D ON E.dept_id = D.id;
# 오른쪽 테이블 = EMPLOYEE, 왼쪽 테이블 = DEPARTMENT => EMPLOYEE 테이블 기준으로 붙임.
FROM DEPARTMENT D RIGHT JOIN EMPLOYEE E ON E.dept_id = D.id;
지금은 아까 LEFT JOIN과는 반대로 'legal'에 대한 행을 보면, DEPARTMENT 테이블에서의 정보는 그대로 가져오는데, EMPLOYEE 테이블에서 가져온 속성에 대한 값은 다 NULL 로 채워지게 됩니다.
그럼, 마지막으로 FULL OUTER JOIN 예시를 살펴보겠습니다.
(※ PostgreSQL 에서는 FULL OUTER JOIN을 지원하지만, MySQL에서는 FULL OUTER JOIN을 지원하지 않습니다.)
(PostgreSQL 에서 FULL OUTER JOIN)
SELECT *
FROM EMPLOYEE E FULL OUTER JOIN DEPARTMENT D ON E.dept_id = D.id;
(MySQL 에서는 UNION 키워드를 사용해서 LEFT JOIN 과 RIGHT JOIN 을 합치는 형태로 사용합니다.) = FULL OUTER JOIN
SELECT *
FROM EMPLOYEE E LEFT JOIN DEPARTMENT D
ON E.dept_id = D.id
union
SELECT *
FROM EMPLOYEE E RIGHT JOIN DEPARTMENT D
ON E.dept_id = D.id;
FULL OUTER JOIN 은 EMPLOYEE 테이블과 DEPARTMENT 테이블 모두에서 join_condition에서 매칭되지 않는 튜플들까지 모두 결과테이블에 포함시킵니다. 따라서, 'JULIA' 와 'legal' 부서에 대한 행이 모두 포함되는 것이죠.
✅ EQUI JOIN
EQUI JOIN은 join_condition 에서 = (등호), 즉 equality 연산자를 사용하는 JOIN 형태입니다.
앞서, 예시로 들었던 JOIN 문을 봐봅시다.
SELECT *
FROM EMPLOYEE E JOIN DEPARTMENT D ON E.dept_id = D.id;
SELECT *
FROM EMPLOYEE E LEFT JOIN DEPARTMENT D ON E.dept_id = D.id;
SELECT *
FROM EMPLOYEE E RIGHT JOIN DEPARTMENT D ON E.dept_id = D.id;
SELECT *
FROM EMPLOYEE E FULL OUTER JOIN DEPARTMENT D ON E.dept_id = D.id;
해당 JOIN 문의 join_condition 에서 다 =(등호)를 사용하였기 때문에, 4가지 형태 모두 EQUI JOIN 이라고 할 수 있습니다.
※ 그런데, 이 EQUI JOIN 에 대해서는 두 가지 시각이 있습니다.
1) INNER JOIN 이든 OUTER JOIN 이든 상관없이 =(등호)를 사용한 JOIN 이라면, EQUI JOIN 으로 보는 경우
2) INNER JOIN으로 한정해서 =(등호)를 사용한 경우에 EQUI JOIN으로 보는 경우
✅ USING
USING은 두 테이블이 EQUI JOIN 을 할 때, join 하는 attribute 의 이름이 같다면, USING 으로 간단하게 작성할 수 있다.
같은 이름의 attribute는 결과 테이블에서 한 번만 표시가 됩니다.
FROM EMPLOYEE E (INNER)JOIN DEPARTMENT D ON E.dept_id = D.id;
<-->
FROM EMPLOYEE E (INNER)JOIN DEPARTMENT D USING(dept_id);
FROM EMPLOYEE E LEFT JOIN DEPARTMENT D ON E.dept_id = D.id;
<-->
FROM EMPLOYEE E LEFT JOIN DEPARTMENT D USING(dept_id);
FROM EMPLOYEE E RIGHT JOIN DEPARTMENT D ON E.dept_id = D.id;
<-->
FROM EMPLOYEE E RIGHT JOIN DEPARTMENT D USING(dept_id);
FROM EMPLOYEE E FULL OUTER JOIN DEPARTMENT D ON E.dept_id = D.id;
<-->
FROM EMPLOYEE E FULL OUTER JOIN DEPARTMENT D USING(dept_id);
JOIN 형태가 어찌 됐든 EQUI JOIN에서 JOIN 하는 attribute 이름이 같다면, USING 을 사용할 수 있습니다.
예를 들어서 아까 (INNER) JOIN을 예시로 들었던 구문을 가져와보겠습니다.
SELECT *
FROM EMPLOYEE E JOIN DEPARTMENT D ON E.dept_id = D.id;
여기서는 EMPLOYEE 테이블의 dept_id 와 DEPARTMENT 테이블의 id 값이 같은 속성이지만, 이름이 다르기 때문에 EQUI JOIN을 통해 직접 명시를 해줬고, 결과도 두 번 중복해서 나왔습니다.
하지만, 만약 EMPLOYEE 테이블과 DEPARTMENT 테이블이 아래와 같다면 어떨까요?
DEPARTMENT 테이블에서의 'id' 컬럼이 'dept_id' 로 이름이 바뀌었다고 해봅시다. 그럼, dept_id 를 USING 을 활용하여 결과테이블을 더 간단하게 만들 수 있습니다.
현재 EMPLOYEE 테이블의 dept_id 와 DEPARTMENT 테이블의 dept_id 속성 이름이 같기 때문에, 이를 USING 으로 묶어서 JOIN 쿼리를 작성해본다면, 아래와 같습니다.
SELECT *
FROM EMPLOYEE E JOIN DEPARTMENT D USING(dept_id);
결과테이블에는 중복된 속성 dept_id 가 하나의 dept_id 컬럼으로 표시되었고, USING 으로 묶은 dept_id 값이 맨 앞으로 빠져나왔습니다. 이 때, 중복하는 이름을 가지는 attribute가 2개 이상이라면, USING() 안에 그 속성들을 나열하면 됩니다.
✅ Natural JOIN
Natural JOIN은 두 테이블에서 같은 이름을 가진 "모든" attribute pair 에 대해서, EQUI JOIN을 수행하는 것입니다.
그래서, join_condition은 따로 명시하지 않고, JOIN 의 형태가 어떻든 앞에 NATURAL 키워드를 붙여서 사용하면 됩니다.
FROM table1 NATURAL (INNER)JOIN table2;
FROM table1 NATURAL LEFT(OUTER)JOIN table2;
FROM table1 NATURAL RIGHT(OUTER)JOIN table2;
FROM table1 NATURAL FULL(OUTER)JOIN table2;
그럼, 이번에도 아까 (INNER) JOIN을 예시로 들었던 구문을 가져와보겠습니다.
SELECT *
FROM EMPLOYEE E JOIN DEPARTMENT D ON E.dept_id = D.id;
현재,만약 EMPLOYEE 테이블과 DEPARTMENT 테이블이 아래와 같다면 어떨까요?
DEPARTMENT 테이블에서의 'name' 컬럼의 이름이 'dept_name' 로 바뀌었다고 해봅시다. 그럼, 현재 이름이 겹치는 컬럼은 'dept_id' 밖에 없게 됩니다. 그럼 이제, NATURAL 키워드를 사용해서 어떤 결과가 나타나는지 확인해봅시다.
SELECT *
FROM EMPLOYEE E NATURAL JOIN DEPARTMENT D;
결과테이블은 USING 을 활용한 결과테이블과 동일한 결과가 나왔습니다.
현재 EMPLOYEE 테이블의 dept_id 와 DEPARTMENT 테이블의 dept_id 속성 이름이 같기 때문에, 이를 USING 으로 묶어서 JOIN 쿼리를 작성하든 NATURAL JOIN을 하든, 공통된 컬럼을 맨 앞으로 빼고 조인의 형태에 맞는 튜플들의 조합을 반환해준 것입니다.
하지만, 만약 DEPARTMENT 테이블의 컬럼이름을 dept_name에서 다시 name 으로 바꿔준다면 어떻게 될까요?
이 상태에서 다시 NATURAL JOIN을 수행하면, 결과는 아래와 같습니다.
아무런 튜플도 반환하지 않습니다. 그 이유에 대해서 살펴봅시다.
우선, NATURAL JOIN 에서는 조인하는 테이블 사이에 같은 이름인 attribute를 찾아야 합니다.
여기서는 (name, dept_id) 가 해당하겠네요. 그럼 NATRUAL JOIN은 어떻게 동작을 할까요?
SELECT * FROM EMPLOYEE E NATURAL JOIN DEPARTMENT D;
위 NATURAL JOIN을 USING 키워드를 사용한 구문으로 똑같이 바꿔본다면, 다음과 같이 나타낼 수 있습니다.
SELECT * FROM EMPLOYEE E JOIN DEPARTMENT D USING(name,dept_id);
그럼, 이 구문을 또 ON 을 사용해서 join_condition에 EQUI JOIN 으로 표현한다면, 다음과 같이 나타낼 수 있습니다.
SELECT * FROM EMPLOYEE E JOIN DEPARTMENT D
ON E.name = D.name AND E.dept_id = D.dept_id;
그러니까, 현재 테이블 상태에서 NATURAL JOIN을 해버리면, 두 테이블에서 dept_id 가 동일하고, name 도 동일한 join_condition 으로 두 테이블을 JOIN 하라는 의미가 돼버립니다.
따라서, EMPLOYEE 테이블에서 직원의 이름과 DEPARTMENT 테이블에서 부서 이름이 같은 경우는 없기 때문에, empty set 을 반환한 것입니다.
✅ Cross JOIN
Cross JOIN은 두 테이블의 tuple pair 로 만들 수 있는 모든 조합(=카테시안 곱)을 결과 테이블로 반환합니다.
따라서, join_condtion은 따로 없습니다.
implicit cross join 은 아래와 같이 수행할 수 있고,
FROM table1,table2
explicit cross join은 아래와 같이 수행합니다.
FROM table1 CROSS JOIN table2
그럼, EMPLOYEE 테이블과 DEPARTMENT 테이블을 explicit 한 방법으로 CROSS JOIN 해봅시다.
SELECT *
FROM EMPLOYEE CROSS JOIN DEPARTMENT;
총 90개의 튜플이 나왔습니다. 현재 EMPLOYEE 테이블과 DEPARTMENT 테이블은 아래와 같습니다.
즉, EMPLOYEE 테이블을 기준으로 각 행마다 DEPARTMENT의 모든 행과의 조합을 다 반환하는 것입니다.
EMPLOYEE 테이블의 15개의 행 x DEPARTMENT 테이블의 6개의 행 = 90개의 행 이 나온 셈이죠.
(※ MySQL 에서는 CROSS JOIN = INNER JOIN = JOIN 이다. 기존 SQL 표준과는 다릅니다.)
(※ CROSS JOIN 에 ON(or USING)을 같이 쓰면, INNER JOIN 으로 동작합니다. <=> INNER JOIN(=JOIN) 이 ON(or USING) 없이 사용되면 CROSS JOIN 으로 동작한다는 말입니다.)
✅ Self JOIN
Self JOIN은 테이블이 자기 자신에게 JOIN 하는 경우를 말합니다. 즉, JOIN을 할 때, 자기자신에게도 JOIN을 걸 수 있다는 뜻이죠.
✅ JOIN 예제
ID 가 1003인 부서에 속하는 임직원 중 리더를 제외한 부서원의 ID, 이름, 연봉을 알고 싶다.
우선, EMPLOYEE 에 대한 정보를 뽑아야 하니까 EMPLOYEE 테이블이 필요한 것은 확실해보입니다.
또, 부서에 대한 리더정보는 EMPLOYEE 테이블이 아닌 DEPARTMENT 테이블에 있기 때문에, 이를 활용하면 될 듯합니다.
쿼리는 아래와 같습니다.
SELECT E.id, E.name, E.salary
FROM EMPLOYEE E JOIN DEPARTMENT D ON E.dept_id = D.dept_id
WHERE E.id != D.leader_id AND E.dept_id = 1003;
OR
SELECT E.id, E.name, E.salary
FROM EMPLOYEE E JOIN DEPARTMENT D ON E.dept_id = D.dept_id
WHERE E.id != D.leader_id AND D.dept_id = 1003;
또 다른 예제를 살펴보겠습니다.
ID 가 2001인 프로젝트에 참여한 임직원들의 이름과 직군과 소속부서 이름을 알고 싶다.
우선, EMPLOYEE 에 대한 정보를 뽑아야 하니까 EMPLOYEE 테이블이 필요한 것은 확실해보입니다.
또, 부서에 대한 이름정보는 DEPARTMENT 테이블에 있기 때문에, 이를 활용하면 될 듯합니다.
또, 어떤 프로젝트에 어떤 임직원이 참여했는지에 대한 정보는 WORKS_ON 테이블을 활용하면 될 듯합니다.
쿼리는 아래와 같습니다.
SELECT E.name, E.position, D.name
FROM EMPLOYEE E JOIN WORKS_ON W ON E.id = W.empl_id
LEFT JOIN DEPARTMENT D ON E.dept_id = D.dept_id
WHERE W.proj_id = 2001;
OR
SELECT E.name, E.position, D.name
FROM WORKS_ON W JOIN EMPLOYEE E ON E.id = W.empl_id
LEFT JOIN DEPARTMENT D ON E.dept_id = D.dept_id
WHERE W.proj_id = 2001;
여기서, DEPARTMENT 테이블과는 LEFT JOIN 을 해줘야 하는데, 혹시 EMPLOYEE 테이블의 dept_id 가 null 인 경우가 있을 수 있으므로(현재는 'Julia'가 2001 프로젝트에 참여하고 있음) 아래와 같이 작성하면 해당 직원은 누라될 여지가 있기 때문입니다.
SELECT E.name, E.position, D.name
FROM EMPLOYEE E JOIN WORKS_ON W ON E.id = W.empl_id
JOIN DEPARTMENT D ON E.dept_id = D.dept_id
WHERE W.proj_id = 2001;
참고자료
- 유튜브 쉬운코드