데이터베이스6-1 조인(JOIN)

조인(Join)

- 둘 이상의 테이블을 연결해 데이터를 검색하는 방법

- 보통 둘 이상의 행들의 공통된 값 Primary key 및 Foreign key 값을 사용해 조인한다.

- 두 개의 테이블을 SELECT 문장 안에서 조인하려면 적어도 하나의 칼럼이 그 두 테이블 사이에서 공유되어야 한다.

 

* 조인 종류 *

 

INNER JOIN (Equi join 동등 조인, 내부조인)	조인하고자 하는 두개의 테이블에서 공통된 요소를 통해 결합하는 조인 방식
NON-EQUIJOIN (비 동등 조인)	조인 조건이 정확히 일치하지 않는 경우에 사용 (등급, 학점 값이 범위 안에 있음)
SELF JOIN	조인을 할 때에 기본 테이블 이외에 참조하는 테이블이 다른 테이블이 아닌 자기 자신
LEFT OUTER JOIN(=LEFT JOIN)	왼쪽 테이블의 것은 모두 출력되어야 한다. -> 왼쪽 데이터를 기준으로 붙이기
RIGHT OUTER JOIN(=RIGHT JOIN)	오른쪽 테이블의 것은 모두 출력되어야 한다 -> 오른쪽 데이터를 기준으로 붙이기
NATURAL JOIN	두 테이블의 동일한 이름을 가지는 컬럼을 모두 조인한다. (자동으로) 결합 기준이 필요없다.(ON 불필요)

 

---

1. INNER JOIN(Equi join)

- 대부분 PK(PRIMARY KEY)와 FK(FOREIGN KEY)의 관계를 이용해 조인한다.

- 공통된 컬럼으로 조인한다.

 

<기본 구조>

 

SELECT table1.column1, table2.column1… FROM table1 JOIN table2 ON table1.column1 = table2.column2

SELECT table1.column1, table2.column1… FROM table1, table2   WHERE table1.column1 = table2.column2

 

-> FROM과 JOIN에는 연결되는 테이블, 연결기준이 되는 필드를 ON 절에다가 적기

-> 두 개의 테이블 정보를 하나의 레코드로 만든다.

-> 같은 컬럼명이 존재하는 테이블이 있을 경우에는 반드시 칼럼명 앞에 테이블명을 붙인다.

-> n개의 테이블을 조인하려면 최소한 n-1번의 조인 조건문이 필요하다.

-> 콤마(‘,’)로 조인하는 건 권장하지 않음(오른쪽 코드 XX)

 

---

 

- 사원의 부서 이름을 결정하기 위해 emp테이블의 deptno와 dept테이블의 deptno의 값을 비교해야 한다.

- emp테이블과 dept테이블 사이의 관계는 양쪽 테이블의 deptno열이 같아야 한다. 이것은 PK와 FK로 연결되어 있다. (dept테이블에서 pk로, emp테이블에서 fk로)

 

 

예제1) 사원명(emp), 급여(emp), 부서코드(emp, dept), 부서명(dept)을 선택하라.

 

> SELECT ename, sal, deptno, dname FROM emp, JOIN dept ON emp.deptno = dept.deptno;

 

-> 오류!! 어떤 필드를 사용할지 지정해 줘야함(deptno는 emp에도 있고, dept에도 있어서)

-> emp테이블과 dpet테이블을 합치는 데 기준은 두 테이블에 모두 있는 deptno로 한다!

 

> SELECT emp.ename, emp.sal, emp.deptno, dept.dname FROM emp, JOIN dept ON emp.deptno = dept.deptno;

 

> SELECT e.ename, e.sal, e.deptno, d.dname FROM emp e JOIN dept d ON e.deptno = d.deptno; 

 

-> 위 코드와 똑같은데 별명(alias)을 사용했다. (필드가 많아지면 헷갈리기 때문에 별명이나 테이블명을 필드 앞에 붙이는 게 좋다.

 

> SELECT e.ename, e.sal, d.deptno, d.dname

> FROM emp e JOIN dept d ON e.deptno = d.deptno; 

 

-> deptno는 emp테이블에도 있고, dept 테이블에도 있어서 e.deptno를 d.deptno로 변경해도 결과값은 같다. 

 

*테이블에서 Alias(별명)사용*

 

테이블 별칭을 사용해 긴 테이블명을 간단하게 사용할 수 있다.	테이블 별칭은 30자까지 사용가능 하지만 짧을 수록 좋다.
FROM절에서 별칭이 사용되면 SELECT문 전체에서 사용 가능하다.	테이블의 별칭은 현재 SELECT문에서만 유용하다.

 

예제2) 사원번호, 담당업무, 급여, 부서명, 부서위치를 선택하라.

