JOIN은 두 개 이상의 테이블에서 데이터를 검색할 수 있게 해준다.
🐨 JOIN이 없다면?
- 두 번 이상의 질의가 필요함
- 이전 질의에서 나왔던 결과와 현재 질의에서 나온 결과를 한 번에 확인할 수 없음
🐨 JOIN 사용 방법
- 내가 원하는 데이터가 무엇인지 (
SELECT) - 그 데이터가 어떤 테이블에 있는지 (
FROM) - 원하는 테이블 간의 공통되는 컬럼(
FK)이 무엇인지 (직접적으로 공통되는 컬럼이 없다면 여러 테이블을 조인해야한다)
1. 이너 조인
<--Inner Join
SELECT e.employee_id, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND last_name = 'King';
만약 primary key가 2개면 2개 다 같아야한다고 명시해줘야한다.
위의 오라클에서 사용하는 조인 쿼리를 국제 표준인 ANSI JOIN으로 표기하면 아래와 같다.
--ANSI Join
SELECT e.employee_id, e.department_id, d.department_name
FROM employees e INNER JOIN departments d
ON e.department_id = d.department_id
WHERE last_name = 'King';기본적으로 JOIN은 INNER JOIN이기 때문에 INNER JOIN 대신 그냥 JOIN으로 표기해도 무관하다.
--3개 이상의 조인
SELECT c.cname, s.major, s.syear
FROM student s, course c, score sc
WHERE sc.sno = s.sno
AND sc.cno = c.cno
AND major = '화학'
AND syear = 1;
--ANSI JOIN에서의 3개 이상 조인
--두 개의 테이블을 먼저 조인 후 세 번째 테이블을 조인
SELECT c.cname, s.major, s.syear
FROM stduent s
JOIN score sc
ON s.sno = sc.sno
JOIN course c
ON c.cno = sc.cno
WHERE major = '화학'
AND syear = 1;2. 셀프 조인(Self Join)
물리적인 테이블은 하나이지만, 논리적으로는 두 개의 테이블로 생각하여 조인해줘야하는 것을 셀프 조인이라고 한다. (상사 - 부하 직원과 같이 계층적인 구조를 테이블화할 때 많이 사용한다.)
ex) 사원 번호와 상사의 번호가 있는 테이블에서 상사의 번호가 같은 테이블에 있는 사원 번호를 가리키는 경우. 상사에 대한 정보를 출력해야하면 셀프 조인을 해줘야 한다.
select a.last_name || '의 매니저는 ' || b.last_name || '이다.'
from employees a, employees b
where a.manager_id = b.employee_id
and a.last_name = 'Kochhar';select DISTINCT s1.sno, s1.sname
from student s1, student s2
where s1.sname = s2.sname
and s1.sno != s2.sno;3. 외부 조인(Outer Join)
Outer Join은 조인 조건(Where, On 조건절)을 통해 조인을 할 때에, 두 테이블 중 하나의 값이 NULL인 행도 반환해주는 조인의 형태이다.
외부 조인이 필요한 이유
내부 조인은 서로 공통되는 컬럼의 값이 모든 row에 존재한다는 전제 하에 그걸 기준으로 조인을 하기 때문에, 해당 컬럼이 NULL인 row는 조인 후에 데이터가 누락될 수 있다. 이런 상황에서의 데이터 누락을 막기 위한 것이 바로 외부 조인이다.
=> 기존의 row 갯수를 확인한 후에, 나오지 않는 정보가 있는 경우 외부 조인을 사용하면 된다.
SELECT e.employee_id, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+); --누락된 반대쪽에 (+)
--직원에 부서 정보가 없다1) LEFT OUTER JOIN
좌측 테이블에서 데이터를 먼저 읽은 후, 우측 테이블에서 JOIN 대상 데이터를 읽어온다. 비교 대상 컬럼에 값이 없는 경우 NULL 값으로 채운다.
2) RIGHT OUTER JOIN
LEFTER OUTER JOIN과 반대로 우측 테이블에서 데이터를 먼저 읽은 후, 좌측 테이블에서 JOIN 대상 데이터를 읽어온다.
Left/Right Join은 절대적인 기준이 아니라 어떤 테이블을 먼저 쓰는지에 따라 달라진다.
위의 외부 조인을 ANSI JOIN으로 표현하면 아래와 같다.
SELECT e.employee_id, e.department_id, d.department_name
FROM employees e LEFT JOIN departments d --누락된 쪽이 왼쪽이니까 Left Join
ON e.department_id = d.department_id;외부 조인 예시
교수 테이블의 ROW 수: 36개
과목 테이블의 ROW 수: 32개
--이너 조인한 경우
select c.cname, p.pname
from course c, professor p
where c.pno = p.pno; --29개 --<교수 테이블을 기준으로 출력>
select c.cname, p.pname
from course c, professor p
where c.pno(+) = p.pno; --교수들 중 과목을 안맡은 부분을 출력 ( 36개)
--교수에 과목 정보가 없다
select c.cname, p.pname
from course c RIGHT JOIN professor p
ON c.pno = p.pno;
--<과목 테이블을 기준으로 출력>
select c.cname, p.pname
from course c, professor p
where c.pno = p.pno(+); --과목들 중 교수가 없는 부분을 출력 ( 32개)
--과목에 교수 정보가 없다
select c.cname, p.pname
from course c LEFT JOIN professor p
ON c.pno = p.pno;외부 조인의 개념 자체는 간단하다. 어쨌든 조인을 하는데 내부 조인으로는 누락될 수 있는 정보를 한 쪽이 null인 부분까지 조회할 수 있게 해주는 것이다.
주의
- 여기서는 과목 테이블이 교수 테이블의 pk를 fk로 갖기 때문에 과목 테이블의 정보를 조회해보면 pno가 null인 값이 있다는 것을 눈으로 확인할 수 있지만, 교수 입장에서 과목을 가르치지 않고 노는 교수는 아우터 조인을 통해서만 확인해볼 수 있다.
3) FULL OUTER JOIN
좌, 우 테이블의 모든 데이터를 읽어 조인한 후, 한 번 중복되는 데이터를 삭제한다. (성능 상의 문제가 있을 수 있으므로 왠만하면 LEFT, RIGHT OUTER JOIN 중 필요한 것 하나만 사용해주는 것이 좋다)
select c.cname, p.pname
from course c full join professor p
on c.pno = p.pno;cf. CROSS JOIN
CROSS JOIN은 두 테이블에서 가능한 모든 조합(CATISIAN PRODUCT)을 찾는다. 두 테이블 간 어떤 연관 관계(FK)를 매개로 한 JOIN이 아니기 때문에, CROSS JOIN의 결과는 실제로 존재할 수 없는 쓰레기 값이 많이 포함되게 된다.
select c.cname, p.pname
from course c cross join professor p외부 조인 문제
--Oracle
SELECT D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.JOB, E.SAL
FROM EMP E, DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO
ORDER BY D.DEPTNO, E.ENAME;
--ANSI JOIN
SELECT D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.JOB, E.SAL
FROM EMP E RIGHT OUTER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
ORDER BY D.DEPTNO, E.ENAME;
--Oracle
SELECT D.DEPTNO, D.DNAME,
E.EMPNO, E.ENAME, E.MGR, E.SAL, E.DEPTNO,
S.LOSAL, S.HISAL, S.GRADE,
E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME
FROM EMP E, DEPT D, SALGRADE S, EMP E2
WHERE E.DEPTNO(+) = D.DEPTNO
AND E.SAL BETWEEN S.LOSAL(+) AND S.HISAL(+)
AND E.MGR = E2.EMPNO(+)
ORDER BY D.DEPTNO, E.EMPNO;
--ANSI JOIN
SELECT D.DEPTNO, D.DNAME,
E.EMPNO, E.ENAME, E.MGR, E.SAL, E.DEPTNO,
S.LOSAL, S.HISAL, S.GRADE,
E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME
FROM EMP E RIGHT OUTER JOIN DEPT D
ON (E.DEPTNO = D.DEPTNO)
LEFT OUTER JOIN SALGRADE S
ON (E.SAL BETWEEN S.LOSAL AND S.HISAL)
LEFT OUTER JOIN EMP E2
ON (E.MGR = E2.EMPNO)
ORDER BY D.DEPTNO, E.EMPNO; 자연 조인(Natural Join)
별도의 WHERE나 ON 조건절 없이, 이름이 같은 컬럼을 자동으로 모두 JOIN 기준 컬럼으로 설정하여 조인해준다.
이너 조인과 달리 자연 조인은 조인 대상 두 테이블에서 같은 값을 같는 컬럼을 기준으로 두 테이블을 조인해주고 공통 컬럼은 하나로 합쳐준다.
SELECT empno, ename, deptno
FROM emp NATURAL JOIN dept;USING 조건절
NATURAL JOIN의 단점은 동일한 이름을 가지는 칼럼은 모두 조인이 된다는 것인데, USING 문을 사용하면 컬럼을 선택해서 조인을 할 수가 있다.
SELECT e.empno, e.ename, deptno
FROM emp e JOIN dept d USING(deptno);또한 INNER JOIN 후 USING 절을 써주면, USING 절에 쓰인 컬럼은 공통 컬럼으로 처리되어 하나만 남는다.
