L08 join

2016. 10. 20(Thu)

L08 join

• Join

• 둘 이상의 테이블을 연결하여 데이터를 검색

• join의 방식으로 크게 3가지(Equi, Non-Equi, Self Join)로 나눈다.

1. Cross Join

1. Cross Join 절은 조인 되는 두 테이블에서 곱집합을 반환

2. Cartesina Product를 구할 때 사용

3. 왼쪽 테이블을 기준으로 오른쪽 테이블의 열 곱한 결과

4. m열을 가진 테이블과 n열을 가진 테이블이 교차 조인되면 m*n개의 열을 생성

5. SELECT ename FROM emp CROSS JOIN dept

6. CROSS JOIN을 생략하고 ‘,’ 사용 가능

2. Equi Join

1. 조인하는 테이블 간에 공통된 값(Foreign Key)을 이용하여 조인한다.

2. Equi Join은 공통된 값 만은 이용하는 Inner Join과

3. 공통 값을 이용하고 남은 데이터들도 조인한 Outer Join이 있다.

4. 공통된 값을 비교할 때 Where절을 사용하지 않는 조인을 ANISI Join이라 부른다.

1. A a INNER JOING B b ON a.key = b.f_key

2. A JOIN B USING (key)

3. A NATURAL JOIN B

3. Inner Join

1. 가장 흔한 결합 방식

2. 일반적인 형태

SELECT e.empno, e.ename, d.dname

FROM dept d, emp e

WHERE d.deptno = e.deptno;

3. INNER JOIN을 사용한 예제

SELECT e.empno, e.ename, d.dname

FROM dept d INNER JOIN emp e

ON d.deptno = e.deptno;

1. 콤마(,) 대신 INNER JOIN을 사용할 수 있다.

2. INNER는 생략 가능하다.

3. join 조건은 ON 절에 온다.

4. 두 테이블에 칼럼이 같다면 ON 대신 USING(DEPTNO)로 대체 가능하다.

1. NATURAL JOIN을 이용하여 조인하는 예제

SELECT e.empno, e.ename, d.dname

FROM dept d NATURAL JOIN emp e;

1. NATURAL JOIN을 사용하면 동일한 칼럼을 내부적으로 모두 조인하므로, ON절이 생략 가능하다.

1. JOIN~USING절을 이용하여 조인하는 예제

SELECT e.empno, e.ename, d.dname

FROM dept d JOIN emp d

USING (deptno);

1. NATURAL JOIN의 단점은 동일한 이름을 가지는 칼럼은 모두 조인이 되는데. USING문을 사용하면 칼럼을 선택해서 조인할 수 있다.

4. OUTER JOIN

1. 2개 이상의 테이블이 조인 될 때 조인 조건을 만족하지 않는 행들까지도 출력

2. ENAME이 ‘KING’직원의 DEPTNO를 NULL로 바꾼다

3. UPDATE EMP SET DEPTNO=NULL WHERE ENAME=’KING’

4. INNER JOIN의 결과가 12개이었지만, UPDATE 후 11개로 변경됨

5. KING의 DEPTNO가 DEPT TABLE에 만족하는 값이 없기 때문

6. 이때 KING도 JOIN의 결과로 나타내기 위해 LEFT OUTER JOIN을 사용.

7. LEFT OUTER JOIN

1. LEFT OUTER JOIN은 오른쪽 테이블에 조인 시킬 칼럼의 값이 없는 경우 사용한다.

2. select ename, deptno, dname, loc from emp left outer join dept using(deptno);

3. select e.ename, e.deptno, d.dname FROM EMP e, DEPT d WHERE e.deptno=d.deptno(+);

4. KING이 출력 되지만 나머지 칼럼은 NULL

8. RIGHT OUTER JOIN

1. RIGHT OUTER JOIN은 왼쪽 테이블에 조인 시킬 칼럼의 값이 없는 경우 사용한다.

2. select ename, deptno, dname, loc from emp right outer join dept using(deptno);

3. select e.ename, e.deptno,d.deptno, d.dname FROM EMP e, DEPT d WHERE e.deptno(+)=d.deptno;

