Database-Join

 

[다중 table]

Join

기본 예시 테이블 생성

create table M (M1 char(6) , M2 char(10));
create table S (S1 char(6) , S2 char(10));
create table X (X1 char(6) , X2 char(10));

insert into M values('A','1');
insert into M values('B','1');
insert into M values('C','3');
insert into M values(null,'3');
commit;

insert into S values('A','X');
insert into S values('B','Y');
insert into S values(null,'Z');
commit;

insert into X values('A','DATA');
commit;

select * from m;
select * from s;
select * from x;

equi join

-- 원테이블과 대응되는 테이블에 있는 컬럼의 데이터를 1:1 매핑
-- [ANSI 문법]  : 테이블 join 테이블 on ~조건절 , [inner] 테이블 join  테이블 on ~조건절
select m.m1,m.m2,s.s2 -- oracle join 문법(권장X)
from m,s
where m.M1 = s.S1;
-- 어쩐 것이 join 조건 절인지 from의 조건절인지 모호하다.
select m.m1,m.m2,s.s2 -- ANSI 문법(권장)
from m join s
on m.m1 = s.s1;

--조인 요령(select * 한 후에 필요한 컬럼만 추출하면 된다.)
select emp.EMPNO,emp.ename,emp.DEPTNO,dept.DNAME
from emp join dept
on emp.deptno = dept.deptno;

--테이블에 가명칭 부여(테이블 이름 길이가 길때 테이블 이름 뒤에 가명칭 부여한다.
select e.EMPNO,e.ename,e.DEPTNO,d.DNAME
from emp e join dept d
on e.deptno = d.deptno;

select s.s1 , x.X2
from s join x
on s.S1 = x.X1;

--여러개의 table join
select m.m1,m.m2,s.s2,x.X2         --------oracle이다.(권장 X)
from m , s , x
where m.m1 = s.s1 and s.s1 = x.x1;

select m.m1,m.m2,s.s2,x.X2         -------- ANSI이다.(권장)
from m join s on m.m1 = s.S1
            join x on s.s1 = x.x1;
            
            
--1.HR 계정으로 이동
select * from employees;
select * from departments;
select * from locations;

--employess, departments
--1. 사번,이름(last_name),부서번호,부서이름을 출력
select e.EMPLOYEE_ID , e.LAST_NAME , e.DEPARTMENT_ID , d.department_name
from EMPLOYEES e join departments d
on e.department_id = d.department_id
order by e.EMPLOYEE_ID; --------------------------- null값이 하나 있어서 사원수 한명 줄게 나온다. 등가 조인으로는 해결이 안된다.

select * from employees where department_id is null; 

--2.사번,이름,부서번호,지역코드,도시명를 출력
select e.EMPLOYEE_ID , e.LAST_NAME , e.DEPARTMENT_ID,d.DEPARTMENT_NAME,l.LOCATION_ID,l.CITY
from EMPLOYEES e join DEPARTMENTS d on e.DEPARTMENT_ID = d.DEPARTMENT_ID 
          join LOCATIONS l on d.LOCATION_ID = l.LOCATION_ID;

non-equi join

--의미만 존재 => 문법 등가조인 1:1 매핑이 안될때 
--**** between을 사용한다.
--사번,이름,급여,급여등급
select e.empno , e.ename , s.grade , e.SAL
from emp e join salgrade s
on e.sal between s.losal and s.hisal;

--사번,이름,부서번호,부서명,급여,급여등급
select e.EMPNO,e.ENAME,e.DEPTNO,d.DName,e.SAL,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;
------------------join                on
------------------join                on       무제한으로 확장 가능

outer join(equi join + null)

-- outer join (두개의 테이블에서 주 , 종 관계 파악)
-- 문법 : left outer join  (왼쪽 주 , 오른쪽 종)
--       right outer join  (오른쪽 주 , 왼쪽 종)
--       full outer join   (left , right join > union 하면)
/*
outer join(equi join + null(남는 데이터)) =>> equi join 
outer join (두개의 테이블에서 주,종 관계 파악) >> 주인의 남는 데이터는 다 null을 쓴다. >>주인이 누구냐에 따라 다르다.

문법: 
left outer join(왼쪽이 주인)
right outer join(오른쪽이 주인)
full outer join(left,right join > union하면)

내부적으로 등가조인을 실행하고(주,종)관계를 파악해서
주인 되는 테이블에 있는 남는 데이터를 가지고 오는 것
*/
select*
from m join s
on m.M1 = s.s1;

select*
from m left outer join s
on m.m1 = s.s1;

select*
from m full outer join s
on m.m1 = s.s1;

select e.EMPLOYEE_ID , e.LAST_NAME , e.DEPARTMENT_ID , d.department_name
from EMPLOYEES e left outer join departments d
on e.department_id = d.department_id
order by e.EMPLOYEE_ID;  --사원수 107명

self join(자기참조)

-- 의미만 존재 => 문법 등가조인 : 테이블에 가명칭을 부여하여 사용
-- ex) emp 테이블에서 smith 관리자 이름은 무었입니까
-- 하나의 테이블안에서 컬럼이 다른 컬럼을 참조하는 경우
select * from emp;

