[코딩] Circle Square

8. 조인(join) : 여러 테이블을 연결해서 필요한 데이터를 조회하는 방법
    - 오라클 조인 : 여러테이블 연결조건을 where절에 정의
    - ANSI 조인(표준)

    1) 개요 : 정규화된 여러 테이블의 데이터를 이용해서 데이터를 조회해야하는 경우 테이블 조인을 한다.
    - 조인은 관계형 데이터 베이스에서의 중요기능.
    - 기본키와 외래키 관계를 이용해서 테이블을 조인해야한다.
    - 조인을 하는 경우 반드시 조인 조건 정의.
  
    2) 조인 사용방법
    - from절에 테이블을 정의할 때 alias를 이용해서 정의.

select d.dname, e.ename, e.sal
from emp e, dept d
where e.deptno=d.deptno and sal>2000;

select e.empno, e.ename, e.job, e.deptno, d.deptno, d.dname
from emp e, dept d
where e.deptno=d.deptno;

select d.deptno, d.dname, l.city
from dept d, locations l
where d.loc_code=l.loc_code;

     -> deptno : 기본키

  - select절에서 두 개 이상의 테이블에 있는 칼럼을 추가하는 경우 from절에서 정의한 alias를 이용해서 칼럼의 모호성 제거.
  - where절에서는 반드시 조인조건을 정의해야한다. 조인을하면 외래키 테이블의 외래키와 기본키 테이블의 기본키를 비교하고 일치하는 레코드의 원하는 값을 가져오므로 조건을 정의하지않으면 데이터를 조회할 수 없다.
  - 사용되는 모든 테이블의 조인조건을 정의해야한다. (테이블이 세개면 조인 조건은 두개 정의)

select e.ename, d.dname, e.sal, l.city
from emp e, dept d, locations l
where e.deptno=d.deptno and d.loc_code=l.loc_code;

    - 검색 조건 추가
      where 조인조건
      and 조건추가

select e.empno, e.ename, e.sal, d.dname, d.loc_code
from emp e, dept d
where job='SALESMAN'
and e.deptno=d.deptno;

select ename, sal, hiredate
from emp e, dept d, locations l
where l.city = 'SEOUL'
        and e.deptno=d.deptno and d.loc_code=l.loc_code;

select e.ename, e.sal, e.job, e.hiredate, e.comm
from emp e, dept d, locations l
where e.deptno=d.deptno and d.loc_code=l.loc_code
and l.city='DALLAS' and e.sal>=1500;

select d.department_name, count(e.employee_id)
from employees e, departments d
where e.department_id = d.department_id
group by department_name;

select e.first_name || e.last_name || '의 연봉은 ' || e.salary
|| ' 입니다.' as 결과
from employees e,departments d
where e.department_id = d.department_id
and d.department_name = 'IT'
order by salary asc;

select e.employee_id, e.first_name, j.job_title, d.department_name
from employees e, departments d, locations l, jobs j
where e.department_id=d.department_id
and d.location_id = l.location_id
and e.job_id=j.job_id
and l.city ='Seattle';

select j.job_title job, sum(e.salary) 급여
from employees e, jobs j
where j.job_title not like '%Representative%'
      and j.job_id=e.job_id
group by j.job_title
having sum(e.salary)>30000
order by sum(e.salary);

select d.department_name 부서명, count(e.department_id) 인원수
from employees e, departments d
where e.department_id = d.department_id
      and hire_date <'2005-1-1'
group by d.department_name;

select d.department_id 부서번호, d.department_name 부서명, count(e.employee_id) 인원수,
 max(e.salary) 최고급여, min(e.salary) 최저급여, floor(avg(e.salary)) 평균급여, sum(e.salary) 급여총액
from employees e, departments d
where e.department_id = d.department_id
group by d.department_id, d.department_name
having count(e.department_id)>=3
order by 인원수 desc;

select j.job_title job, sum(e.salary) 급여
from employees e, jobs j
where j.job_title not like '%Representative%'
      and j.job_id=e.job_id
group by j.job_title
having sum(e.salary)>30000
order by sum(e.salary);

select d.department_name, floor(avg(salary)) 평균연봉
from employees e, departments d
where e.department_id=d.department_id
group by d.department_name
having avg(salary)>= 5000
order by 평균연봉 desc; 

  3) 조인의 종류(oracle 조인)
   ① Equip 조인 : 두개 이상의 테이블에서 칼럼 값이 정확하게 일치하는 경우 조회.
    조인조건 : where 기본테이블.기본키 = 외래키테이블.외래키
        (테이블은 alias 사용가능)

select e.ename d.dname
from emp e, dept d
where e.deptno = d.deptno

   ② outer 조인
   - 조인 적용했을 때 조인 조건을 만족하지않는 데이터를 조회하고 싶을때 사용.
   - (+) 연산자를 한쪽 칼럼에 초가해서  사용.
   - 만족하지 않아도 한쪽테이블의 모든 데이터를 조회해서 볼 수 있도록 자원.
   - (+)가 추가되면 만족되지 않는 조건을 임의로 추가해서 비교하므로 (+)가 투가되지않은 테입르의 레코드가 

     출력된다.
   [구문]

select 테이블명1(alias1명).칼럼며으 테이블2 alias2
from 테이블명1 alias1, 테이블명2 alias2
where 테이블명1(alias1).칼럼명(+) = 테이블명2(alias).칼럼명(+)

    - dept table에 null을 추가하여 emp table과 비교하여 emp table의 null data를 조회한다.

    - Equip 조인은 조인문에 detno가 일치하지않거나 null인 경우 조회되지않는다.

select e.empno, d.dname, e.ename, e.sal
from emp e, dept d
where e.deptno = d.deptno(+);

select nvl(m.ename,'관리자없음') 관리자명, count(e.empno) 인원수
from emp e, emp m
where e.mgr=m.empno(+)
group by m.ename;

select j.job_title, count(e.employee_id)
from employees e, jobs j
where e.job_id=j.job_id(+)
group by j.job_title;

    - emp table에 null을 추가하여 dept table과 비교하여 dept table의 null data를 조회한다.

select e.empno, d.dname, e.ename, e.sal
from emp e, dept d
where e.deptno(+) = d.deptno;

select d.department_name, count(e.employee_id)
from employees e, departments d
where e.department_id(+)=d.department_id
group by d.department_name
order by d.department_name;

   ③ non-Equip 조인(등급표 조인)
    - 두테이블에서 비교해야하는 칼럼 값이 정확하게 일치하지않고 사이 값인 경우 조인하는 방법. =연산자를 

      사용하지않은 조인.

select e.empno, e.sal, g.grade
from emp e, salgrade g
where e.sal between g.losal and g.hisal;

   ④ self 조인
    - 같은 테이블에서 조인하는 경우.
    - 하나의 테이블의 다른 칼럼을 가지고 조인하며 서로 다른 테이블인 것처럼 작업할 수 있다.
    - 조인조건은 equip조인과 동일하게 정의

select e.empno 사원번호, e.ename 사원명, e.mgr 관리자코드, m.ename 관리자명
from emp e, emp m
where e.mgr=m.empno;

select e.employee_id, e.first_name, nvl(m.first_name,'관리자 없음') 관리자명
from employees e, employees m
where e.manager_id = m.employee_id(+)
      and e.first_name like '_t%';

select e.first_name, e.salary
from employees e, employees m
where e.manager_id = m.employee_id
      and e.salary>m.salary;