sql중급 / equi join, non equi join, outer join, self join

058 여러 테이블의 데이터를 조인해서 출력하기 1(EQUI JOIN)

조인(join)이 필요한 이유 : 하나의 테이블에서는 알 수 없는 정보를 다른 테이블과 조인함으로써 알아낼 때 사용

emp table ----- 조인(join)--------dept table

ex) KING은 어느 부서위치에서 근무하는가?

ALLEN은 어느 부서명에서 근무하는가?

위와 같은 질문의 답은 emp table에서 알 수 없음 > 다른 데이터를 가지고 조인해야 알 수 있음

-- 이름과 부서위치를 출력하세요
select ename, loc
 from emp, dept ;
 /* 에러
  두 개의 테이블을 조인하기 위해서 where절에 조인 조건을 넣어줘야 함
  조인하기 위해서는 공통된 데이터로 조인해야함 */
 
select ename, loc
 from emp, dept
 where emp.deptno = dept.deptno;
 -- emp.deptno = dept.deptno > 조인 조건

 

문제276. 이름이 SCOTT인 사원의 이름과 부서위치를 출력하시오

select ename, loc
 from emp, dept
 where emp.deptno = dept.deptno and ename = 'SCOTT';
 --            조인조건          and     검색조건

문제277. 월급이 3000 이상인 사원들의 이름과 월급과 부서위치를 출력하시오

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

문제278. 위의 결과를 다시 출력하는데 부서번호도 같이 출력되게 하시오

select ename, sal, loc, deptno
 from emp, dept
 where emp.deptno = dept.deptno and sal >= 3000;
 /* 에러메시지 : 열의 정의가 애매합니다 
 emp테이블에도 dept테이블에도 deptno데이터가 있다보니 어느 테이블에서 가져와야 할 지 모르는 것
 어떤 테이블에서 deptno데이터를 가지고 올 것인지 정의해줘야함 */
 
select ename, sal, loc, emp.deptno
 from emp, dept
 where emp.deptno = dept.deptno and sal >= 3000;
 /* ename, sal, loc도 앞에 테이블명을 붙여줘야되지만
  붙이지 않아도 출력되는 이유는 컬럼이 중복되지 않기 때문.
  조인문의 검색 성능을 위해서는 반드시 컬럼명 앞에 "테이블명."을 붙여주는게 좋음.
  
  하지만, "테이블명.컬럼명"을 다 붙여주려다 보면 SQL코딩길이가 길어짐
  > 테이블명 별칭 사용 */
  
select e.ename, e.sal, e.deptno, d.loc
 from emp e, dept d 
 where e.deptno = d.deptno and e.sal >= 3000;
 -- emp테이블의 별칭 e, dept테이블의 별칭 d

문제279. DALLAS에서 근무하는 사원들의 이름, 월급, 부서위치, 부서번호를 출력하시오

select e.ename, e.sal, e.deptno, d.loc
 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
       e.sal between 1000 and 3000;

문제281. 이름의 첫글자가 S로 시작하는 사원들의 이름과 직업과 월급과 부서위치를 출력하시오

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

 

ㅁ우리반 테이블 통신사 데이터를 모두 소문자로 변경합니다

update emp14
 set telecom = lower(telecom);

commit;

우리반 테이블 통신사 skt를 sk로 변경

update emp14
 set telecom = 'sk'
 where telecom = 'skt';
 
commit;

통신사, 통신사별 인원수 출력하시오

select telecom, count(*)
 from emp14
 group by telecom;

create table telecom_service
( telecom      varchar2(10),
  price        number(10),
  add_service  number(10) );
  
insert into telecom_service  values('sk', 18000, 5);
insert into telecom_service  values('lg', 17000, 4);
insert into telecom_Service  values('kt', 16000, 4);

commit;

 

문제282. 우리반 테이블, 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 deptno, sum(sal)
 from emp
 group by deptno;

문제285. 부서위치, 부서위치별 토탈월급을 출력하시오

select d.loc, sum(e.sal)
 from dept d, emp e
 where d.deptno = e.deptno
 group by d.loc;
 -- 부서위치는 dept테이블에 있고 월급은 emp테이블에 있으므로 조인해서 출력해야함

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

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

문제287. 부서위치, 이름, 월급, 순위를 출력하는데 순위가 월급이 높은 순서대로 부여하여 출력하시오

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

