SQL Oracle Join - Equi join 여러 테이블 조인 (공통컬럼 O)

• 조인이 필요한이유?

하나의 테이블에서는 알 수 없는 정보를 다른 테이블과 결합함으로써 다양한 여러 정보를 함께 출력해 낼 수 있도록 합니다.

• 조인을 할때에는 where 절에 조인 조건문을 꼭 작성해 주어야한다.

예제 58 조인 조건문을 이용하여 이름과 지역을 출력하기.

select ename, loc 
from emp, dept 
where emp.deptno = dept.deptno;

---

→ where 절에 꼭 조인조건을 적어주어야한다 (검색조건 아님!!!! )

deptno 의 컬럼은 두 테이블(emp,dept)의 공통된 연결 고리이다. 

이 문장을 조건문으로 적어주는것이 equi join  조인조건이다. 

---

문제 276 이름이 스캇인 사원의 이름, 부서위치 출력

select ename, loc
from emp, dept
where emp.deptno = dept.deptno
     and emp.ename = 'SCOTT';

---

→where 절에 조인조건과, 검색조건은 연결연산자 and 로 연결하여 줄력 할 수 있다.

---

문제 277 월급이 3000 이상인 사원들의 이름, 월급,위치 출력

select ename, sal, loc 
from emp, dept 
where emp.deptno = dept.deptno 
     and sal >=3000;

문제 278 : 위의 결과에서 부서번호도 같이 출력해라.

select ename, sal, loc, emp.deptno 
from emp , dept 
where emp.deptno = dept.deptno 
      and sal >=3000;

---

부서번호는 emp 에도 존재하고 dept 에도 존재한다.

따라서 어디 테이블에서 부서번호를 가져올지 애매하기 때문에 테이블을 정해서 select 절에 입력해 주어야한다. (어떤 테이블을 정해도 상관없다!)

---

but : select 절의 ename, loc 같은 경우에는 각각의 테이블의 고유한 컬럼이기때문에 굳이 테이블 명을 deptno 처럼 적어 주지 않아도 오류나지 않고 출력이 된다.

그래도 ename 과 loc 에 각각의 테이블 명을 적어준다면 검색의 속도가 빨라지고, 유지보수에 좋다.

---

table 별칭 사용하여 문제풀기 

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

 

---

테이블명을 일일히 써주려면 너무 길어지기 때문에 table 에 별칭을 사용해 주어 심플하게 한다.

from 절에서 테이블명을 별칭으로 바꾸어 주기때문에 다음 실행 되는 절에는 무조건 별칭을 써서 표현 해 주어야한다.

---

문제 279 : 달라스에 근무하는 사원들 이름,월급, 부서위치,부서번호 출력

select e.ename, e.sal, d.loc, e.deptno
from emp e, dept d
where e.deptno = d.deptno 
     and d.loc = 'DALLAS';

문제 280 월급이 1000에서 3000 사이인 사원들의 이름과 월급과 부서위치를 출력

select e.ename, e.sal, d.loc 
from emp e, dept d
where e.deptno = d.deptno
    and sal between 1000 and 3000;

문제 282 이름의 첫글짜가 s 로 시작하는 사원들의 이름,직업,월급, 부서위치 출력

select e.ename , e.job, e.sal, d.loc 
from emp e , dept d
where e.deptno = d.deptno
    and e.ename like 'S%'; —————— or and substr(e.ename,1,1) = 'S';

문제 : 우리반 테이블과 telecom_service 를 조인해서, 이름, 나이, 통신사 통신비 출력

select e.ename, e.age, e.telecom, t.price
from emp14 e, telecom_service t 
where e.telecom = t.telecom;

문제 283 (점심시간문제) 이름이 김씨인 학생들의 이름, 나이, 통신사, 통신비 출력

select e.ename, e.age, e.telecom, t.price 
from emp14 e, telecom_service t 
where e.telecom = t.telecom
    and e.ename like '김%';

문제 284 부서위치, 부서위치별 토탈월급 출력

select d.loc, sum(e.sal) as 평균 
from emp e, dept d
where e.deptno = d.deptno —————> 조인문법 
group by d.loc; ——————————> 그룹함수 그루핑

 

---

문제 286 부서위치, 부서위치별 평균월급을 출력하는데 부서위치별 평균월급이 높은것부터 출력하시오

select d.loc, round(avg(e.sal))as 평균
from emp e, dept d
where e.deptno = d.deptno
group by d.loc 
order by 평균 desc;

문제 287 부서위치, 이름, 월급 순위 출력 , 순위는 월급이 높은 순서대로 순위부여

select d.loc, e.ename, e.sal,
        dense_rank() over (order by e.sal desc)as 순위
from emp e, dept d 
where e.deptno = d.deptno;

문제 288 위의 결과를 다시 출력하는데 순위를 출력할때 부서위치별로 ,월급이 높은순으로 각각 순위가 부여되게 하시오

select d.loc, e.ename, e.sal, 
       dense_rank() over (partition by d.loc 
                          order by e.sal desc)as 순위
from emp e, dept d
where e.deptno = d.deptno;

---

각각 순위를 부여하라는 말이 나올때에는 rank () over ( partition by 를 사용해주어야한다~)

분석함수를 사용할때 특정 컬럼으로 각각 결과를 출력하라고 하면 partition by 를 하면돼~

---

문제 289 부서위치, 부서위치별 속한 사원들의 이름을 가로로 출력하고 이름을 abc 순으로 출력하시오

select d.loc, listagg(e.ename,',') within group (order by e.ename asc)as EMPLOYEE
from emp e, dept d 
where e.deptno = d.deptno ————————————>조인문법 
group by d.loc;

---

listagg(_____,',') within group (order by _____ ) !!!! listagg 쓸때 within group 꼭 필수!

---

문제 292 급여 등급별 각각 순위가 부여되게 하시오

select s.grade, e.ename, e.sal,
       rank() over (partition by s.grade
                     order by e.sal desc) as 순위
from emp e, salgrade s 
where e.sal between s.losal and s.hisal; ————————————→ 조인문법

-> non equi join 문법 이다.