단 30번 부서의 사원은 제외하고 급여는 10% 인상한 급여와 지급액(금액 + 보너스)를 출력하라

(1) 30번의 부서는 제외하고 사원번호, 담당업무, 급여, 부서명, 부서위치 선택하기(JOIN 사용)

> SELECT e.empno, e.job, e.sal, d.dname d.loc FROM emp e JOIN dept p

ON e.deptno = d.deptno WHERE e.deptno != 30;

 

(2) 10%인상한 급여와 지급액(급여+보너스) 출력하기 (emp 테이블에서)

> SELECT e.sal, e.sal*1.1 '급여', e.sal + IFNULL(e.comm,0) '지급액' FROM emp;

 

-> 보너스는 NULL 값이 있을 수 있기 때문에 IFNULL로 NULL 값이 있다면 0으로 반환해줘야 한다.

-> ‘급여’와 ‘지급액’은 별칭이다.

 

(3) 전체코드

> SELECT e.empno, e.job, e.sal, e.sal*1.1 '급여', e.sal + IFNULL(e.comm,0) '지급액', d.dname, d.loc

FROM emp e JOIN dept d ON e.deptno = d.deptno WHERE e.deptno != 30

 

- 이 코드를 인라인뷰와 함께 사용할 수도 있다. (FROM 절 서브쿼리)

> SELECT a.empno, a.job, a.급여, a.지급액, a.dname, a.loc FROM

(SELECT e.empno, e.job, e.sal, e.sal*1.1 '급여', e.sal+IFNULL(e.comm,0) '지급액' , d.dname, d.loc

FROM emp e join dept d

ON e.deptno = d.deptno WHERE e.deptno != 30) a ;

 

-> 서브쿼리는 위에 식과 똑같고 테이블의 별명을 a로 함

 

 

예제3) MANAGER업무를 하는 사람의 평균급여보다 많이 받는 사원을 선택하는데

사원번호, 사원명, 담당업무, 급여, 부서코드, 부서명을 사원명 기준으로 오름차순으로 정렬하여 선택하라.

(1) MANAGER 업무를 하는 사람의 평균급여보다 많이 받는 사원을 구하기

> SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp WHERE job = 'MANAGER');

 

(2) 사원번호, 사원명, 담당업무, 급여, 부서코드, 부서명을 선택하기(JOIN 사용)

> SELECT e.empno, e.ename, e.job, e.sal, e.deptno, d.dname

FROM emp e JOIN dept d ON e.deptno = d.deptno;

 

(3) 전체코드

SELECT e.empno, e.ename, e.job, e.sal, e.deptno, d.dname

FROM emp e JOIN dept d ON e.deptno = d.deptno

WHERE sal > (SELECT AVG(sal) FROM emp WHERE job = 'MANAGER') ORDER BY e.ename ASC;

 

 

* AND 연산자를 사용한 추가적인 검색 조건*

 

- JOIN 이외의 WHERE절에 추가적인 조인 조건을 가질 수 있다.

- WHERE절과 동일한 기능을 가짐(이때 까지 했던 예제에 WHERE 대신 AND를 넣을 수 있다.)

 

예제) salesman의 사원번호, 이름, 급여, 부서명, 근무지를 출력하라.

> SELECT e.empno, e.ename, e.sal, d.dname, d.loc

FROM emp e JOIN dept d ON e.deptno = d.deptno AND e.job = 'SALESMAN';

 

-> AND 대신에 WHERE도 가능

 

 

---

2. Non-Equijoin 비동등 조인

- 동등조인처럼 동일한 값은 아니지만 값이 범위 안에 속한다.

- 조인 조건이 정확히 일치하지 않는 경우에 사용한다. (등급, 학점)

- 비동등조인은 테이블의 어떤 column도 join할 테이블의 column에 일치하지 않을 때 사용한다.

- WHERE절 뒤의 조건절에서 두 개 이상의 테이블을 연결할 때 조건이 Equal(=)이 아닌 다른 연산 기호로 만들어지는 경우에 사용한다. (동등(=)이외의 연산자: BETWEEN AND, IS NULL, IS NOT NULL, IN, NOT IN)

 

 

- EMP테이블의 어떠한 컬럼도 직접적으로 SALGRADE테이블의 한 컬럼에 상응하지 않는다.

- EMP테이블의 sal컬럼이 SALGRADE의 losal과 hisal 컬럼사이에 있다. (이경우에 non-equijoin)

- 조인 조건은 = 연산자 이외의 BETWEEN ~ AND 연산자를 이용한다.

 