문제288. 위의 결과를 다시 출력하는데 순위를 출력할때 부서위치별로 각각 순위가 부여되게 하시오.

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

 

ㅁ 분석함수를 사용할 때 특정 컬럼으로 각각 결과를 출력하라고 하면 group by절이 아닌 (partition by)를 써야함

 

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

select d.loc, listagg(e.ename, ',') within group (order by e.ename asc) employee
 from dept d, emp e
 where d.deptno = e.deptno
 group by d.loc;
 
-- listagg는 데이터 분석함수 중 유일하게 group by를 사용함

 

 

059 여러 테이블의 데이터를 조인해서 출력하기 2(NON EQUI JOIN)

-- 사원 테이블과 급여 테이블과 조인하여 이름과 월급과 월급에 대한 등급을 출력하세요
select e.ename, e.sal, s.grade
 from emp e, salgrade s
 where e.sal between s.losal and s.hisal;
 
 -- equal이 아닌 조인 조건의 문법을 no equi join 이라고 부름

문제290. 직업이 SALESMAN인 사원들의 이름, 월급, 급여등급을 출력하시오

select e.ename, e.sal, s.grade
 from emp e, salgrade s
 where e.sal between s.losal and s.hisal
     and e.job = 'SALESMAN';
     
-- from절에서 emp는 e로 변경되었기 때문에 select절에 emp.ename이라고 하면 안되고 e.ename이라고 해줘야함

문제291. 급여등급, 이름, 월급, 월급에 대한 순위를 출력하시오

select s.grade, e.ename, e.sal, rank() over (order by e.sal desc) 순위
 from salgrade s, emp e
 where e.sal between s.losal and s.hisal;

문제292. 위의 결과를 다시 출력하는데 급여등급별로 각각 순위가 부여되게 하시오.

select s.grade, e.ename, e.sal, rank() over (partition by s.grade
                                             order by e.sal desc) 순위
 from salgrade s, emp e
 where e.sal between s.losal and s.hisal;

문제293. 급여등급, 급여등급별로 속한 사원들의 이름을 가로로 출력하시오

select s.grade, listagg(e.ename, ',') within group (order by e.ename asc) employee
 from emp e, salgrade s
 where e.sal between s.losal and s.hisal
 group by s.grade;

 

ㅁ 조인문장을 작성할 때는 꼭 2개의 테이블만 조인해야 하는가?

X, 여러개의 테이블을 조인해서 각각의 컬럼들을 하나의 결과로 볼 수 있음.

ex) emp ---- dept ---- salgrade 모두 조인 연결할 수 있음

 

문제294. 이름, 월급, 급여등급(grade), 부서위치(loc)를 출력하시오

select e.ename, e.sal, s.grade, d.loc
 from emp e, dept d, salgrade s
 where e.deptno = d.deptno and e.sal between s.losal and s.hisal;

문제295. 급여등급이 2등급인 사원들의 이름과 월급, 급여등급과 부서위치를 출력하시오.

select e.ename, e.sal, s.grade, d.loc
 from emp e, dept d, salgrade s
 where e.deptno = d.deptno and e.sal between s.losal and s.hisal
      and s.grade = 2;

문제296. 부서위치가 CHICAGO이고 직업이 SALESMAN인 사원들의 이름, 월급, 직업, 부서위치를 출력하시오

select e.ename, e.sal, e.job, d.loc
 from emp e, dept d
 where e.deptno = d.deptno
       and e.job = 'SALESMAN' and d.loc = 'CHICAGO';
       
 -- salgrade 테이블은 굳이 조인하지 않아도 됨 > 질문의 조건들을 잘 파악해야함 < sql 검색속도 느려짐

 

ㅁ오라클 조인 문법 4가지

1. equi join :  조인하려는 테이블 사이의 연결고리가 equal조건(=)인 경우

2. non equi join : 조인하려는 테이블 사이의 연결고리가 equal조건이 아닌 경우

3. outer join : equi join으로는 볼 수 없는 결과를 보려할 때 사용하는 조인

4. self join : 자기 자신의 테이블과 조인하는 조인문법

 

 

060 여러 테이블의 데이터를 조인해서 출력하기 3(OUTER JOIN)

