Big Data展

SQL by Oracle #6 outer join과 다양한 서브쿼리

빅브로오 2020. 4. 24. 12:20

Author : Yoon Baek

outer join

  • 조인 조건을 만족하지 않는 대상까지 추출
  • 기준이 되는 테이블 위치에 따라
    left outer join, right outer join, full outer join으로 구분

예제) Student, professor 테이블을 사용하여
각 학생의 이름, 학년, 지도교수명 출력
(단 지도 교수가 없는 학생의 정보는 생략)

select s.name, s.grade, p.name
  from student s left join professor P
    on s.profno = p.PROFNO; --ansi 표준
select s.name, s.grade, p.name
  from student s, professor P
 where s.profno = p.profno(+); --오라클 표준. 기준 되는 테이블 반대쪽에 +를 붙여주면 된다.

연습문제) Student, professor 테이블을 사용하여
교수의 이름, pay, 각 교수의 지도학생 이름과 학년 출력

select p.name, p.profno, p.pay, s.name
  from professor p left join student s
    on s.profno = p.profno
 order by 2;

연습문제) 위 결과에서 지도교수 별 지도학생 수를 출력
단, 지도학생 없는 교수도 출력

select p.name, p.profno, count(s.studno) 지도학생수
  from professor p left join student S
    on s.profno = p.profno
 group by p.profno, p.name
 order by 2;

오라클 표준문법으로는 full outer join 불가능하다.
ansi 표준 이용하면 가능

1) ansi 표준

select s.name as 학생이름,
       p.name as 교수이름
  from Student s full outer join professor P
    on s.profno = p.profno;

2) oracle 표준

select s.name as 학생이름,
       p.name as 교수이름
  from student s, professor P
 where s. profno(+) = p.profno(+); -- 불가, 연산오류, 성능상으로도 좋지 않음

full outer join은 성능상 사용하지 않는다!
실제로 사용해야하는 경우도 많지 않다.

2-1 오라클 표준으로 full outer join 하는 법

select s.name as 학생이름,
       p.name as 교수이름
  from student s, professor P
 where s.profno(+) = p.PROFNO
 union
select s.name as 학생이름,
       p.name as 교수이름
  from student s, professor P
 where s.profno = p.PROFNO(+);
 -- union을 사용

각 방향으로 outer join한 다음 union 해주면
left와 right를 합친 full outer join 결과가 출력되는 것을 볼 수 있다.


예제)emp2, p_grade 테이블을 사원의 나이 기준으로 조인,
각 사원의 이전직급, 현재직급 출력
단, 직급이 없는 직원도 모두 출력

select name, 
       trunc(months_between(sysdate,birthday)/12)
       as 나이,
       e.position as 이전직급,
       nvl(p.position, '사장') as 현재직급
  from emp2 e left join p_grade P
    on trunc(months_between(sysdate,birthday)/12) between s_age and e_age;


예제) STUDENT, PROFESSOR, DEPARTMENT 테이블을 사용,
각 학생의 이름, 지도교수명, 지도교수의 학과명을 출력
(단 지도교수가 없는 학생 정보도 출력)

select s.name 학생이름, p.name 지도교수명, d.dname 지도교수학과명
  from PROFESSOR p right join student s
    on s.PROFNO = p.PROFNO
  left join department d
    on p.DEPTNO = d.DEPTNO ;

s를 기준으로 조인할 때 s - p - d이면 p뿐만아니라 d에도 +가 적용되어야 한다.
s-p(+)-d -->X
s-p(+)-d(+) --> O

self join

: 하나의 테이블이 여러번 조인이 되는 경우
한번의 scan으로 표현할 수 없는 정보를
똑같은 테이블을 재사용 scan 해야만 사용할 수 있는 경우

조인하고자하는 대상컬럼이 본인 테이블에 존재하는 경우의 조인이다.
단 한 번의 테이블 access로는 원하는 결과값을 가져올 수 없을 경우에 사용.
ex) 같은 테이블의 MGR(관리자)의 번호를 통해 이름까지 가져오고 싶을 경우.

