이번 글에서는 여러 테이블을 하나의 테이블처럼 만들어서 사용할 수 있게 해주는 JOIN에 관해 정리해 보겠습니다.
표준 방식의 JOIN 방법을 보고싶으신 분은 6번으로 가시면 됩니다.
해당 글에서 사용하는 테이블의 종류는 emp, detp, salgrade 테이블입니다.
0. 두 테이블을 어떻게 하나로 만들지?
두개의 테이블을 하나로 만들려면 어떻게 해야할까요?
그냥 무작정 옆으로 갖다 붙이면 안됩니다.
반드시 공통의 컬럼이 존재해야 합니다.
이 말을 조심해서 이해하셔야 하는게, 같은 이름의 컬럼이 아닌, 값의 범위가 일치하는 공통의 컬럼입니다.
emp 테이블과 dept 테이블을 예시로 보겠습니다.
dept테이블은 부서 번호, 부서 명, 부서 위치가 있고
emp테이블은 사원번호, 사원이름, 직책, 사수, 입사날짜, 급여, 추가수당, 부서 번호 가 있습니다.
두 테이블의 공통된 컬럼이 무엇인지 보이시나요?
바로 DEPTNO 컬럼 이죠.
emp 테이블에는 부서 번호가 10~30까지 있고
dept 테이블에는 부서 번호가 10~40까지 있습니다.
범위가 dept 테이블이 더 크죠. 따라서 dept 테이블이 부모, emp 테이블이 자식 관계라고 생각하시면 됩니다.
이제 본격적으로 JOIN 방법을 하나하나 알아보겠습니다.
1. 이전 JOIN 방식 - 등가 조인(Equi Join)
먼저 표준 JOIN 방식을 보기 전, 이전 JOIN 방식을 알아보겠습니다.
처음 알아볼 것은 등가 조인입니다.
공통된 컬럼이 있는데 그것을 '='으로 묶는 방식입니다.
또한, JOIN 방식은 'FROM 절과 WHERE 절을 사용'합니다.
JOIN을 하려면 JOIN에 대한 조건이 필요한데, 이 조건을 WHERE절에서 사용합니다.
SELECT ename, job, sal, hiredate, dname, loc
FROM emp, dept
WHERE emp.deptno = dept.deptno;
emp 테이블과 dept 테이블은 공통된 컬럼이 부서 번호인, deptno 컬럼이였죠.
'deptno 컬럼에 대해서 두 테이블을 연결시켜줘' 라고 명령을 하는데, 공통된 이름이니 각 deptno가 어떤 테이블의 컬럼인지 명시해줘야겠죠.
따라서 꼭 emp.deptno = dept.deptno 라고, 각 컬럼이 어떤 테이블의 컬럼인지 알려줘야 합니다.
위 결과를 보시면, emp 테이블에 없던 loc 컬럼이 생겼고(dept테이블에 있던 loc컬럼)
dept 테이블에 없던 ename, job, sal, hiredate, dname 컬럼이 하나의 테이블에 존재하죠.
그런데 아래와 같은 경우는 오류입니다.
SELECT ename, job, sal, deptno, hiredate, dname, loc
FROM emp, dept
WHERE emp.deptno = dept.deptno;
SELECT 절을 보시면 deptno 컬럼도 보여달라고 명시했습니다.
이대로 실행해 볼까요?
오류 내용을 읽어보면, 'dept 컬럼이 뭔지 좀 모호한데?' 라고 합니다.
해당 컬럼은 emp, dept 테이블 둘 다 있었죠.
따라서 저렇게 공통된 컬럼을 표시하고 싶을 땐, 반드시 어느 쪽 컬럼인지를 명시해줘야 합니다.
SELECT ename, job, sal, emp.deptno, hiredate, dname, loc
FROM emp, dept
WHERE emp.deptno = dept.deptno;
위와 같이 말이죠.
또한 아래와 같이 Alias(별칭)을 이용해 표시할 수 있습니다.
필수는 아닙니다.
SELECT ename, job, sal, e.deptno, hiredate, dname, loc
FROM emp e, dept d
WHERE e.deptno = d.deptno;
결과는 같습니다.
2. 이전 JOIN 방식 - 비등가 조인(Non-equi Join)
먼저, emp 테이블과 salgrade 테이블을 보시겠습니다.
salgrade 테이블은 등급, 최저급여, 최고급여 컬럼이 있습니다.
혹시 뭔가 감이 오는것 같지 않나요?
emp 테이블에는 급여 컬럼 sal 컬럼이 있죠.
salgrade는 각 급여의 범위를 표현하고 해당 범위에 있는 급여의 등급을 나타내는 테이블입니다.
따라서 JOIN 될 수 있습니다.
하지만, 같은 값이 없기 때문에 '='연산으로 JOIN 될 수 없습니다.
따라서 이럴 땐, BETWEEN A AND B 키워드를 사용합니다.
영어 뜻과 같이 'A와 B 사이' 라는 뜻 입니다.
SELECT ename, job, sal, hiredate, s.grade
FROM emp e, salgrade s
WHERE sal BETWEEN s.losal AND s.hisal;
SELECT 절에 grade라는 컬럼이 들어갔습니다. 이는 salgrade에 있는 컬럼 명이죠.
그리고 두 테이블을 WHERE절에서 보시는것과 같이 BETWEEN A AND B 로 지정해줬습니다.
결과를 보시면
각 사원의 급여 범위에 따라 마지막 컬럼에 등급이 잘 나타난 것을 볼 수 있습니다.
하지만 salgrade의 losal과 hisal의 범위가 등급에 따라 중복되는 구간이 있으면 JOIN 될 수 없습니다.
하나의 sal 컬럼이 여러 등급에 속하게 되기 때문입니다.
3. 자체 조인 - Self Join (등가 조인 사용)
하나의 테이블을 여러 테이블처럼 사용하는 방식입니다.
따라서 이 때는 등가조인을 사용합니다.
하나의 테이블 안에 공통의 컬럼이 존재할 때 사용합니다.
emp 테이블을 볼까요?
여기서 empno와 mgr 컬럼을 봐주세요.
empno는 사원 번호이며 mgr은 사수의 컬럼이죠.
또한, 각 사수는 emp테이블 내에 존재하는 어느 한명의 사원입니다.
따라서 둘이 공통된 컬럼입니다.
공통된 컬럼인건 알겠는데... 등가 조인으로 어떻게 표현을 할까요?
둘다 같은 테이블인데 emp = emp로 표현할 수 없습니다.
먼저 이 관계를 봐주세요
한 명의 직원은 한 명의 관리자만 갖는다.
한 명의 관리자는 여러 명의 직원을 갖는다.
위 말을 풀어서 설명하면 이렇습니다.
한 명의 직원은 여러 명의 관리자가 아닌 딱 한 명의 관리자가 붙는다.
그런데 한 명의 관리자는 여러 명의 직원을 담당합니다.
다시한번 emp 테이블을 보겠습니다.
mgr 컬럼을 보면, 공통된 사원 번호가 있습니다.
한 명의 사원은 한 명의 사수가 붙지만,
사수는 여러명의 사원을 담당하고 있죠.
1 : Many 관계를 나타냅니다.
따라서 이 때는 반드시 Alias(별칭)d을 사용해야 합니다.
예시를 보겠습니다.
사원 번호와 사원 이름을 출력하는데, 그 옆에 담당하는 사수의 번호와 사수의 이름을 이어 붙이고 싶습니다.
empno(사원 번호), ename(사원 이름), empno(담당 사수의 사원 번호), ename(담당 사수의 이름)
위와 같이 말이죠.
따라서 아래와 같이 표현할 수 있습니다.
SELECT w.empno, w.ename, m.empno, m.ename
FROM emp m, emp w
WHERE m.empno = w.mgr;
여기서 w는 Worker를 뜻하고, m은 Manager를 뜻합니다.
FROM 절에 각각 테이블이 어떤것을 말하는지 alias를 통해 지정하였고
WHERE 절을 통해서 empno와 mgr를 엮어달라고 요청하였습니다.
Manager인 m은 empno(사원 번호) 컬럼을 참조하죠.
Worker인 w은 mgr(사수) 컬럼을 참조합니다.
따라서 위와 같이 명령해야 제대로 된 결과값을 얻을 수 있습니다.
4. 외부 조인 - Outer Join
JOIN의 특성은 이렇습니다.
JOIN 조건을 만족하지 못한 정보(행 - row)는
JOIN 되지 못한다.
먼저 아래와 같이 입력해서 KIM 이란 사원의 정보를 emp 테이블에 추가해 봅시다.
INSERT into emp (empno, ename, sal, hiredate, job) VALUES (9999, 'KIM', 500, sysdate, 'CLERK');
나중에 예시가 다 끝나면 아래처럼 입력해서 정보를 삭제해 주시면 됩니다.
DELETE FROM emp WHERE empno = 9999;
추가 한 후 emp 테이블을 보겠습니다.
행이 총 14개에서 KIM 사원이 추가되어 15개가 된 것을 볼 수 있습니다.
정보를 보면, 부서 정보가 없는것을 볼 수 있습니다.
따라서 이대로 JOIN을 하면 KIM 사원은 JOIN에 참여될 수 없어서 해당 사원에 대한 정보를 확인할 수 없습니다.
하지만 이럴 때 아래와 같이 사용하면 JOIN이 됩니다.
SELECT empno, ename, sal, hiredate, job, d.deptno, d.dname, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno(+);
그림이 커서 그런지 여러번 캡쳐해도 좀 깨지네요.. 양해 바랍니다 ㅠ
위 결과에서 보시면 JOIN을 했는데, 연관되는것이 없음에도 마지막에 JOIN되어 테이블에 정보가 표시된것을 볼 수 있습니다.
그리고 위 쿼리를 보시면 d.deptno(+) 같이 뒤에 "(+)" 기호를 붙였습니다.
이는 WHERE절 JOIN의 조건 기준 '왼쪽에 빈 행을 하나 더 추가해라' 라는 뜻입니다.
그래서 KIM 사원의 정보가 표시될 수 있는 것이죠.
"왼쪽에 빈 행을 추가하는데 오른쪽에 (+)를 추가한 것을 명심하세요"
그리고 이를 Left Outer Join 이라고 합니다.
그러면 반대는 당연히 Right Outer Join 이 되겠죠.
SELECT empno, ename, sal, hiredate, job, d.deptno, d.dname, d.loc
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno;
마지막 행을 보시면 오른쪽에 '부서 번호 40, 부서 이름 OPERATIONS, 부서 위치 BOSTON' 정보가 추가된 것을 볼 수 있고 왼쪽에는 아무런 정보도 JOIN 되어있지 않은것을 볼 수 있습니다.
그러면 양쪽 다 추가는 어떻게 할까요?
이는 아래에서 설명 할 표준 JOIN 방식에서만 지원합니다.
잠깐 기다려주세요 :)
5. 멀티 테이블 JOIN
조인의 또 다른 특성이 있습니다.
JOIN은 항상 두개의 테이블만 할 수 있다.
즉, 한번에 3개 이상 연결을 못한다는 겁니다.
그런데 우리가 사칙 연산을 할 때, 계산을 한번에 하나요?
예를들어 1+2+3 이란 연산이 있을 때, 1+2를 먼저하고 나온 결과를 3과 더하죠.
멀티 테이블 JOIN 도 같은 방식입니다.
먼저 두개의 테이블을 JOIN 해서 하나의 테이블로 만든 후, 다른 테이블과 합칩니다.
사원의 이름, 업무, 급여, 부서명, 근무위치, 급여의 등급(salgrade)을 출력하는 쿼리를 작성하세요.
위 문제를 보시면 emp 테이블에 있는 '사원의 이름, 업무, 급여, 부서명'
dept 테이블에 있는 '부서명, 근무 위치'
salgrade 테이블에 있는 '급여의 등급'
세 개의 테이블이 있습니다.
그러면 먼저 1차적으로 emp테이블과 dept 테이블을 엮어볼까요?
위에서 했던거죠?
SELECT e.ename, e.job, e.sal, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno;
잘 엮어진 것을 볼 수 있습니다.
그러면 여기서 salgrade를 더 붙여보겠습니다.
salgrade 테이블은 BETWEEN ... AND ... 를 썼죠.
SELECT e.ename, e.job, e.sal, d.loc, s.grade
FROM emp e, dept d, salgrade s
WHERE e.deptno = d.deptno and sal BETWEEN s.losal AND s.hisal;
위와 같이 WHERE 절에 and 연산자를 써서 추가 JOIN 조건을 넣은 것을 볼 수 있습니다.
테이블도 각 급여에 맞게 등급이 잘 나왔네요.
만약 WHERE 절이 없다면 어떻게 될까요?
SELECT empno, ename, emp.deptno, dept.deptno, dname
FROM emp, dept
ORDER BY ename, dept.deptno;
위와 같이 WHERE절이 없고 그냥 정렬만 시켰습니다.
그럼 아래와 같은 무시무시한 결과가 나옵니다.
밑에 KIM 사원이 있네요 ㅎㅎ 깜빡하고 안지웠나봅니다.
잘 보시면 ADAMS 사원에 대해 사원번호 10, 20, 30, 40에 각 부서에 해당하는 부서 이름...
ALLEN 사원도 10, 20, 30, 40... 똑같이 쭈욱 출력이 됩니다.
KIM 사원을 지우셨다면(혹은 처음부터 없었다면), emp테이블 14개 X dept 테이블 4개 해서 총 56개의 행이 나왔을 겁니다.
왜 이런 결과가 나온 것일까요?
이유는 이렇습니다.
JOIN 조건이 없다는 것은, 어떤 방식으로 JOIN을 수행할 것인지 지정 하는게 없다는 뜻이고
메모리상에 존재하는 DBMS는 JOIN에 참여하는 두 테이블이 어떤 방식으로 JOIN되는지 모른다는 뜻이다.
그래서 '연결 가능한 모든 경우의 수를 다 연결해 버린다.'
사용하는 경우가 있긴 합니다만, 일반적인 업무에서는 사용하지 않습니다.
6. 표준 JOIN 방식
표준 JOIN 방식은 이전 JOIN 방식과는 다르게 FROM 절만 사용합니다.
FROM 절에, JOIN에 참여하는 JOIN의 종류를 명시해야 합니다.
조인 방식은 4가지가 있습니다.
1) NATURAL JOIN
2) INNER JOIN
3) OUTER JOIN
4) CROSS JOIN
1) NATURAL JOIN
NATURAL JOIN 방식은 같은 이름의 컬럼을 자동으로 찾아서 연결시켜주는 방식입니다.
SELECT ename, job, dname, loc
FROM emp NATURAL JOIN dept;
끝입니다.
이전 JOIN 방식과 비교해볼까요?
SELECT ename, job, dname, loc
FROM emp, dept
WHERE emp.deptno = dept.deptno;
확실히 표준 JOIN 방식이 편해보입니다.
결과입니다.
결과도 똑같이 나오는것을 볼 수 있습니다.
그런데 이 방식은 문제가 있습니다.
NATURAL JOIN 방식은 JOIN에 참여하는 두개의 테이블로부터 같은 이름을 갖는 컬럼을 찾습니다.
그 후 같은 값끼리 연결합니다.
그럼 만약에 ,
'컬럼 명'이 다른데 '공통된 값'이 있다면 어떻게 될까요?
컬럼 명이 다르기 때문에 공통된 값이 있다 해도 JOIN이 되지 않습니다.
따라서 잘 사용되지 않는 방식입니다.
2) INNER JOIN
다음은 INNER JOIN 방식입니다.
이 방식은 너무나도 간단합니다.
먼저, 함께 사용되는 명시 방법을 보겠습니다.
- USING
- ON
먼저 INNER JOIN 과 USING을 볼까요?
emp테이블과 dept 테이블을 하나의 테이블로 만들어보겠습니다.
SELECT ename, job, dname, loc
FROM emp INNER JOIN dept USING(deptno);
결과가 잘 나왔네요.
그러면 USING은 뭘까요?
USING뒤에 보시면 괄호 안에 deptno 라는 컬럼명이 들어갑니다.
즉, 이 또한 같은 컬럼 명을 찾고 공통된 값을 연결시켜주는 NATURAL JOIN 방식과 동일합니다.
그 말은 즉, 문제가 있는 방식이죠.
역시나 잘 쓰이지 않습니다.
그 다음은 INNER JOIN 과 ON 을 보겠습니다.
SELECT ename, job, dname, loc
FROM emp INNER JOIN dept ON emp.deptno = dept.deptno;
위 결과 처럼 ON은 등가 조인(Equi Join) "=" 을 사용하기 때문에 이 방법이 가장 정석적인 방법입니다.
의미도 '조건에 만족하지 않은 행은 JOIN에 참여할 수 없는 방식' 으로 같죠.
INNER JOIN ... ON을 사용하여 이전 JOIN 방식에서 썼었던 것 처럼 비등가 조인(Non-equi Join), 자체 조인(Self Join) 모두 가능합니다.
SELECT ename, job, sal, grade
FROM emp INNER JOIN salgrade ON sal BETWEEN losal AND hisal;
SELECT w.empno, w.ename, m.empno, m.ename
FROM emp w INNER JOIN emp m ON m.empno = w.mgr;
또한 INNER JOIN은 대표적으로 많이 사용되기 때문에 INNER 가 생략이 가능합니다.
SELECT ename, job, dname, loc
FROM emp JOIN dept ON emp.deptno = dept.deptno;
3) OUTER JOIN
OUTER JOIN은 '공통된 값이 없어서 JOIN에 참여할 수 없는 정보'를 추가해주는 외부 조인 방식입니다.
여기서 다시 KIM 사원을 불러볼까요? (위 글에서 추가하고 삭제 안하셨다면 추가하실 필요 없습니다.)
INSERT into emp (empno, ename, sal, hiredate, job) VALUES (9999, 'KIM', 500, sysdate, 'CLERK');
KIM 사원의 데이터 삭제 방법입니다. 나중에 기본 예시로 공부하기 위해서 꼭 삭제해 주세요.
DELETE FROM emp WHERE empno = 9999;
이렇게 두고 emp 테이블을 봅시다.
네 마지막 행에 KIM 사원 정보가 잘 들어갔네요.
KIM 사원은 현재 배정된 사원 번호가 없습니다. 따라서 INNER JOIN을 하면 공통된 값이 없기 때문에 JOIN에 참여할 수 없게 됩니다. 나중에 JOIN된 결과에서 KIM 사원에 대한 정보는 볼 수 없는것이죠.
이전 JOIN 방식에서는 왼쪽 혹은 오른쪽에 행을 추가함으로써 JOIN되지 못했던 한쪽 데이터의 값만 표시할 수 있었습니다.
그러나 표준 JOIN 방식은 양쪽 다 표시가 가능합니다.
쓰는 방식은 INNER JOIN 방식과 비슷합니다.
- LEFT OUTER JOIN ... ON => 왼쪽 외부 조인
- RIGHT OUTER JOIN ... ON => 오른쪽 외부 조인
- FULL OUTER JOIN ... ON => 양쪽(전체) 외부 조인
[LEFT OUTER JOIN]
SELECT ename, job, sal, dname, loc
FROM emp LEFT OUTER JOIN dept ON emp.deptno = dept.deptno;
왼쪽에 행이 추가되면서 KIM 사원에 대한 정보가 들어간 것을 볼 수 있습니다.
[RIGHT OUTER JOIN]
SELECT ename, job, sal, dname, loc
FROM emp RIGHT OUTER JOIN dept ON emp.deptno = dept.deptno;
오른쪽에 행이 추가 되면서 dept 테이블의 부서 이름, 위치의 정보 데이터가 추가된것을 볼 수 있습니다.
[FULL OUTER JOIN]
SELECT ename, job, sal, dname, loc
FROM emp FULL OUTER JOIN dept ON emp.deptno = dept.deptno;
위 결과와 같이 양쪽에 행이 추가 되면서 KIM 사원의 정보와 dept 테이블의 40번 부서에 대한(JOIN에 참여되지 못했던) 부서 이름, 위치가 추가된 것을 볼 수 있습니다.
단, 위 결과와 같이 KIM 사원과 40번 부서에 대한 데이터는 연관성이 없기 때문에 서로 다른 행으로 만들어져서 총 2개의 행이 추가 되었습니다.
4) CROSS JOIN
이 또한 잘 사용하지 않는 방법이긴 합니다.
이유는 WHERE절이 없었을 때와 같기 때문입니다.
즉, JOIN이 없는 방식입니다.
바로 넘어와서 이 방식들부터 보신 분들을 위해 다시 설명드리자면
JOIN 조건이 없다는 것은,
어떤 방식으로 JOIN을 수행할 것인지 지정 하는게 없다는 뜻이고
메모리상에 존재하는 DBMS는 JOIN에 참여하는 두 테이블이 어떤 방식으로 JOIN되는지 모른다는 뜻이다.
그래서 '연결 가능한 모든 경우의 수를 다 연결해 버린다.'
실습하시기 전 KIM 사원 정보를 지우지 않으셨다면 지워주세요.
DELETE FROM emp WHERE empno = 9999;
SELECT ename, job, sal, dname, loc
FROM emp CROSS JOIN dept;
또 어마무시하게 나오죠...
KIM 사원 데이터를 잘 지우셨다면(기본 테이블을 사용하셨다면),
JOIN 조건이 없을 때 처럼 '연결 가능한 모든 경우의 수를 다 연결'해 버려서
emp 테이블 14 X dept테이블 4 해서 총 56개의 행이 나온것을 볼 수 있습니다.
7. 멀티 JOIN을 표준 JOIN 방식으로 해결
이는 적절히 위 표준 방식을 섞어서 사용하시면 됩니다.
위 예시를 가져오겠습니다.
사원의 이름, 업무, 급여, 부서명, 근무위치, 급여의 등급(salgrade) 을 출력하는 쿼리를 작성하세요.
이 문제는 emp 테이블, dept 테이블, salgrade 테이블이 전부 섞여있죠.
위에서 설명드렸다시피
JOIN은 한번에 여러개를 JOIN 하지 못합니다.
즉, 멀티 JOIN 방식은 '먼저 두개의 테이블을 하나의 테이블로 만든 후, 만들어진 테이블을 다른 테이블과 다시 JOIN 하는 방식' 입니다.
그럼 먼저 두개의 테이블을 표준 JOIN 방식으로 해보겠습니다.
SELECT ename, job, sal, dname
FROM emp INNER JOIN dept ON emp.deptno = dept.deptno;
그리고 이제 남은 salgrade를 JOIN 해볼까요?
SELECT ename, job, sal, dname, grade
FROM emp INNER JOIN dept ON emp.deptno = dept.deptno
INNER JOIN salgrade ON sal BETWEEN losal AND hisal;
해당 급여 등급에 맞게 잘 등급까지 JOIN 된것을 볼 수 있습니다.
이처럼 표준 JOIN 방식으로도 충분히 멀티 JOIN을 할 수 있습니다.
여기까지 JOIN에 대한 정리 글이였습니다.