문제297. 이름과 부서위치를 출력하시오. 그리고 부서위치 쪽에 dept테이블에는 존재하는 부서위치인데 출력 안된 부서위치가 있는지 찾아보시오

select e.ename, d.loc
 from emp e, dept d
 where e.deptno = d.deptno;
 /* 40번 부서인 사원이 없기 때문에 BOSTON 출력이 안됨 > BOSTON이 있는데 단지 출력이 안됐을 뿐
 하지만 이로인해 출력결과로 잘못된 해석을 할 수 있음
 > 이 경우 outer 조인을 사용해야함*/
 
-- 왼쪽의 결과가 모자른 결과를 보려면 아래와 같이 작성하고
select e.ename, d.loc
 from emp e, dept d
 where e.deptno (+) = d.deptno;
 
-- 오른쪽의 결과가 모자른 결과를 보려면 아래와 같이 작성해야함
select e.ename, d.loc
 from emp e, dept d
 where e.deptno = d.deptno (+) ;
 
 
/* 공통된 데이터의 갯수가 맞는지 확인해야함
select distinct deptno from dept;
select distinct deptno from emp;
개수를 비교해서 outer join / outer join sign(+) 을 사용 */

-- 사원테이블에 데이터 추가

insert into emp(empno, ename, sal, deptno)
 values (2919, 'JACK', 3400, 70);
 
commit;
 
-- 사원테이블에는 70번 부서번호가 존재하는데 부서 테이블에는 70번 부서번호가 존재하지 않는 상태.

select e.ename, d.loc
 from emp e, dept d
 where e.deptno = d.deptno;
 -- JACK 출력되지 않음
 
 -- JACK을 보기 위해 outer join sign 넣어야함
select e.ename, d.loc
 from emp e, dept d
 where e.deptno = d.deptno(+);
 
-- but, 양쪽으로 outer join sign을 넣을 수 없음 > 뒤에서 full outer join 배울 예정

문제298. 부서위치, 부서위치별 토탈월급을 출력하시오

select d.loc, sum(e.sal) 
 from emp e, dept d
 where e.deptno = d.deptno
 group by d.loc;

문제299. 위의 결과에서 조인안 된 BOSTON도 출력되게 하시오

select d.loc, sum(e.sal) 
 from emp e, dept d
 where e.deptno(+) = d.deptno
 group by d.loc;

 

 

061 여러 테이블의 데이터를 조인해서 출력하기 4(SELF JOIN)

SELF JOIN : 자기 자신의 테이블과 조인하는 조인 문법

ex) BLAKE의 관리자가 누구인가? 관리자보다 더 많은 월급을 받는 사원들의 직업은 무엇인가? 관리자보다 먼저 입사한 사원들은 누구인가?

 

-- BLAKE의 관리자가 누구인가? 
select empno, ename, mgr
 from emp
 where ename= 'BLAKE';
 -- 7839가 사원번호인 사원이 바로 BLAKE의 관리자
select empno, ename
 from emp
 where empno = 7839; 
 -- KING
 
/* 결과를 다음과 같이 보고 싶다면?
사원이름  관리자이름
BLAKE     KING
  :        :
  :        :
> self join을 사용
*/

select 사원.ename as 사원, 관리자.ename as 관리자이름
 from emp 사원, emp 관리자 
 where 사원.mgr = 관리자.empno;

문제300. 위의 결과를 다시 출력하는데 사원이름, 월급, 관리자이름, 관리자월급을 출력하시오

select e.ename 사원, e.sal 사원월급, m.ename 관리자, m.sal 관리자월급
 from emp e, emp m
 where e.mgr = m.empno;

문제301. 관리자보다 더 많은 월급을 받는 사원들을 출력하시오

select e.ename 사원, e.sal 사원월급, m.ename 관리자, m.sal 관리자월급
 from emp e, emp m
 where e.mgr = m.empno and
      e.sal >= m.sal;

문제302. (오늘의 마지막 문제) 사원이름, 사원의 입사일, 관리자이름, 관리자의 입사일을 출력하는데 관리자보다 먼저 입사한 사원들만 출력하시오

select e.ename 사원이름, e.hiredate 사원입사일, m.ename 관리자이름, m.hiredate 관리자입사일
 from emp e, emp m
 where e.mgr = m.empno and
      to_date(e.hiredate, 'RRRR/MM/DD') < to_date(m.hiredate, 'RRRR/MM/DD');