select a.*, b.ename, c.ename, d.ename
  from emp a, emp b, emp c, emp d
 where a.mgr = b.empno(+)
   and b.mgr = c.empno(+)
   and c.mgr = d.empno(+);


서로 같은 테이블이라고 생각하기보다
서로 별개의 테이블이라고 생각하면 크게 다르거나 어려울 것도 없다.\

  • 단 중복되는 이름의 컬럼이 있을 경우 어떤 테이블의 컬럼인지 알리아스를 통해 표시해주자.

예제) 본인의 mgr의 sal보다 sal이 높은 사람을 출력

select a.ename, a.sal, b.ename 매니져, b.sal, c.ename 더높은사람, c.sal
  from emp a, emp b, emp C
 where a.mgr = b.empno
   and b.sal < c.SAL;

연습문제) DEPARTMENT에서 각 학과명과 상위학과명을 동시 출력
단, 상위학과가 없는 경우도 함꼐 출력

 select a.deptno, a.dname, b.dname, c.dname
   from DEPARTMENT a, DEPARTMENT b, DEPARTMENT c
  where a.part = b.DEPTNO(+)
    and b.part = c.deptno(+);
-- oracle 표준

 select a.deptno, a.dname, b.dname, c.dname
   from department a left join department B
     on a.part = b.DEPTNO
   left join department C
     on b.part = c.deptno;
-- ANSI 표준

연습문제) 위 문제에서 각 학과의 소속정보를
'소프트웨어공학과의 소속은 컴퓨터정보학부입니다.'형식으로 출력
단, 소속학과가 없는 경우는 본인 소속으로 출력

select a.deptno,
       a.dname ||'의 소속은 '||nvl(b.dname,a.dname)||'입니다.' as Output
  from department a, department b
 where a.part = b.deptno(+);

연습문제 employees 테이블을 사용하여
각 직원의 이름, 입사일, 연봉, 직급을 출력
단, 직급은 근속년수 기준 p_grade 참조하여 부여

select first_name ||' '|| last_name 이름, hire_date 입사일, salary 연봉, 
       p.position 직급, trunc(months_between(sysdate, hire_date)/12) 근속년수
from employees e join P_GRADE p
  on trunc(months_between(sysdate, hire_date)/12) between p.S_YEAR and p.E_YEAR;

연습문제) professor 테이블에서 교수의 번호, 교수이름,
입사일, 자신보다 입사일 빠른 사람 인원수를 출력하세요.

select a.profno 교수번호, a.name 이름, a.hiredate 입사일, count(b.profno) 선배수
  from PROFESSOR a left join PROFESSOR b
    on a.hiredate > b.hiredate
 group by a.profno, a.name, a.hiredate
 order by 3;

서브 쿼리

sub query : 쿼리문 안에 있는 쿼리문을 의미

서브쿼리 종류 및 목적

  1. select ... (select ...) --> 스칼라 서브쿼리
  2. from ... (select ...) --> 인라인 뷰
  3. where ... (select ...) --> 일반 서브쿼리

일반 서브쿼리

-- 예제) emp 테이블에서 전체 직원의 평균 연봉보다
--낮은 연봉을 받는 직원의 이름, 연봉, 입사일 출력

select ename, sal, hiredate
  from EMP
 where sal < (select avg(sal) from emp);

변동하는 상수값을 안정적으로 적용하기 위해 많이 사용한다.

예제) scott과 같은 부서에 있는 직원정보 출력

select *
  from EMP
 where deptno = (select deptno from emp where ename = 'SCOTT');

문법에 따른 서브쿼리 분류

1. 단일행 서브쿼리 : =, !=, >, < 등 하나와 비교하는 연산과 잘 어울림

employees 테이블에서 'Donald' 와 salary 가 동일한 사람들의 employee_id , first_name, salary 값을 출력하기

select employee_id, first_name, salary
  from employees
 where salary = (select salary from employees
                              where first_name = 'Donald');

employees 테이블에서 'Donald' 사원과 입사일이 동일하거나 늦게 입사한 사원들의\
employee_id , first_name , salary , hire_date 값을 출력하기

