[day_07] DB / 12. 기본 SQL 7 - JOIN 조인 (inner join, outer join, cross join, self join)

 JOIN

  : 두개 이상의 테이블 간의 정보를 결합하기 위한 SQL 문법

---

1. Inner Join 이너조인

: 조인하고자 하는 두개의 테이블에서 공통된 요소들을 통해 결합하는 조인 방식
: 값이 일치하는 행을 반환

 

[ 생성 문법 ]

> TSQL 방법

SELECT 별칭.컬럼명, 별칭.컬럼명, 별칭.컴럼명
FROM 테이블명 별칭, 테이블명 별칭

WHERE 별칭.컬럼명 = 별칭.컬럼명 —> 조인조건

 

> ANSI SQL 방법

SELECT 별칭.컬럼명, 별칭.컬럼명, 별칭.컴럼명
FROM 테이블명 별칭, 테이블명 별칭
JOIN  테이블명 별칭 ON 별칭.컬럼명 = 별칭.컬럼명 —> 조인조건

JOIN  테이블명 별칭 USING (컬럼명)—> 조인조건

 

* 조인 이용 시에는 별칭을 권장. 각 테이블에 컬럼명이 중복될 수 있기 때문이다.

 

1) equi-join

: 조인 대상 테이블의 상응하는 컬럼 정보가 정확하게 일치하는 경우에 사용하는 JOIN

(ex. 사원테이블과 부서테이블의 동일한 컬럼명인 부서번호를 이용해 조인)

: WHERE 절에 = 연산자를 사용해 조인조 건을 명시 

 

2) non-equi-join

: 조인 테이블 간의 상응하는 컬럼 정보가 없을 때 사용하는 조인

(ex. 사원 테이블과 월급 등급 테이블은 컬럼에 연관성은 없지만, 사원의 월급 등급을 확인하기 위해 non-equi join을 이용)

: WHERE 절에 > 와 같은 연산자를 이용해 조인 조건을 명시 

 

2. Outer Join 아우터 조인

: 두 테이블의 공통영역을 포함해 한쪽 테이블의 다른 데이터를 포함하는 조인 방식

: 일반적인 조인 조건에 만족하지 않는 경우에도(한쪽 값이 일치하지 않더라도) 다른 행을 조회하기 위해 사용

(INNER JOIN은 테이블 간의 컬럼에 일치되는 값이 없는 경우에는 조회되지 않는다.)

: (+) 연산자는 없는 쪽에 넣어준다.

[ 생성 문법 ]

SELECT 별칭.컬럼명, 별칭.컬럼명, 별칭.컴럼명 
FROM 테이블명 별칭, 테이블명 별칭

WHERE 별칭.컬럼명(+) = 별칭.컬럼명(+) —> 조인조건

or  
FULL / LEFT / RIGHT OUTER JOIN  테이블명 별칭 ON 별칭.컬럼명 = 별칭.컬럼명 —> 컬럼명이 같을 경우

FULL / LEFT / RIGHT OUTER JOIN 테이블명 별칭  USING (컬럼명)—> 조인조건

 

3. Cross Join 크로스  조인

: 특정 기준 없이, 두 테이블간 가능한 모든 경우의 수에 대한 결합을 결과로 보여주는 방식

 

[ 생성 문법 ]

SELECT 별칭.컬럼명, 별칭.컬럼명, 별칭.컴럼명 
FROM 테이블명 별칭, 테이블명 별칭
CROSS  별칭  별칭 —> 조인조건

 

4. Self Join 셀프 조인

: 동일한 테이블이지만 개념적으로 다른 정보를 결합

: 조인을 할 때에 기본 테이블 이외에 참조하는 테이블이 다른 테이블이 아닌 자기 자신 !

 

[ 생성 문법 ]

SELECT 별칭.컬럼명, 별칭.컬럼명, 별칭.컴럼명 
FROM 테이블명 별칭a, 테이블명 별칭b (**같은 테이블이지만 논리적으로 나눈다)
WHERE  a.컬럼명 = b.컬럼명

 

---

기본 JOIN 활용 예제

 

K_EMPLOYEE TABLE

DEPARTMENT TABLE

 

1. 두 테이블을 결합하여 조회해보기 

// 기본

SELECT	E.EMPNO, E.ENAME, E.SAL, E.DEPTNO, D.DEPTNO, D.DNAME, D.LOC
FROM	K_EMPLOYEE E, DEPARTMENT D
WHERE	E.DEPTNO = D.DEPTNO;

//JOIN 이용 (ON)