select e.empno ,e.ENAME , m.EMPNO,m.ENAME
from emp e left outer join emp m ---self join 은 하나의 테이블을 두개 처럼 사용하는 것(테이블에 가명칭을 부여해서)
on e.MGR = m.EMPNO;

--사원은 14명인데 결과 13명 >> 사장님은 MGR이 null이라서

--시험
/*
select       5
from         1
where       2
group by   3
having       4
order by    6
*/

Example Quiz

-- 1. 사원들의 이름, 부서번호, 부서이름을 출력하라.
select e.ename , e.DEPTNO , d.dname
from emp e join DEPT d
on e.DEPTNO = d.DEPTNO;
 
-- 2. DALLAS에서 근무하는 사원의 이름, 직종, 부서번호, 부서이름을
-- 출력하라.


select e.ename , e.job , e.deptno,d.dname
from emp e join dept d 
on e.deptno = d.deptno
where d.loc = 'DALLAS';



 
-- 3. 이름에 'A'가 들어가는 사원들의 이름과 부서이름을 출력하라.
select emp.ENAME,dept.DNAME
from emp join dept on emp.DEPTNO = dept.DEPTNO
where emp.ENAME LIKE '%A%' ;



-- 4. 사원이름과 그 사원이 속한 부서의 부서명, 그리고 월급을
--출력하는데 월급이 3000이상인 사원을 출력하라.
select emp.ENAME,dept.DNAME,emp.SAL
from emp join dept on emp.DEPTNO = dept.DEPTNO
where emp.sal >=3000;
 
-- 5. 직위(직종)가 'SALESMAN'인 사원들의 직종 그 사원이름, 그리고
-- 그 사원이 속한 부서 이름을 출력하라.
select emp.job ,emp.ENAME , dept.DNAME
from emp join dept on emp.DEPTNO = dept.DEPTNO
where emp.job = 'SALESMAN';

 
-- 6. 커미션이 책정된 사원들의 사원번호, 이름, 연봉, 연봉+커미션,
-- 급여등급을 출력하되, 각각의 컬럼명을 '사원번호', '사원이름',
-- '연봉','실급여', '급여등급'으로 하여 출력하라.
--(비등가 ) 1 : 1 매핑 대는 컬럼이 없다
select e.EMPNO as "사원번호",e.ENAME as "사원이름", e.SAL as "연봉", e.sal || e.COMM as "실급여" , s.GRADE as "급여등급"
from emp e join salgrade s on e.sal between s.losal and s.hisal;

 
-- 7. 부서번호가 10번인 사원들의 부서번호, 부서이름, 사원이름,
-- 월급, 급여등급을 출력하라.
select * from salgrade;

select d.DEPTNO,d.DNAME,e.ENAME,e.sal,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
where e.deptno = 10;
 
 
-- 8. 부서번호가 10번, 20번인 사원들의 부서번호, 부서이름,
-- 사원이름, 월급, 급여등급을 출력하라. 그리고 그 출력된
-- 결과물을 부서번호가 낮은 순으로, 월급이 높은 순으로
-- 정렬하라.
select dept.DEPTNO,dept.DNAME,emp.ENAME,emp.SAL,salgrade.grade
from emp join dept on emp.DEPTNO = dept.DEPTNO
               join SALGRADE on emp.sal between salgrade.
group by dept.deptno , emp.sal
having dept.deptno = 10 or dept.deptno = 20
order by dept.deptno asc , emp.sal desc;
 
 
-- 9. 사원번호와 사원이름, 그리고 그 사원을 관리하는 관리자의
-- 사원번호와 사원이름을 출력하되 각각의 컬럼명을 '사원번호',
-- '사원이름', '관리자번호', '관리자이름'으로 하여 출력하라.
--SELF JOIN (자기 자신테이블의 컬럼을 참조 하는 경우)
select e.EMPNO as "사원번호",e.ENAME as "사원이름", m.EMPNO as "관리자 번호", m.ENAME as "관리자 이름"
from emp e join emp m
on e.EMPNO = m.MGR;