select employee_id, first_name, salary, hire_date
  from employees
 where hire_date >= (select hire_date
                     from employees
                    where first_name = 'Donald');

2. 다중행 서브쿼리

: 결과가 여러 행이 출력되는 서브쿼리
: 연산과 함께 쓸 수 없음
=> in, (max, min을 출력하는) any, all 과 함께 씀

예제 ) 이름이 'A'로 시작하는 직원과 같은 부서에 있는 직원 정보 출력

select * 
  from EMP
 where deptno in (select deptno
                    from EMP
                   where ename like 'A%');

예제) 이름이 'A'로 시작하는 직원의 연봉보다 높은 연봉을 갖는 직원 정보 출력

select *
  from EMP
 where sal > all (select sal
                    from EMP
                   where ename like 'A%');

all은 전체를 대상으로 비교하며
any는 하나라도 비교하여 조건에 맞으면 출력된다.

[실습]

1.

student 테이블에서 각 학생의 이름, 제1 전공학과명을 출력하고
담당지도교수 이름과 지도교수의 소속 학과명도 함께 출력하여라
단 지도교수가 없는 학생들도 모두 출력

select s.name, d1.dname, p.name, d2.dname
  from STUDENT s left join PROFESSOR p
    on s.profno = p.profno
  left join department d1
    on s.deptno1 = d1.deptno
  left join department d2
    on d2.deptno = p.deptno;

ansi 표준은 많은 현업에서도 사용하는 직관적이고 좋은 표준이나
오라클 표준에 비해 여러번 조인을 해주는 과정에서 쿼리가 길어질 수 있다.
오라클 표준은 from절에 테이블을 한 번에 넣을 수 있지만
ansi 표준은 계속해서 다음 테이블과 조인을 해줘야 한다.

2.

student 테이블과 exam_01, department 테이블을 이용하여
각 학과별 평균 점수와 최고점수, 최저점수를 나타내되
학과이름, 학과번호와 함께 출력.

select d.deptno, dname, avg(total), max(total), min(total)
  from student s join department d
    on s.deptno1 = d.deptno
  join exam_01 e
    on s.studno = e.STUDNO
 group by d.DEPTNO, dname;

3.

student 테이블에서 같은 학년내 각 학생보다 키가 큰 학생의 수를 출력하되,
학생이름, 학과번호, 학년을 함께 출력하여라

select s1.name, s1.deptno1, s1.grade, count(s2.studno) 키큰학생수
  from STUDENT s1 left join STUDENT s2
    on s1.height < s2.height
   and s1.grade = s2.grade
 group by s1.name, s1.deptno1, s1.grade;
 select * from PROFESSOR;

4.

professor 테이블에서 홈페이지 주소가 있으면
그대로 출력 없으면 아래와 같이 출력
http://www.naver.com/email_id\
단, email_id는 email 주소의 @ 앞의 문자라 가정한다.

select name, 
       nvl(HPAGE, 'http://www.naver.com/'||substr(email,1,instr(email, '@')-1))
  from PROFESSOR;

5.

레포트를 작성하고자 한다.
emp 테이블을 이용하여 각 부서별 직원수를 출력하되
다음과 같은 형식으로 작성하여라.

레포트명 10_직원수 20_직원수 30_학생수
본인이름 레포트 3 5 6

풀이 1 : 스칼라 서브쿼리로 풀이

select 'Yoon 레포트' as 레포트명,
       (select count(empno) from emp where deptno = 10) as "10_직원수",
       (select count(empno) from emp where deptno = 20) as "20_직원수",
       (select count(empno) from emp where deptno = 30) as "30_직원수"
from dual;

풀이 2 : count와 decode 함수를 이용한 풀이

select 'Yoon 레포트' as 레포트명,
        count(decode(deptno,10,1)) as "10_직원수",
        count(decode(deptno,20,1)) as "10_직원수",
        count(decode(deptno,30,1)) as "10_직원수"
   from emp;


이밖에,
3. 다중컬럼 서브쿼리
4. 상호연관 서브쿼리
가 존재하나 이는 다음 글을 통해서 다뤄보기로 하자.