국비 5일차 JOIN(catasian, equi, non-equi, self, outer), ANSI JOIN(cross, natural, using, on, left, right, full outer)

2021.01.11 국비교육 5일차

[TOC]

JOIN

PK(primary_Key)

• 중복값 불가(유일)
• NULL 불가

Catasian Product

모든 가능한 행들의 join으로 다음과 같은 경우에 발생

1. 조이 조건이 생략된 경우
2. 조인 조건이 잘못된 경우
3. 첫번재 와 두번재 행의 모든 행이 조인되는 경우
4. 양쪽 ROW의 개수를 곱한 결과

select  empno, ename, job, dept.deptno, dname, loc
from dept, emp
order by empno;

deptno를 기준으로 두테이블 조인

Equi 조인

• PK와 FK의 관계를 이용하여 조인
• 단순 조인 또는 내부(inner) 조인 이라고 함

SELECT EMP.EMPNO, EMP.ENAME, DEPT.DNAME, DEPT.LOC
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;

SELECT e.EMPNO, e.ENAME, d.DNAME, d.LOC
FROM EMP e, DEPT d
WHERE e.DEPTNO = d.DEPTNO;

SELECT e.EMPNO, d.DEPTNO, d.DNAME, d.LOC
FROM EMP e, DEPT d
WHERE e.DEPTNO = d.DEPTNO AND e.EMPNO = 7369;
--7369번의 부서번호, 부서이름, 부서지역, 출력

SELECT d.DNAME 부서명, COUNT(*) 인원수
FROM EMP e, DEPT d
WHERE e.DEPTNO = d.DEPTNO AND e.HIREDATE < '05/01/01'
GROUP BY d.DNAME;
--입사일이 2005년 이전인 직원들의 부서이름과 부서별 인원 출력

• SELECT 부분의 EMP. DEPT. 등 테이블 이름을 명시하지 않아도 출력값은 나오지만 추후에 어느 테이블의 칼럼인지 구분이 어렵기 때문에 명시하는 것 권장

NON-Equi 조인

• WHERE절에 조인 조건을 지정할 때 동등 연사자 이외의 비교 연사자를사용하는 조인을 의미한다.

SELECT e.ENAME, e.SAL, s.GRADE
FROM EMP e, SALGRADE s
WHERE e.SAL BETWEEN s.LOSAL AND s.HISAL;

SELECT e.EMPNO, e.ENAME,e.JOB, e.SAL, s.GRADE, s.LOSAL, s.HISAL
FROM EMP e, SALGRADE s
WHERE e.SAL BETWEEN s.LOSAL AND s.HISAL AND e.DEPTNO= 10;

SELECT e.ENAME, s.GRADE, SAL
FROM EMP e, SALGRADE s
WHERE e.SAL BETWEEN s.LOSAL AND s.HISAL AND e.EMPNO = 7369;
--7369번의 이름, 월급등급, 월급 출력

SELECT e.ENAME, d.DNAME, d.LOC, s.GRADE
FROM EMP e, SALGRADE s, DEPT d
WHERE e.DEPTNO = d.DEPTNO AND e.SAL BETWEEN s.LOSAL AND s.HISAL
ORDER BY 2, 4;
--사원에 해당하는 부서정보(부서명, 지역), 월급등급

SELECT e.EMPNO, e.ENAME, e.DEPTNO,d.DNAME, d.LOC, e.SAL, s.GRADE
FROM EMP e, SALGRADE s, DEPT d
WHERE e.SAL BETWEEN s.LOSAL AND s.HISAL AND e.DEPTNO = d.DEPTNO
AND e.DEPTNO = 10;
--부서 번호가 10번인 부서의 사원번호, 이름,부서번호, 부서명, 지역,월급, 월급 등급 출력

SELECT e.EMPNO, e.ENAME, e.DEPTNO, d.DNAME, d.LOC, e.SAL, s.GRADE
FROM EMP e, SALGRADE s, DEPT d
WHERE e.SAL BETWEEN s.LOSAL AND s.HISAL 
AND e.DEPTNO = d.DEPTNO
AND e.EMPNO = 7369;
--사원번호 7369번의 이름, 부서번호, 부서명, 지역, 월급, 월급등급

Self 조인

• 특정 테이블 자신을 자신이 JOIN하는 방법

SELECT a.EMPNO 사원번호, a.ENAME 사원명,a.MGR, b.ENAME 담당매니저
FROM EMP a, EMP b
WHERE b.EMPNO = a.MGR;

SELECT e.ENAME, e.MGR, m.EMPNO, m.ENAME
FROM EMP e, EMP m
WHERE e.MGR = m.EMPNO AND e.EMPNO = 7369;
--7369번 사원의 이름, 관리자번호, 관리자의 사원번호, 관리자 이름 출력

SELECT e.ENAME, e.MGR, m.EMPNO, m.ENAME, m.DEPTNO, d.DNAME
FROM EMP e, EMP m, DEPT d
WHERE e.MGR = m.EMPNO AND e.EMPNO = 7369 AND m.DEPTNO = d.DEPTNO;
--7369번 사원의이름, 관리자 번호, 관리자의 사원번호, 관리자 이름, 관리자의 부서번호, 부서이름

OUTER JOIN

SELECT e.ENAME 사원명, m.ENAME 관리자명, mm.ENAME "관리자의 관리자명"
FROM EMP e, EMP m, EMP mm
WHERE e.MGR = M.EMPNO 
AND m.MGR = mm.EMPNO;

