집합 연산자와 조인의 차이점
- 조인은 두 개 이상의 테이블을 연결하여 하나의 테이블 처럼 출력할 때 사용하는 방식이다.
- 집합연산자를 사용한 결과는 두 개 이상 SELECT 문의 결과 값을 세로로 연결한 것이고, 조인을 사용한 결과는 두 개
이상의 테이블 데이터를 가로로 연결 한 것이라고 볼 수 있다.
FROM 절에는 여러 테이블을 명시할 수 있다.
select * from emp, dept order by empno;
단순히 FROM절에 테이블을 두 개 입력했을 때는 위와 같이 무수히 많은 데이터들이 출력된다.
이는 FROM절에 함께 명시한 각 테이블을 구성하는 행이 모든 경우의 수로 조합되어 출력되기 때문이다
때문에 조건식을 지정해 주어야 하는데 이 때에는 보통 외래키를 이용하여 조건식을 설정해준다.
1. 등가 조인
- 등가 조인은 테이블을 연결한 후에 출력 행을 각 테이블의 특정 열에 일치한 데이터를 기준으로
선정하는 방식이다.
- 내부 조인(Inner Join) 또는 단순 조인(Simple Join)
특정 열(외래키)를 이용하여 일치하는 데이터를 기준으로 emp, dept 두 테이블을 조인해보자.
select * from emp e, dept d where e.deptno = d.deptno;
emp테이블에서 dept 테이블을 참조하는 외래키는 deptno이므로
e.deptno = d.deptno 라는 조건식을 지정함으로써 각 사원들의 부서 별 부서이름과 지역이 오른쪽에 추가된 것을 볼 수 있다.
FROM 절의 emp e, detp d 는 emp테이블의 별칭을 e, dept테이블의 별칭을 d로 지정해 두는 것이다.
이로 인해 emp테이블의 부서번호는 e.deptno와 같이 간단하게 표기할 수 있다.
또한 테이블은 2개를 지정하였으나 SELECT절에서 이를 지정하지 않고 단순히 empno, deptno 등과 같이 사용할 경우 어느 테이블에서 불러오는지 알 수 없기 때문에 오류가 발생한다.
따라서 SELECT 절에서는 FROM 절에서 사용한 별칭(또는 테이블 명)을 사용하여 e.empno, d.deptno와 같이 지정해주어야 한다.
select e.empno, e.ename, d.deptno, d.dname from emp e, dept d where e.deptno = d.deptno;
where절에 조건을 추가하여 범위를 설정할 수도 있다.
위와 같은 조인식에서 부서명이 SALES인 직원들을 찾아보자.
select e.empno, e.ename, d.deptno, d.dname from emp e, dept d
where e.deptno = d.deptno and d.dname = 'SALES';
내부 조인은 다음과 같이 INNER JOIN [테이블] ON [조건] 을 이용하여 조인 할 수도 있다.
위 예시와 같은 조인구문을 INNER JOIN을 이용하여 작성해보면 다음과 같다.
select e.empno, e.ename, d.deptno, d.dname from emp e
inner join dept d on e.deptno = d.deptno and d.dname = 'SALES';
2. 비등가 조인
- 조인 대상이 되는 테이블과 대응되는 열(컬럼)이 없는 경우 범위를 지정하는 조건식으로 조인하는 방식
- WHERE 절에 "="이 아닌 ">", "<", "BETWEEN" 등의 조건식을 사용한다.
급여가 700부터 1200까지는 1등급, 1201부터 1400까지는 2등급 ... 3001부터 9999까지는 5등급을 나타내는 SALGRADE 테이블이다.
이 SALGRADE 테이블과 EMP 테이블을 조인해보자
select e.empno, e.ename, e.sal, s.grade, s.losal, s.hisal from emp e, salgrade s
where e.sal between s.losal and s.hisal;
위와 같이 대응되지 않는 두 테이블을 조인하기 위해
emp테이블의 사원의 sal(급여)가 between losal to hisal (범위 사이에 있는 경우) 를 조건으로 하고 조인하였다.
3. 외부 조인 (포괄 조인)
- 두 테이블간 조인 수행에서 조인 기준 열의 어느 한쪽이 NULL이어도 강제로 출력하는 방식
- 외부 조인은 좌우를 따로 나누어 지정하는데 WHERE 절에 조인 기준 열 중 한쪽에(+) 기호를 붙임
- LEFT OUTER JOIN (LEFT JOIN) : 왼쪽 테이블을 기준으로 조인
- RIGHT OUTER JOIN (RIGHT JOIN) : 오른쪽 테이블을 기준으로 조인
- FULL OUTER JOIN (FULL JOIN) : 모든 NULL값까지 출력
위 그림을 보면 앞서 나왔던 INNER JOIN 과 LEFT / RIGHT / FULL 조인이 이해가 될 것이다.
앞서 했던 예시를 조금 변경하여 부서번호, 부서명, SALES 부서에 있는 사원들을 찾아보자.
select e.empno, e.ename, d.deptno, d.dname from emp e right join dept d on
e.deptno = d.deptno and d.dname = 'SALES';
두 테이블 중 오른쪽 테이블 (DEPT) 을 기준으로 DEPT 테이블에서 SALES 를 조건으로 가진 열 이외에 대응하는 열이 없더라도 모두 NULL값으로 강제 출력시키는 것을 볼 수 있다.
즉 오른쪽 테이블을 기준으로 조건에 부합하는 것은 출력하고 부합하는것이 없다면 NULL이 되는 셈.
반대로 LEFT JOIN을 사용하면
select e.empno, e.ename, d.deptno, d.dname from emp e left join dept d on
e.deptno = d.deptno and d.dname = 'SALES';
이처럼 왼쪽 테이블(EMP)를 기준으로 모든 사원에 대해 조건에 부합하는 SALES 부서 뿐 아니라 대응되는 열이 없는 모든 사원을 출력하고 그에 대한 부서명과 부서번호는 모두 NULL 처리 된 것을 볼 수 있다.
이 또한 왼쪽 테이블을 기준으로 부합하는 값이 있다면 출력하고 없다면 모두 NULL로 처리해서 보여준다.
마지막으로 FULL JOIN 을 사용해보면
select e.empno, e.ename, d.deptno, d.dname from emp e full join dept d on
e.deptno = d.deptno and d.dname = 'SALES';
LEFT JOIN 과 RIGHT JOIN 에서 NULL로 처리되었던 모든 값들이 다 출력되는 것을 볼 수 있다.
4. 자체 조인 (셀프 조인)
- 하나의 테이블을 여러개의 테이블 처럼 활용하여 조인하는 방식
- SELECT 문 내부에서 별칭을 다르게 지정하여 논리적으로 다른 테이블인 것 처럼 사용
자체 조인을 사용하여 각 사원들의 상관을 구하는 쿼리문을 작성해보자
select e1.ename as "사원", e2.ename as "상관" from emp e1, emp e2
where e1.mgr = e2.empno;
emp 테이블을 e1, e2로 나누어 e1의 mgr(매니저)과 e2의 empno(사원번호)를 비교하여 출력하였다.
이처럼 하나의 테이블도 별칭을 따로 지정하여 다른 테이블인 것처럼 사용할 수 있다.