Join: 2개 이상의 테이블에서 검색하는 것

join 문법:

(1) ANSI 표준 문법:

SELECT 컬럼, ...
    FROM 테이블1
    	join 종류 테이블2 ON join 조건;

(2) Oracle 표준 문법:

SELECT 컬럼, ...
FROM 테이블1, 테이블2, ...
WHERE join 조건;

join 종류:

(1) inner join

교집합 부분만 찾

(2) outer join

left outer join // 왼쪽
right outer join // 오른쪽
full outer join // 합집합 // Oracle 지원 안됨

inner join과 outer join의 차이점을 보기 위해서 행 추가.

INSERT INTO emp(empno, ename, deptno)
VALUES (1004, '오쌤',50);
COMMIT;

사번, 이름, 부서번호, 부서이름을 검색. inner join.
테이블 별명 설정(AS 생략 가능)

SELECT e.empno, e.ename, d.deptno, d.dname
FROM emp e 
    inner join dept d ON e.deptno = d.deptno;

Oracle에서만 사용 가능

SELECT e.empno, e.ename, d.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;

left outer join

SELECT e.empno, e.ename, e.deptno, d.dname
FROM emp e
    left outer join dept d ON e.deptno = d.deptno;

Oracle left outer join

SELECT e.empno, e.ename, e.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno(+); -- 오른쪽에 (+)

right outer join

SELECT e.empno, e.ename, e.deptno, d.dname
FROM emp e
    right outer join dept d ON e.deptno = d.deptno;

Oracle right outer join

SELECT e.empno, e.ename, e.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno; -- 왼쪽에 (+)

full outer join

SELECT emp.empno, emp.ename, dept.deptno, dept.dname
FROM emp
    full outer join dept ON emp.deptno = dept.deptno
ORDER BY deptno;

Oracle full outer join 문법을 제공하지 않음.
left outer join 결과와 right outer join 결과를 합집합(union)하면 됨.

SELECT e.empno, e.ename, e.deptno, d.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno(+)  -- left outer join
UNION                         -- 합집합
SELECT e.empno, e.ename, e.deptno, d.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno; -- right outer join

inner join은 간단히 inner으로 생략 가능. inner join = join
left/right/full/ outer join에서 outer 생략 가능함. left outer join = left join

self-join: 같은 테이블에서 join하는 것.

사번, 이름, 매니저 사번, 매니저 이름 검색.

SELECT e1.empno AS "사번", e1.ename AS "직원 이름" , e1.mgr AS "매니저 사번", e2.ename AS "매니저 이름" -- ename 출력
FROM emp e1
    join emp e2 ON e1.mgr = e2.empno -- mgr와 empno같으면
ORDER BY e1.empno;

SELECT e1.empno, e1.ename, e1.mgr, e2.ename
FROM emp e1, emp e2
WHERE e1.mgr = e2.empno
ORDER BY e1.empno;

equi join: join 조건이 =를 사용해서 마들어지는 경우.

non-equi join: join 조건이 부등식(>, >=, <, <=, ...)을 사용해서 만들어지는 경우.

emp, salgrade 테이블을 사용, 사번, 이름, 급여, 급여 등급을 검색.
사번 오름차순 정렬.

SELECT e.empno, e.ename, e.sal, s.grade
FROM emp e 
    JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
ORDER BY e.empno;

SELECT e.empno, e.ename, e.sal, s.grade
FROM emp e, salgrade s 
WHERE e.sal BETWEEN s.losal AND s.hisal
ORDER BY e.empno;

ex1. 직원 이름, 직원 근무 도시를 검색. 근무 도시 오름차순 정렬

SELECT e.ename, d.loc
FROM emp e 
    JOIN dept d ON e.deptno = d.deptno
ORDER BY d.loc;

ex2. 직원 이름, 매니저 이름, 급여, 급여 등급을 검색. 매니저 오름차순, 급여 등급 오름차순 정렬

SELECT e1.ename, e2.ename, e1.sal, s.grade
FROM emp e1
    JOIN emp e2 ON e1.mgr = e2.empno
    JOIN salgrade s ON e1.sal BETWEEN s.losal AND s.hisal
ORDER BY e2.ename, s.grade;

ex3. 직원 이름, 부서 이름, 급여, 급여 등급을 검색. 부서 이름 오름차순, 급여 등급 오름차순 정렬

SELECT e.ename, d.dname, e.sal, s.grade
FROM emp e
    JOIN dept d ON e.deptno = d.deptno
    JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
ORDER BY d.dname, s.grade;

ex4. 부서 이름, 부서 위치, 부서 직원수 검색. 부서 번호 오름차순

SELECT d.dname, d.loc, COUNT(*)
FROM emp e 
    FULL JOIN dept d ON e.deptno = d.deptno
GROUP BY e.deptno, d.dname, d.loc
ORDER BY e.deptno;

ex5. 부서 번호, 부서 이름, 부서 직원수, 부서의 급여 최솟값, 부서의 급여 최댓값 검색. 부서 번호 오름차순

SELECT e.deptno, d.dname, COUNT(*), MAX(e.sal), MIN(e.sal)
FROM emp e
    JOIN dept d ON e.deptno = d.deptno
GROUP BY e.deptno, d.dname
ORDER BY e.deptno;

ex6. 부서 번호, 부서 이름, 사번, 이름, 매니저 사번, 매니저 이름, 급여, 급여 등급을 검색.
급여가 3000 이상인 직원들만 검색.
(1) 부서 번호 (2) 사번 오름차순 정렬

SELECT e1.deptno, d.dname, e1.empno, e1.ename AS "이름", e1.mgr, e2.ename AS "매니저 이름", e1.sal, s.grade
FROM emp e1 
    FULL JOIN emp e2 ON e1.mgr = e2.empno
    JOIN dept d ON e1.deptno = d.deptno
    JOIN salgrade s ON e1.sal BETWEEN s.losal AND s.hisal
WHERE e1.sal >= 3000
ORDER BY e1.deptno, e1.empno;

(1) 부서 번호 (2) 사번 오름차순 정렬

SELECT e1.deptno, d.dname, e1.empno, e1.ename AS "이름", e1.mgr, e2.ename AS "매니저 이름", e1.sal, s.grade
FROM emp e1, emp e2, dept d, salgrade s
WHERE
e1.mgr = e2.empno AND 
e1.deptno = d.deptno AND
e1.sal BETWEEN s.losal AND s.hisal AND
e1.sal >= 3000
ORDER BY e1.deptno, e1.empno;