8일차(2)/DB_Oracle(4) : JOIN

 

*JOIN문의 종류와 작성법

1. JOIN (= INNER JOIN)

2. SELF JOIN

3. OUTER JOIN 

 

 

1. JOIN : 하나의 테이블로 원하는 칼럼정보를 참조할 수 없는 경우, 관련된 테이블을 논리적으로 결합하여 원하는 칼럼 정보를 참조하는 방법

 

[JOIN문 작성법]
SELECT 칼럼명1,칼럼명2, ...
FROM 테이블명1, 테이블명2, ...
WHERE JOIN 조건1 AND 조건2, ...

 

 

예제) emp테이블의 모든 사원들의 이름, 부서번호, 부서명 출력

- dept라는 테이블과 emp라는 테이블에 deptno(부서명)이 일치함.

 두 테이블의 내용을 조합시켜 ename에 해당하는 dname을 찾는 예제.

 

SELECT ename, deptno, dname
FROM emp, dept;
: 오류 발생. 열의 정의가 애매하다. (deptno가 어느 테이블의 칼럼인지)

SELECT ename, emp.deptno, dept.deptno, dname
FROM emp, dept;

: 48개 행 추출 → 가능한 모든 경우의 수를 계산한 것 (emp의 row 12개 * dept의 row 4개)

 

- 하지만 다른 조합은 필요가 없다.

 emp.deptno=dept.deptno 에 해당하는 row만 골라내야 한다.(필요없는 row의 배제)

 

SELECT ename, emp.deptno, dept.deptno, dname
FROM emp, dept

WHERE emp.deptno=dept.deptno;

 

WHERE절을 추가하면 deptno 값이 같은 12개의 열만 추출된다.

여기서의 WHERE절을 조인 조건(Join conditon) 이라고 부른다.

 

 

예제) emp테이블의 모든 사원들의 이름, 부서번호, 부서명 출력 + 급여 3000~5000 사이인 직원만

SELECT ename, emp.deptno, dname
FROM emp, dept

WHERE emp.deptno=dept.deptno AND sal BETWEEN 3000 AND 5000;

 

- JOIN 조건과 일반조건을 함께 사용

 

---

 

- 비교하는 칼럼명이 같은 경우 SELECT문을 세 가지 방법으로 작성해볼 수 있음.

 

예제) 부서명이 'ACCOUNTNG' 인 사원의 이름,입사일,부서번호,부서명을 출력

 → SELECT 이름,입사일,부서번호,부서명 / FROM emp,dept 테이블 /

    WHERE emp,dept 테이블의 deptno 칼럼 / AND 추가조건(부서명)

 

1) 기본 조건절 작성법

SELECT ename, hiredate, emp.deptno, dname (칼럼이름이 같을경우 칼럼에 테이블명 명시)

FROM emp, dept

WHERE emp.deptno = dept.deptno

AND dname = 'ACCOUNTING' ;

 

2) ANSI JOIN 표현식 : INNER JOIN 테이블명 ON 칼럼명1=칼럼명2 (★다시 확인)

SELECT ename, hiredate, emp.deptno, dname (칼럼에 테이블명 명시)
FROM emp
INNER JOIN dept ON emp.deptno=dept.debtno
WHERE dname='ACCOUNTING'

3) USING문 사용 : USING(칼럼명) 

SELECT ename, hiredate, deptno, dname (테이블명 명시하지 않아도 됨! 어차피 같은 이름의 칼럼이므로)
FROM emp
JOIN dept USING(deptno)
WHERE dname='ACCOUNTING'

- JOIN은 기본이 INNER JOIN이기때문에 INNER는 생략 가능.

- USING문 JOIN dept USING(deptno) 는 

 'emp와 dept라는 테이블을 조인하되, deptno라는 칼럼을 사용해서 조인하라.' 라고 생각하면 쉽다.

- USING절은 JOIN 조건에 비교하는 양쪽 테이블의 칼럼 이름이 같으면 사용 가능
 이외에는 사용할 수 없다!!!

 

- emp, dept 두 테이블의 정보를 모아 위와 같이 조건에 맞는 값을 추출할 수 있음

 

---

 