4. DEPTNO 40 부서가 출력 되지만 ename = NULL

9. FULL OUTER JOIN

1. FULL OUTER JOIN은 양쪽 테이블 모두 Outer Join을 걸어야 하는 경우 사용 한다.

2. select e.ename, e.deptno, d.dname, d.loc from emp e full outer join dept d on e.deptno = d.deptno;

3. Join의 조건은 ON or USING을 사용해야 한다.

4. MySQL에서는 union으로 합집합 결과를 출력해야 한다.

5. (SELECT e.ename, e.deptno ,d.dname

FROM EMP e LEFT OUTER JOIN DEPT d

on e.deptno = d.deptno)

UNION

(SELECT e.ename, e.deptno, d.dname

FROM emp e right outer join dept d

on e.deptno = d.deptno);

5. NON-EQUI Join

1. 테이블 간에 공통된 값이 없는 조인

2. cross join이 일어난다. (두 테이블의 곱)

3. where의 검색 조건으로 많이 사용되며 두 테이블 간 공통된 값이 없음으로 동등(=) 이외의 연산자로 검색한다.

4. SELECT e.empno, e.sal, s.grade

FROM emp e, salgrade s

5. 위의 쿼리에서 사원의 급여에 따른 등급을 표시하세요.

6. WHERE e.sal BETWEEN s.losal AND s.hisal;

6. Self Join

1. Equi Join과 같지만 하나의 테이블에서 조인이 일어난다.

2. 같은 테이블에 대해 두 개의 alias를 사용하여 FROM절에 두 개의 테이블을 사용하는 것처럼 조인한다.

3. 사원 매니저명을 조회하는 Self Join 예제

SELECT e.ename, a.ename “Manager”

FROM emp e, emp a

WHERE  a.mgr = e.empno;

7. 문제1) dallas에서 근무하는 직원의 이름, 직업, 부서번호, 부서이름을 출력하라

     답) SELECT ename, job, deptno, dname, loc

from EMP JOIN DEPT

USING(deptno)

where lower(loc)='dallas';

8. 문제2) accounting 부서 소속 사원의 이름과 입사일을 출력하세요

     답)SELECT ename, hiredate, dname

FROM EMP NATURAL JOIN DEPT

WHERE LOWER(dname)='accounting';

9. 문제3) 직급이 manager인 사원의 이름, 부서명을 출력하세요.

     답) SELECT ename, dname, job

FROM EMP e, DEPT d

WHERE e.DEPTNO = d.DEPTNO

AND LOWER(job) = 'manager';

10. 문제4) 급여 등급이 3등급 이상인 직원의 사원 이름과 사원의 관리자 이름, 급여와 급여 등급 순으로 출력하라.

     답) SELECT e.ename, a.ename manager, e.sal, s.grade

FROM EMP e LEFT OUTER JOIN EMP a

ON e.mgr = a.empno

CROSS JOIN SALGRADE s

WHERE e.SAL BETWEEN s.LOSAL AND s.HISAL

AND s.grade > 2

ORDER BY s.grade;

11. 문제5) 커미션을 받는 사원의 사원이름, 부서이름, 실제급여(급여+커미션), 실제급여의 등급을 실제급여 순으로 출력하세요.

     답) SELECT e.ENAME, d.DNAME, e.SAL+e.COMM, s.GRADE

FROM EMP e JOIN DEPT d CROSS JOIN SALGRADE s

ON e.DEPTNO = d.DEPTNO

WHERE e.SAL+e.COMM BETWEEN s.LOSAL AND s.HISAL

AND e.COMM>0

ORDER BY e.SAL;

12. 문제6) EMP TABLE을 Self Join해서 Smith씨와 동일한 근무지에 근무하는 사원의 이름을 출력하세요.

     답) SELECT e.ename, e.deptno, a.ename, a.deptno

FROM EMP e JOIN EMP a

ON e.DEPTNO = a.DEPTNO

WHERE e.ENAME='SMITH'

AND a.ENAME <> 'SMITH';

      서브쿼리) SELECT ename, deptno

FROM EMP

WHERE deptno = (

 SELECT deptno

 FROM EMP

 WHERE LOWER(ename) = 'smith');