[days09] 복합키 / JOIN

복합키

각 튜플들을 식별할 수 있는 두개 또는 그 이상의 속성들로 구성된 후보키

 

사원번호 지급일자    지급급여액  순번
1001     20/01/15         300         1
1002     20/01/15         250         2
1001     20/02/15         310         3

[사원번호 + 지급일자] 복합키 PK

 

create table tbl_pay
(
    empno number(4) --constraint primary key 복합키는 이렇게 안돼 (인라인 안돼)
    ,paydate date   --constraint primary key
    ,pay number(9)
    --테이블레벨
    ,constraint tblpay_empnopaydate_PK primary key(empno, paydate)
);

JOIN

1. 같거나 서로 다른  2개 이상의 테이블에서 컬럼을 검색할 때 사용하는 문 
2. where 절에 join 조건을 작성
3. PK 와 FK 를 이용해서 조인한다. 
   dept 테이블의 PK = deptno
   emp 테이블의 FK = deptno
   예) dept 과 emp 테이블을 조인한다면 조인 조건은 ?    e.deptno = d.deptno

4. JOIN의 종류
  ㄱ. equi join( 이콜조인 ) :     = (같다) 연산자를 where 조인조건절에 사용한다.  
    where e.deptno = d.deptno;  -- equi join
     == NATURAL JOIN
    -->  USING 절을 사용 

  ㄴ.  non-equi join
     - 관계되는 컬럼이 정확히 일치하지 않는 경우에 사용되는 JOIN의 형태이다
     - WHERE 절에 BETWEEN ... AND ... 연산자를 사용한다
     emp/salgrade   join
     where e.sal between s.losal and s.hisal;  -- non-equi join
     
   ㄷ. inner join
       - 둘 이상의 테이블에서 join 조건을 만족하는 행[만] 반환한다.
       -- dept 부서테이블에 40 번부서 미존재
       select *
       from dept d,  emp e
       where d.deptno = e.deptno;  -- equi join , inner join
       
       from dept d  join emp e on d.deptno = e.deptno;
       from dept d inner join emp e on d.deptno = e.deptno;
       
   ㄹ. outer join
     - JOIN 조건을 [만족하지 않는 행]을 보기 위한 추가적인 join의 형태이다
     - '(+)' 연산자를 사용
     
      LEFT [OUTER] JOIN 
      RIGHT [OUTER] JOIN 
      FULL [OUTER] JOIN 

select d.deptno, dname, empno, ename
from emp e join dept d on e.deptno = d.deptno(+); --left outer join 
from emp e join dept d on e.deptno(+) = d.deptno; --right outer join : 왼쪽에 +
from emp e right join dept d on e.deptno = d.deptno;

left join

 

right join

   
   ㅁ. self join   
      - 자신의 테이블을 alias를 사용하여 마치 두 개의 테이블인처럼 JOIN하는 형태이다.      
      from emp e1, emp e2 where 조인조건
      from emp e1 join emp  e2 on  조인조건
   
   ㅂ.  cross join
   
   
   ㅅ. ANTIJOIN     :     not in 연산자를 사용한 쿼리.
   
   ㅇ. SEMIJOIN     :   exists 연산자를 사용한 쿼리
    
 -- [JOIN]
 --  RDBMS = table 관계 table
 -- 조회                JOIN   
 -- 1. 2. 3. 4. 5. 6. 7. 8. 

 

 

 

1) 조인 첫번째 
select emp.deptno, dname, empno, ename, hiredate
from emp, dept; --조인조건 없음 48개 나옴  -> 크로스조인, 데카르트곱, Cartition product


select emp.deptno, dname, empno, ename, hiredate
from emp, dept
where dept.deptno=emp.deptno; --PK=FK
--이콜조인, 이너조인


2) 조인 두번째
select e.deptno, dname, empno, ename, hiredate
from emp e, dept d
where d.deptno = e.deptno;


3)SQL표준 JOIN~ON구문
select e.deptno, dname, empno, ename, hiredate
from emp e join dept d on d.deptno = e.deptno;


4) USING문을 사용해 조인 - using절을 사용하여 이퀄조인과 동일한 결과 출력
select deptno, dname, empno, ename, hiredate --여기도 e.deptno안쓴다 알리아스
from emp e join dept d using(deptno); --using문에서는 alias 안쓴다


5) 오라클에만 있는 natural 조인 (equi join과 동일)
select deptno, dname, empno, ename, hiredate
from dept natural join emp; --조건 없는데도 나오네
where deptno=30;

book, danga, gogaek, panmai, au_book
문제) [책ID, 책제목, 판매수량, 단가], 서점명, 판매금액 조회
--       b_id, title, p_su, price,   g_name, p_su*price
--book: b_id, title
--panmai: p_su
--gogaek: g_name
--danga: price

select  b.b_id, title, p_su, price,   g_name, p_su*price qty
from book b, panmai p, gogaek g, danga d
where b.b_id = p.b_id and b.b_id=d.b_id and g.g_id = p.g_id;

--위 코딩을 JOIN ON 사용
select  b.b_id, title, p_su, price, g_name, p_su*price qty
from book b join panmai p on b.b_id = p.b_id
             join gogaek g on g.g_id = p.g_id join  danga d on  b.b_id=d.b_id;
             
--위 코딩을 USING절 사용 (앞으로는 쓸 일 없음)
select  b_id, title, p_su, price, g_name, p_su*price qty
from book join panmai using(b_id)
             join gogaek using( g_id) 
             join  danga using( b_id);

문제) 각각의 책들 총 판매권수 조회 (책ID, 책제목, 단가, 총판매수 qty)

select b.b_id, title, sum(p_su) qty
        ,(select sum(p_su) from panmai) total_qty
from panmai p join book b on b.b_id = p.b_id
              join danga d on b.b_id =d.b_id 
group by b.b_id, title, price;

번외 문제) 각 부서별 사원수 deptno, dname, count(*)

select d.deptno, dname, nvl(cnt,0)
    ,(select count(*) from emp) total_cnt
    ,round(cnt/(select count(*) from emp)*100,2)||'%'
from(
select deptno, count(*) cnt
from emp
group by deptno
--)t join dept d on t.deptno = d.deptno;
--40번 부서도 나오면 좋겠다 > outer join
--)t right outer join dept d on t.deptno = d.deptno;
)t full outer join dept d on t.deptno = d.deptno 
order by deptno;