[참고] 테이블에도 별칭(alias) 주기 가능!

SELECT ename, hiredate, d.deptno, dname
FROM emp e, dept d
WHERE e.deptno=d.deptno
AND dname='ACCOUNTING';

- FROM절에서 별칭을 지정하면 진행순서상 모든 코드에 적용가능

 

---

 

예제) 커미션이 null 이 아닌 사원의 이름, 입사일, 부서명을 출력하기

 → SELECT 사원명, 입사일, 부서명 / FROM emp, dept 테이블 / 

    WHERE emp,dept테이블의 deptno 칼럼 / AND comm이 null이 아님

1) SELECT ename, hiredate, dname

FROM emp, dept

WHERE emp.deptno=dept.deptno

AND comm IS NOT NULL;

 

2) SELECT ename, hiredate, dname

FROM emp

INNER JOIN dept ON emp.deptno=dept.deptno

WHERE comm IS NOT NULL;

 

3) SELECT ename, hiredate, dname

FROM emp

JOIN dept USING(deptno)

WHERE comm IS NOT NULL;

 

3가지 방식으로 모두 입력할 수 있도록 연습하기!

 

---

 

2. SELF JOIN :참조해야 할 칼럼이 자신의 테이블에 있는 경우에 사용하는 JOIN

 

예제) 각 사원의 이름과 담당 매니저 이름을 출력하기

 → SELECT 사원명, 매니저명(사원명) / FROM emp 테이블 / WHERE 매니저번호, 매니저명(사원명)

 

- 사원명별 매니저 코드를 추출하고, 매니저 코드와 사원명을 비교하여 일치하는 값을 도출한다.

- 추출된 테이블을 emp1, emp2로 구분하기

- JOIN emp1.mgr = emp2.empno

 

1) 기본 표현식

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

 

- NULL값이 있어서 11개 행만 도출된다.

 

2) ANSI JOIN 표현식

SELECT e1.ename, e2.ename
FROM emp e1
JOIN emp e2 ON e1.mgr=e2.empno;

 

- 진행순서를 생각하며 테이블에 e1, e2라는 별칭 부여하기!

 

참고**) 만약 KING(값이 NULL인 사원)도 나오게 하고싶다면 LEFT OUTER JOIN 사용

* LEFT OUTER JOIN식 (아래 OUTER JOIN 참조)

SELECT e1.ename, e2.ename
FROM emp e1
LEFT OUTER JOIN emp e2 ON e1.mgr=e2.empno

 

- NULL값(빈칸)이 오른쪽에 있으므로, 왼쪽이 더 튀어나오는 데이터라고 생각하면 알기 쉽다.

 

SELECT e1.ename, NVL(e2.ename, 'none')
FROM emp e1
LEFT OUTER JOIN emp e2 ON e1.mgr=e2.empno;

 

- KING의 매니저명 값을 빈자리로 두지않고 none을 넣고싶다면 NVL(e2. ename, 'none')를 넣으면 된다.

 

---

 

3. OUTER JOIN : 한쪽 테이블에는 해당하는 데이터가 존재하는데 다른 테이블에는 데이터가 존재하지 않을 경우에도 모든 데이터를 추출하도록 하는 JOIN 방법

 

예제) 사원번호,부서번호,부서명을 출력. 단, 사원이 근무하지 않는 부서명도 같이 출력하기.

 → SELECT 사원번호,부서번호,부서명 / FROM emp테이블 / deptno 칼럼 사용

SELECT empno, deptno, dname
FROM emp
INNER JOIN dept USING(deptno);

- 기본 INNER JOIN으로 추출했을 경우, 40번 부서(OPERATIONS)는 근무자가 없어서 표에 나타나지 않는다.

 

SELECT empno, deptno, dname
FROM emp
RIGHT OUTER JOIN dept USING(deptno);

- 왼쪽 데이터가 null이고 오른쪽 데이터가 튀어나오므로 RIGHT OUTER 사용!

- OUTER JOIN을 사용하면 emp 테이블에 해당하는 데이터가 없더라도 dept 테이블의 값을 출력할 수 있다.

 

SELECT e.empno, d.deptno, d.dname

