[Oracle] SQL - [고급쿼리] 다중행함수-조인(join)

*** 조인(Join)

-- SQL명령에서 사용하는 컬럼이 하나 이상*의 테이블에 존재하는 경우 사용하는 고급쿼리 기법(32개까지 가능)

-- 종류

1. Eque join(inner join)    : 조건에 만족하는 행만* 추출
2. Outer join(left & right) : 조건에 만족하는 행이 추출되고, 마스터 테이블의 모든 행이 함께 추출되는 조인
3. Cross join(데카르트의 곱) : 카티션 프로덕트, table*table, 경우의 수 추출, 조건이 없음
----------------------
4. Self join : 물리적으로 자기 자신의 테이블과 조인되는 경우

 

-- 문법

1. T-SQL 문법 : from절에 테이블 나열형, where절이 조건절
----------------------------
1) 컬럼을 원하는 곳에서 사용
2) 사용된 컬럼을 가진 테이블 목록을 from~(, 구분자) 나열
3) 사용된 테이블간의 관계(PK <= FK)를 조건식으로 표현
----------------------------

2. Ansi 문법 : 사용되는 테이블을 서술형으로 표현, on 조건절
* 관계되는 컬럼명이 같은 경우에만 사용! : 테이블 별칭 불가!, (+)기호 불가!

 

(설명1)

사원명, 부서번호, 부서명을 추출하세요.
-natural join (방법1)
SQL> select saname, deptno, dname from sawon natural join dept;

-using join (방법2)
SQL> select saname, deptno, dname from sawon join dept using(deptno);

-- (방법3)
SQL>  select s.saname, s.deptno, d.dname from sawon s, dept d
            where s.deptno = d.deptno;

-- (방법4)

SQL> select s.saname, s.deptno, d.dname from sawon s join dept d on(s.deptno = d.deptno);

 

(연습1)
--고객명, 전화번호, 담당사원명을 추출. 단, 담당자가 없는 고객도 추출
SQL> select g.goname, g.gotel, s.saname from gogek g, sawon s
           where g.godam = s.sabun(+);                                -- left outer join

 

SQL> select g.goname, g.gotel, s.saname from gogek g 

          left outer join sawon s on(g.godam = s.sabun);

SQL> select s.saname, m.saname from sawon s, sawon m
          where s.samgr = m.sabun(+);

--부서(명)별 급여합계를 추출
SQL> select d.dname,sum(s.sapay) from sawon s , dept d
          where d.deptno = s.deptno(+)
          group by d.dname;

(연습2)
--부서명, 사원명, 직책, 관리자명, 관리자 직책을 추출(단, 관리자가 없는 사원도 추출)
SQL> select d.dname, s.saname, s.sajob, m.saname, m.sajob
          from dept d, sawon s, sawon m
          where s.deptno = d.deptno(+) and s.samgr = m.sabun(+);

--고객명, 전화, 담당자명, 직책, 부서명을 추출(단, 담당자가 없는 고객도 추출)
SQL> select g.goname 고객명, g.gotel 고객전화, s.saname 담당자명, s.sajob 직책, d.dname 부서명
           from dept d, sawon s, gogek g
           where g.godam=s.sabun(+) and s.deptno=d.deptno(+);

SQL> select g.goname 고객명, g.gotel 고객전화, s.saname 담당자명, s.sajob 직책, d.dname 부서명
           from gogek g

           left outer join s on(g.godam=s.sabun) left outer join d on(s.deptno=d.deptno);

--직책별 부서별 급여합계를 추출

직책    영업부   관리부   전산부   총무부     급여합계
-----------------------------------------------------------
과장
대리
사원
부장

select  sa.sajob "직책",
sum(case de.dname when '총무부' then sa.sapay else 0 end) "총무부" ,
sum(case de.dname when '영업부' then sa.sapay else 0 end) "영업부" ,
sum(case de.dname when '전산부' then sa.sapay else 0 end) "전산부" ,
sum(case de.dname when '관리부' then sa.sapay else 0 end) "관리부",
sum(sapay) "급여합계"
from sawon sa , dept de
where sa.deptno = de.deptno
group by rollup(sajob);

 

(연습3)
-- 이순신보다 급여를 많이 받는 사람을 추출
select s2.saname, s2.sapay from sawon s1, sawon s2
where s1.saname='이순신' and s1.sapay < s2.sapay;