예시) 사원번호, 사원명, 급여, 호봉(salgrade의 grade), 급여의 최솟값(losal),급여의 최댓값(hisal)을 선택하라.

> SELECT e.empno, e.ename, e.sal, s.grade, s.losal, s.hisal

FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;

 

예제1) emp 테이블에서 사원명, 급여, 호봉을 선택하되, 

담당업무가 'Manager'이거나 'Salesman'인 사원 중 사원명에 A가 포함된 사원을 선택하라

(1) 담당업무가 ‘manager’이거나 ‘salesman’인 사원 중 사원명에 A가 포함된 사원 구하기

> SELECT * FROM emp WHERE ename LIKE '%a%' AND (job = 'manager' OR job = 'salesman');

 

(2) 전체 코드

> SELECT e.ename, e.sal, s.grade

FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal

WHERE e.ename LIKE '%a%' AND (e.job = 'manager' OR e.job = 'salesman');

 

> SELECT e.ename, e.sal, s.grade

FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal

AND e.job IN( 'manager', 'salesman') AND e.ename LIKE '%a%';

 

-> In 연산자를 사용하게 되면 OR 사용안해도 된다.

 

*세 개의 테이블 조인*

 

예제1) 사원명(emp), 급여(emp), 부서코드(emp, dept), 부서명(dept), 호봉(salgrade)을 선택하라

> SELECT e.ename, e.sal, e.deptno, d.dname, 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;

 

 

예제2) 사원번호, 사원명, 입사일, 급여, 부서명, 호봉, 호봉의 최고급여를 선택하라

-- 단, 급여가 2500불 이상인 사원을 급여 순으로 내림차순 하여 선택하라.

1) JOIN 먼저 구하기

> SELECT e.empno, e.ename, e.hiredate, e.sal, d.dname, s.grade, s.hisal

FROM emp e JOIN dept d ON e.deptno = d.deptno

JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;

 

2) 전체코드

SELECT e.empno, e.ename, e.hiredate, e.sal, d.dname, s.grade, s.hisal

FROM emp e JOIN dept d ON e.deptno = d.deptno

JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal

AND sal >= 2500 ORDER BY sal DESC;

 

->AND 절과 ORDER BY 절에 e.sal 이렇게 테이블 별칭을 붙여도 됨

 

---

3. SELF JOIN

- 동일 테이블 사이의 조인

- Self join을 사용해 한 테이블의 행들을 같은 테이블의 행들과 조인한다.

- 같은 테이블에 대해 두 개의 별명(Alias)을 작성해 테이블을 구분해서 FROM절에 동일 테이블이 두 번 이상 나타난다.

 

예시) 사원명, 담당업무, 급여, 매니저이름(사원의), 매니저 업무, 매니저급여

> SELECT e.ename, e.job, e.sal, m.ename, m.job, m.sal FROM emp e JOIN emp m ON e.mgr = m.empno;

-> empno를 하는 이유는 PK제약조건에 의해서 empno는 중복 데이터가 허용되지 않기 때문에 연결기준을 empno로 했다.

 

 

예제1) 사원명(emp e), 부서명(dept d), 급여(emp e), 매니저명(emp m), 매니저부서명(emp m 과 dept d2), 매니저급여(emp m)를 선택하라. (self join 과 equi join)

> SELECT e.ename '사원명', d.dname '부서명', e.sal '급여',

            m.ename '매니저명', d2.dname '매니저부서명', m.sal '매니저급여'

FROM emp e JOIN dept d ON e.deptno = d.deptno

JOIN emp m ON e.mgr = m.empno

JOIN dept d2 ON m.deptno = d2.deptno;

 

 

예제2) emp 테이블에서 ‘누구의 관리자는 누구이다’는 내용 출력하기

> SELECT CONCAT(e.ename, '의 관리자는 ' , m.ename, '이다') '결과'

FROM emp e JOIN emp m ON e.mgr = m.empno;

 

---

4. LEFT OUTER JOIN & RIGHT OUTER JOIN

 

LEFT OUTER JOIN	왼쪽 테이블의 것은 모두 출력되어야 한다. (= LEFT JOIN) -> 왼쪽 데이터를 기준으로 붙이기 -> 왼쪽 테이블의 데이터가 더 많을 경우 오른쪽 테이블에는 NULL 값이 생긴다.
RIGHT OUTER JOIN	오른쪽 테이블의 것은 모두 출력되어야 한다 (= RIGHT JOIN) -> 오른쪽 데이터를 기준으로 붙이기 -> 오른쪽 테이블의 데이터가 더 많을 경우 왼쪽 테이블에는 NULL 값이 생긴다.

 