FROM emp e, dept d

WHERE e.deptno(+) = d.deptno;

- 기본 WHERE 조건문으로 작성했을 때

 NULL값이 있는 칼럼 쪽에 (+)를 붙여 RIGHT OUTER JOIN과 같은 결과값을 가져올 수 있다.

 

---

예제1)

1. emp 테이블과 dept 테이블을 조인하여 부서번호,부서명,이름,급여 를 출력
1) SELECT e.deptno, d.dname, e.ename, e.sal
FROM emp e, dept d
WHERE e.deptno=d.deptno;

2) SELECT dept.deptno, dname, ename, sal
FROM emp
INNER JOIN dept ON emp.deptno=dept.deptno;

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

 

---

2. 사원의 이름이 'ALLEN' 인 사원의 부서명을 출력
1) SELECT e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno
AND e.ename='ALLEN'; (일반조건을 where절에 같이 적은것)
 
2) SELECT ename, dname
FROM emp
INNER JOIN dept ON emp.deptno=dept.deptno
WHERE ename='ALLEN'; 

3) SELECT ename, dname
FROM emp
JOIN dept USING(deptno)
WHERE ename='ALLEN';  

 

---

 

3. 모든 사원의 이름, 부서번호, 부서명, 급여를 출력하세요. 단, emp 테이블에 없는 부서도 출력
1) SELECT ename, dept.deptno, dname, sal
from emp, dept
where emp.deptno(+)=dept.deptno;

2) SELECT ename, dept.deptno, dname, sal
FROM emp
INNER JOIN dept ON emp.deptno(+) = dept.deptno

(ANSI JOIN이 아닌 경우, 1열 deptno 칼럼의 정의가 애매하기때문에 dept.를 명시한다.)

3) SELECT ename, deptno, dname, sal
FROM emp
RIGHT OUTER JOIN dept USING(deptno);
(오른쪽이 튀어나오는 조인) ★다시 확인

 

---

 

4. 다음과 같이 모든 사원의 매니저를 출력 / "SMITH 의 매니저는 FORD 입니다."
1) SELECT e1.ename || ' 의 매니저는 ' || e2.ename || ' 입니다'  info(칼럼이름 바꿔주기)
 FROM emp e1,emp e2
 WHERE e1.mgr = e2.empno;
→ 11개 행 도출 (KING은 매니저 값이 없음)

2) SELECT e1.ename || ' 의 매니저는 ' || e2.ename || ' 입니다'  info
FROM emp e1
JOIN emp e2 ON e1.mgr=e2.empno

3) SELF JOIN이므로 같은 테이블을 e1, e2로 이름을 부여함. USING 절은 쓸 수 없다.

 

참고**) KING도 결과 테이블에 나오게 하려면 매니저가 우측에 있으므로 왼쪽 칼럼이 튀어나온다.
SELECT e1.ename || ' 의 매니저는 ' || e2.ename || ' 입니다'  info
FROM emp e1
LEFT OUTER JOIN emp e2 ON e1.mgr=e2.empno

 

---

 

(급여별로 들어가있는 grade 표)

 

 

5. 사원명, 급여, 급여의 등급을 출력

 - JOIN 조건: sal BETWEEN losal AND hisal ★

1) SELECT ename, sal, grade
FROM emp, salgrade
WHERE sal BETWEEN losal AND hisal;
(칼럼 앞에 테이블명은 애매할 때만 붙인다. ename, sal은 emp에만 있고 grade는 salgrade에 있어서 구분 잘됨) 

2) SELECT ename, sal, grade
FROM emp, salgrade
JOIN salgrade ON sal BETWEEN losal AND hisal;

- USING절은 쓸 수 없다.(비교하는 칼럼명이 다르므로)

---

6. 사원명, 부서명, 급여의 등급을 출력
1) SELECT ename, dname, grade
FROM emp, dept, salgrade
WHERE emp.deptno=dept.deptno  (join조건1)
AND sal BETWEEN losal AND hisal; (join조건2)

2) SELECT ename, dname, grade
FROM emp
JOIN dept USING(deptno)
JOIN salgrade ON sal BETWEEN losal AND hisal
★다시 확인