SELECT  e.empno, e.ename, d.deptno, d.dname
FROM    emp e
JOIN    dept d ON e.deptno = d.deptno

 

2. EMPNO 가 2인 사원의 ENAME과 DNAME을 조회 

SELECT	E.ENAME, D.DNAME
FROM	K_EMPLOYEE E, DEPARTMENT D
WHERE	E.DEPTNO = D.DEPTNO AND E.EMPNO = 2;

 

---

 

DEPT 테이블

 

EMP 테이블

 

SALGRADE 테이블

---

INNER JOIN 활용 예제

 

- join 조건

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

 

1) equi JOIN

- 가장 높은 월급(sal)을 받는 사원 emp의 empno, ename, deptno, loc를 조회

SELECT	e.empno, e.ename, e.deptno, d.loc
FROM	emp e, dept d
WHERE	e.deptno = d.deptno AND e.sal = ( SELECT	max(sal)
					 			 FROM	emp);

 

 

2) non-equi JOIN

emp table	salgrade table
sal	hisal, losal

 

- 두 테이블의 연관성은 사원table의 sal과 salgrade table의 최고액(hisal)과 최저액(losal) 사이에 있다.

SELECT e.ename, s.grade, s.losal, s.hisal
FROM   emp e, salgrade s
WHERE  e.ename = 'SMITH' AND e.sal >= s.losal AND e.sal <= s.hisal;

 

 

---

OUTER JOIN 활용 예제

 

- emp table의 부서명 종류

SELECT DISTINCT (deptno)
FROM   emp;

 

- dept table의 부서명 종류

SELECT DISTINCT (deptno)
FROM   dept;

 

- emp table과 dept table을 기본 join(inner join)했을 경우에 사원이 없는 부서 번호 40에 해당하는 부서정보는 조회되지 않는다

SELECT   e.ename, d.deptno, d.dname
FROM     emp e, dept d
WHERE    e.deptno = d.deptno
ORDER BY e.deptno ASC;

 

- 사원이 없는 부서정보까지 모두 조회하고자 할 때는 OUTER JOIN을 실행한다.

// (+) 연산자 JOIN

SELECT   e.ename, d.deptno, d.dname
FROM     emp e, dept d
WHERE    e.deptno(+) = d.deptno
ORDER BY e.deptno ASC;

//LEFT JOIN

SELECT   e.ename, d.deptno, d.dname
FROM     dept d
LEFT OUTER JOIN emp e ON d.deptno = e.deptno 
ORDER BY e.deptno ASC;

//RIGHT JOIN

SELECT   e.ename, d.deptno, d.dname
FROM     emp e
RIGHT OUTER JOIN dept d ON d.deptno = e.deptno 
ORDER BY e.deptno ASC;

 

---

SELF JOIN 활용 사례

 

- SMITH의(사원번호는 7369) 매니저의 사원 번호(MGR)는 7902이다

SELECT  empno, ename, mgr
FROM    emp
WHERE   empno = 7369;

 

- SMITH의 매니저의 사원 번호(MGR)는 또 다른 사원의 사원 번호 empno(7902)이다.

SELECT  empno, ename, mgr
FROM    emp
WHERE   empno = 7902;

 

 - 현재 사원들의 사원번호 empno, 사원명 ename, 매니저번호 mgr, 매니저명을 조회 (SELF JOIN)

SELECT  e.empno, e.ename, e.mgr, m.empno, m.ename
FROM    emp e, emp m
WHERE   e.mgr = m.empno;

* 위 SQL문은 inner join이기 때문에, 매니저가 없는 사원은 제외되어 13명이 조회된다 (원래는 14명)

 

 - 매니저가 없는 사원까지, 모든 사원들의 사원번호 empno, 사원명 ename, 매니저번호 mgr, 매니저명을 조회 

(SELF JOIN + OUTER JOIN)

SELECT  e.empno, e.ename, e.mgr, m.empno, m.ename
FROM    emp e, emp m
WHERE   e.mgr = m.empno(+);

 

정리 문제

(JOIN, SELF JOIN,  non-equi-join을 이용)

empno가 7369인 사원의 ename과 dname, 매니저명(m.ename), grade(월급 등급)을 조회

SELECT  e.ename, d.dname, m.ename as 매니저명, s.grade as 월급등급
FROM    emp e, dept d, emp m, salgrade s
WHERE   e.empno = '7369' 
        AND e.deptno = d.deptno 
        AND e.mgr = m.empno 
        AND e.sal >= s.losal 
        AND e.sal <= s.hisal