예시) emp 테이블의 부서번호와 dept 테이블의 부서번호를 dept 테이블을 기준으로 JOIN

 

> SELECT DISTINCT d.deptno'D', e.deptno 'E'

FROM dept d LEFT OUTER JOIN emp e ON d.deptno = e.deptno;

 

 

-> emp(E)테이블이 dept(D)테이블보다 데이터 값이 더 적기 때문에 NULL 값으로 나온다. 이렇게 왼쪽 테이블 기준으로 값이 나온다.

 

> SELECT DISTINCT e.deptno 'E', d.deptno'D'

FROM emp e RIGHT OUTER JOIN dept d ON d.deptno = e.deptno;

 

 

-> emp(E)테이블이 dept(D)테이블보다 데이터 값이 더 적기 때문에 NULL 값으로 나온다. 이렇게 오른쪽 테이블 기준으로 값이 나온다.

---

[종합문제]

1. emp 테이블에서 모든 사원에 대한 이름, 부서번호, 부서명을 출력하는 문장을 작성하세요.(부서번호순으로 오름차순 정렬하라.)

> SELECT e.ename '이름', e.deptno '부서번호', d.dname '부서명'

FROM emp e JOIN dept d ON e.deptno = d.deptno ORDER BY e.deptno ASC;

 

 

2. emp테이블에서 NEW YORK에서 근무하고 있는 사원에 대하여 이름, 업무, 급여, 부서명을 출력하는 SELECT문을 작성하세요.

> SELECT e.ename '이름', e.job '업무', e.sal '급여', d.dname '부서명', d.loc '현재 근무지'

FROM emp e JOIN dept d ON e.deptno = d.deptno AND d.loc = 'NEW YORK';

 

-> WHERE도 상관없음

 

 

3.EMP테이블에서 보너스를 받는 사원에 대하여 이름, 부서명, 위치를 출력하는 SELECT문을 작성하세요.

> SELECT e.ename '이름', d.dname '부서명', d.loc '위치'

FROM emp e JOIN dept d ON e.deptno = d.deptno WHERE e.comm > 0;

 

> SELECT e.ename '이름', d.dname '부서명', d.loc '위치'

FROM emp e JOIN dept d ON e.deptno = d.deptno AND IFNULL(e.comm,0)> 0;

 

 

4. emp 테이블에서 이름 중 L자가 있는 사원에 대하여 이름, 업무, 부서명, 위치를 출력하는 SELECT문을 작성하세요. (서브쿼리로도 만들어보기)

> SELECT e.ename '이름', e.job '업무', d.dname '부서명', d.loc '위치'

FROM emp e JOIN dept d ON e.deptno = d.deptno AND e.ename LIKE '%L%';

 

> SELECT a.ename '이름', a.job '업무', d.dname '부서명', d.loc '위치'

FROM ( SELECT ename, job, deptno FROM emp WHERE ename LIKE '%L%') a JOIN dept d

ON a.deptno = d.deptno;

 

 

5 아래의 결과를 출력하는 문장을 작성하세요 (관리자가 없는 King을 포함하여 모든 사원을 출력)

 

 

> SELECT e.ename 'Employee', e.empno 'Emp#', m.ename 'Manager', m.empno 'Mgr#'

FROM emp e LEFT OUTER JOIN emp m ON e.mgr = m.empno;

 

 

5. NATURAL JOIN 

- 두 테이블의 동일한 이름을 가지는 컬럼은 모두 조인이 된다.

- 동일한 컬럼을 모두 갖게 되므로 테이블에 별명을 주면 오류가 발생한다.

- 같은 필드명을 가지고 있는 필드끼리 알아서 결합해주기 때문에 ON 연산자(결합기준)가 필요없다.

- 동일한 컬럼이 두 개 이상일 경우 JOIN-USING 문장으로 조인되는 컬럼을 제어할 수 있다.

 

예시) 사원번호(empno), 사원명(ename), 부서번호(deptno) 부서명(dname)을 선택하세요.

> SELECT empno '사원번호' , ename '사원명', deptno '부서 번호',부서번호 dname '부서명'FROM emp NATURAL JOIN dept;

> SELECT e.empno, e.ename, d.deptno, d.dname FROM emp e JOIN dept d ON e.deptno = d.deptno; 와 동일

 

 

* JOIN ~ USING *

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

- USING 절 안에 포함되는 컬럼에 Alias를 지정하면 오류 발생!!

 

> SELECT empno, ename, sal, deptno, dname FROM emp JOIN dept USING (deptno);

> SELECT empno, ename, sal, e.deptno, dname FROM emp e JOIN dept USING (deptno);

 

-> 결과값은 동일하다.