ANSI JOIN

• 표준화된 JOIN

CROSS JOIN

• catasian product와 동일

select  empno, ename, job, dept.deptno, dname, loc
from dept, emp;
-- catasian

SELECT EMPNO, ENAME, DNAME FROM DEPT CROSS JOIN EMP;
--위와 결과값 동일

NATURAL JOIN

• EQUI-JOIN과 동일

SELECT d.DNAME 부서명, COUNT(*) 인원수
FROM EMP e, DEPT d
WHERE e.DEPTNO = d.DEPTNO AND e.HIREDATE < '05/01/01'
GROUP BY d.DNAME;
--EQUI 조인

SELECT DNAME, COUNT(*) FROM EMP
NATURAL JOIN DEPT
WHERE TO_CHAR(HIREDATE, 'YYYY') <=2005
GROUP BY DNAME;
--위와 동일

SELECT EMPNO, ENAME, DNAME, LOC FROM EMP
NATURAL JOIN DEPT;

SELECT ENAME, DEPTNO, DNAME FROM EMP NATURAL JOIN DEPT
WHERE EMPNO =  7369;
--7369번의 이름과 부서번호, 부서이름, 출력

• 공통 칼럼에 알리아스 넣으면 안됨

USING 절

• 동일 이름의 칼럼이 여러개인 경우 조인 칼럼 지정
• NATURAL JOIN과 상호 배타적

SELECT EMPNO, ENAME, DNAME, LOC FROM EMP
JOIN DEPT USING(DEPTNO);

ON 절

• NON-EQUI조인 또는 임의의 조건으로 조인시 사용
• 조인할 칼럼을 명시하기 위해서 사용 // 공통 칼럼 없을때도 사용

SELECT EMPNO, ENAME, DNAME, LOC
FROM EMP
JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO
WHERE DEPT.DEPTNO = 10;

SELECT e.EMPNO, e.ENAME, d.DNAME, d.LOC
FROM EMP e JOIN DEPT d
ON e.DEPTNO  = d.DEPTNO 
WHERE d.DEPTNO = 10;
-- ON 사용

SELECT ENAME, DNAME, LOC
FROM EMP JOIN DEPT USING(DEPTNO)
WHERE DEPTNO = 10;
--USING 사용

SELECT e.ENAME, e.SAL, s.GRADE
FROM EMP e JOIN SALGRADE s ON e.SAL BETWEEN s.LOSAL AND s.HISAL;
--사원이름, 월급, 월급등급

SELECT e.ENAME, e.SAL, s.GRADE
FROM EMP e JOIN SALGRADE s ON e.SAL BETWEEN s.LOSAL AND s.HISAL
WHERE e.EMPNO = 7369;
--사원이름, 월급, 월급등급 - 7369번

SELECT e.ENAME, e.SAL, s.GRADE ,DEPTNO, d.DNAME
FROM EMP e JOIN DEPT d USING(DEPTNO)
JOIN SALGRADE s ON e.SAL BETWEEN s.LOSAL AND s.HISAL;
-- 사원이름, 월급, 월급등급, 부서번호, 부서이름
--SELECT 절에서 DEPTNO는 공통 칼럼이므로 테이블 명시 제외

SELECT e.ENAME, e.SAL, s.GRADE ,e.DEPTNO, d.DNAME
FROM EMP e JOIN DEPT d ON e.DEPTNO = d.DEPTNO
JOIN SALGRADE s ON e.SAL BETWEEN s.LOSAL AND s.HISAL;
--위의 명령문의 USING을 ON으로 활용
-- USING과 달리 DEPTNO앞에 테이블 명시해주어야함

• ON 조건절에 JOIN 조건 외에도 데이터 검색조건을 추가할수는 있으나 검색 조건 목적인 경우는 WHERE절을 사용함을 권장
• SELECT에 어떤 테이블 거 인지 명시해주어야함

SELF JOIN

SELECT e.EMPNO, e.ENAME, e.MGR, m.EMPNO, m.ENAME, m.MGR, mm.EMPNO, mm.ENAME
FROM EMP e JOIN EMP m ON e.MGR = m.EMPNO
jOIN EMP mm ON m.MGR = mm.EMPNO
WHERE e.EMP = 7369;
--담당 관리자 출력

SELECT e.ENAME 사원명, m.ENAME 관리자명, mm.ENAME "관리자의 관리자명"
FROM EMP e, EMP m, EMP mm
WHERE e.MGR = M.EMPNO 
AND m.MGR = mm.EMPNO;
--위 명령문과 결과 같음

SELECT e.ENAME 사원명, m.ENAME 관리자명, d.DNAME 관리자부서, d.LOC
FROM EMP e JOIN EMP m ON e.MGR = m.EMPNO
JOIN DEPT d ON m.DEPTNO = d.DEPTNO;
--관리자의 관리자정보(부서, 부서위치)

LEFT, RIGHT, FULL OUTER JOIN

SELECT EMPNO, ENAME, DNAME
FROM DEPT LEFT OUTER JOIN EMP
ON DEPT.DEPTNO = EMP.DEPTNO;

--부서명 할당 (해당자 없는 부서까지)

SELECT EMPNO, ENAME, DNAME
FROM DEPT FULL OUTER JOIN EMP
ON DEPT.DEPTNO = EMP.DEPTNO;