230703 Oracle SQL 6 [join, 집합연산자]

SQL 복습 정리

1. select 문의 6가지 절
   select 보고싶은 컬럼명
   from 테이블명
   where 검색조건
   group by 그룹핑할 컬럼
   having 그룹핑할 컬럼으로 검색조건
   order by 정렬할 컬럼

2. 함수
   1) 단일행 함수 : 문자, 숫자, 날짜, 변환, 일반
   2) 복수행 함수 : max, min, avg, sum, count
   3) 데이터 분석함수 : rank, dense_rank, ntile, cume_dist, listagg, lag, lead, 누적데이터 구하기, 기타

3. 조인 JOIN (외워버리기)
   1) 오라클 조인 문법

   • equi join
   • non equi join
   • outer join
   • self join
     2) 1999 ANSI 조인문법 (10g)
   • on 절을 사용한 join
   • using 절을 사용한 join
   • left/right/full outer join
   • natural join
   • cross join

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

self join : 자기 자신과 조인하는 문법, mgr 을 이해하고 있어야 사용가능
(관리자의 사원번호)

예제. select empno, ename, mgr
from emp;

예제. 사원이름, 관리자의 이름을 출력하시오
(관리자라는 것은 자기자신의 관리자를 말합니다.)

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

• emp ename을 사원, 관리자로 나눠서 출력하는 느낌?
• emp와 dept의 경우는 emp.deptn=dept.deptno 라는 동일한 조인조건이 있었다.
  사원 테이블과 관리자 테이블의 경우에는,
  사원 테이블의 관리자번호 (사원.mgr) = 관리자번호의 사원번호 (관리자.empno)

문제 318.
이름, 사원이름, 관리자이름을 출력하는데
사원이름이 'SMITH'인 사원만 출력하시오

select  e.ename as 사원이름, m.ename as 관리자이름
 from emp e, emp m
 where e.mgr = m.empno and e.ename = 'SMITH';

문제 319.
사원이름, 관리자이름을 출력하는데
관리자보다 월급이 더 많은 사원들만 출력하시오

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

문제 321.
사원이름, 사원의 입사일, 관리자 이름, 관리자의 입사일을 출력하는데
관리자보다 먼저 입사한 사원들만 출력하시오

select e.ename as 사원이름, e.hiredate "사원 입사일", m.ename 관리자이름, m.hiredate "관리자 입사일"
 from emp e, emp m
 where e.mgr = m.empno and e.hiredate < m.hiredate ;

날짜에서 비교 부등호 쓸때 잘 체크하기
"먼저"입사한 쪽으로 < 써줘야함

문제 322. (OCP 시험)
'HR' 계정의 employees 테이블을 이용해서 self join을 하는데,
관리자번호가 100번인 사원의 사원이름과 관리자이름을 (last name) 출력하시오

select e.last_name as 사원이름, m.last_name as 관리자이름
 from employees e, employees m
 where e.manager_id = m.employee_id
        and e.manager_id = 100;

1999 ANSI 조인 문법으로 바꾸기

오라클 조인문법은 'where' 을 쓰는데, ansi 조인문법은 'on' 을 사용한다

select e.last_name , m.last_name
 from employees e join employees m
 on (e.manager_id = m.employee_id)
 where e.manager_id = 100;

※ 차이점 : 오라클은 조인조건과 검색조건이 둘다 where 절에 있음
ansi문법은 조인조건은 on 에, 검색조건은 where 절에 쓴다.
from 절에 콤마대신 join 쓰는것이 정확한 문법이다.
둘다 알고 있어야 하고, 사용한다. (둘다 사용해도 무방) 성능이나 속도에는 차이없다.

모든 직원과 해당 관리자의 100을 'MANAGER_ID'로 표시하려고 합니다. 출력을 두 개의 열로 나눌 수 있습니다. 첫 번째 열은 관리자의 'LAST_NAME'이고 두 번째 열은 직원의 'LAST_NAME'입니다.

select  m.last_name "manager" , e.last_name "employees" 
      -> 관리자 테이블의 last_name 은 'manager'로 , 직원테이블의 last_name 은 'employees' 로 표시하여 출력할것이다.
 from  employees m join employees e
      -> emplyees 테이블을 m, 과 e 로 나누어 쓸거다.
 on ( m.employee_id = e.manager_id ) 
      -> 관리자테이블의 직원id = 직원테이블의 관리자id
 where e.manager_id = 100;
     -> 직원테이블의 매니저id 가 100인 사람을 출력할거다.

문제 323.
이름과 부서위치를 출력하는데, on 절을 사용한 조인문법으로 구현하시오

{오라클 조인문법}

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

{on절을 사용한 조인문법}

select e.ename, d.loc
from emp e join dept d
on ( e.deptno = d.deptno) ;

참고. mysql 에서도 둘다 작동한다.

문제 324.
on 절을 사용한 조인 문법으로 DALLAS 에서 근무하는 사원들의 이름과 부서위치를 출력하시오

select e.ename, d.loc
 from emp e join dept d
 on ( e.deptno = d.deptno)
 where d.loc = 'DALLAS' ;

문제 325.
직업이 SALESMAN 인 사원들의 이름과 직업과 부서위치를 출력하시오
( on 절을 사용한 조인문법으로 수행 )

select e.ename, e.job, d.loc
 from emp e join dept d
 on ( e.deptno = d.deptno )
 where e.job = 'SALESMAN' ;

오라클 조인문법 4가지 분류

- equi join          -> inner join
- non equi join      -> inner join
- outer join         -> outer join
- self join          -> outer 안썼으면 inner임

=> join 을 크게 두가지로 나눈다면 inner join, outer join (outer 사인이나 말이 없다면 다 inner임)

문제 326. (복습문제)
emp 와 salgrade 를 조인해서 이름, 월급과 급여등급(grade) 를 출력하는데, 3등급만 출력하시오
(오라클 조인문법으로 먼저)

select e.ename, e.sal, s.grade
 from emp e , salgrade s
 where e.sal between s.losal and s.hisal
       and s.grade = '3';

(1999 ansi 조인문법)

select e.ename, e.sal, s.grade
from emp e join salgrade s
on ( e.sal between s.losal and s.hisal )
where s.grade = '3';

문제 327. (3개의 테이블 조인)
emp , dept , salgrade 를 조인해서
급여등급이 3등급인 사원들의 이름과 월급과 부서위치와 grade (급여등급)을 출력하시오
(오라클 조인문법)

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

(1999 ansi 조인문법)

select  e.ename, e.sal, d.loc, s.grade
from  emp e join dept d 
            on (e.deptno = d.deptno)
            join salgrade s
            on (e.sal between s.losal and s.hisal)
where  s.grade = '3';

※ 2개 이상의 조인 할때 on 절 쓰려면,
from 절에
a join b on (조인조건 1)
join c on (조인조건 2)
where 절에 검색조건

문제 328. (OCP 시험)
HR유저의 employees 와 departments 와 jobs 를 조인해서
lase_name, department_name, job_title 을 출력하시오
(on 절을 사용한 조인문법으로 수행하시오)

select e.last_name, d.department_name, j.job_title
 from employees e join departments d
            on (e.department_id = d.department_id)
      join jobs j
            on (e.job_id = j.job_id);

employees - departments 조인조건 : department_id
employees - jobs 조인조건 : job_id

다음 중 쿼리의 'WHERE' 절에 지정된 inner joiin 에 대한 설명으로 옳은 것은 무엇입니까?
A. 오로지 equi 조인 조건에서만 사용된다 X
B. EQUI , NON EQUI 둘다 쓸 수 있기 때문에 O
C. 컬럼 이름이 똑같지 않아도 JOIN 할 수 있기 때문에 X
D. 프라이머리키, 폴리키 (제약) 없이도 join 할 수 있기 때문에 X

063. 여러 테이블의 데이터를 조인해서 출력하기 5 (USING)

예제. 이름과 부서위치를 출력하는데 using 절을 사용한 조건으로 수행하시오

select e.ename, d.loc
from emp e join dept d
using (deptno) ;

※ using 절에 연결고리가 되는 컬럼인 deptno 만 작성하면 된다.
이 때 주의할 사항은, e.deptno 또는 d.deptno 라고 쓰면 에러 발생

※ using 절을 사용한 조인은 조인 조건이 이퀄 조건이었던 equi 조인만 가능하고,
between .. and 로 주었던 non equi 조인은 구현 안된다.
ex) emp랑 salgrade 는 sal의 중간값으로 조인조건 했었던거라 using 절 사용 X

문제 329.
employees 테이블과 departments 테이블을 서로 조인해서
last_name, department_name 을 출력하는데
using 절을 사용한 조인문법으로 구현하시오

select e.last_name, d.department_name
 from employees e join departments d
 using (department_id) ;

• using 절에 괄호를 안쓰면 에러가 발생한다! ( ) 필수!

064. 여러 테이블의 데이터를 조인해서 출력하기 6 (NATURAL JOIN)

예제. 이름과 부서위치를 출력하는데 natural join 으로 수행하시오

select e.ename, d.loc
from emp e natural join dept d;

※ natural join : 오라클이 알아서 조인조건을 찾아서 조인해주는 조인 문법

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

• 오라클 조인문법 (equi join)

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

• ansi 문법 (using 절)

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

• ansi 문법 (natural join)

  select d.loc, sum(e.sal)
  from emp e natural join dept d
  group by d.loc;

(점심시간 문제)

select d.loc as 부서위치,
       listagg (e.ename ||'('||e.sal||')' , ',')
       within group (order by e.sal desc) as 사원
from emp e join dept d
on e.deptno = d.deptno
group by d.loc;

• listagg 사용 -> 가로 배열하기 listagg (컬럼 , ',') within group (order by 순서)
• 컬럼 이랑 문자절을 이어주는 || 사용 -> 컬럼 ||'문자'||컬럼||'문자')
• on 절을 사용한 1999 ansi 조인 문법

065. 여러 테이블의 데이터를 조인해서 출력하기 7 (LEFT/RIGHT OUTER JOIN)

예제. 이름과 부서위치를 출력하는데 아우터 조인을 사용해서
사원테이블에는 없는 부서번호인데 부서테이블에는 있는 부서번호에 대한 부서위치를 출력하시오

• 오라클 조인 문법 :
  select e.ename, d.loc
  from emp e, dept d
  where e.deptno (+) = d.deptno ;

• ANSI 문법 :
  select e.ename, d.loc
  from emp e right outer join dept d
  on (e.deptno (+) = d.deptno) ;
  

-> 'right outer join' 으로 작성하면 오른쪽에 outsider 한 데이터가 출력됩니다.

문제 331.
HR 유저의 employees 테이블과 departments 테이블을 서로 조인해서
last_name 과 department_name 을 출력하는데
부서 테이블에는 존재하는 department_name 인데
해당 사원이 아직 배치되지 않은 부서명도 출력하시오

select e.last_name, d.department_name
 from employees e right outer join departments d
 on (e.department_id = d.department_id) ;

문제 332. (SQLD)
위의 결과를 다시 출력하는데, last_name 이 null 값이 맨위에 나오게 하시오

select e.last_name, d.department_name
 from employees e right outer join departments d
 on (e.department_id = d.department_id)
 order by e.last_name  asc nulls first;

'nulls first' 사용해서 null 값 맨 위로 올리기
>

문제 333. (self join)
사원 이름, 관리자인 사원의 이름을 출력하시오 (on 절을 사용한 조인 문법으로)

select  e.ename as 사원이름, m.ename as 관리자이름
 from emp e join emp m
 on (e.mgr = m.empno );

-> 사원 14명인데 KING 이 출력이 안되었음

문제 334. (outer join)
위의 결과에서 사원이름쪽에 KING 이 나오게 하시오

select e.ename as 사원이름, m.ename as 관리자이름
 from emp e left outer join emp m
 on (e.mgr = m.empno) ;

KING은 사장이라 mgr번호가 없어서 outer 한 데이터임 -> inner join 으로는 결과 X

문제 335. 나중에 다시 체크해보기

There is a parent/child relationship between 'EMPLOYEE_ID' and 'MANAGER_ID'.
You want to display the last names and manager IDs of employees who work for the same manager as the employee whose 'EMPLOYEE_ID' is 123
-> 부모키, 자식키 : MANAGER_ID 에 있는 것들은 모두 'employee_id' 에 있다는 말..!

'employee_ID'와 'MANAGER_ID' 사이에는 상위/하위 관계가 있습니다.
'employee_ID'가 123인 직원과 동일한 관리자에 근무하는 직원의 성과 관리자 ID를 표시하려고 합니다.
올바른 출력을 제공하는 쿼리는 무엇입니까?

한번 고민해보기 (강사님도 헷갈리신다고함)

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

오라클 조인문법으로는 지원하지 않는 아우터 조인문법이고
오로지 ansi 조인 문법으로만 구현할 수 있는 문법

예제1. 아래의 오라클 조인문법이 수행되는지 확인하시오

select e.ename, d.loc
 from emp e, dept d
 where e.deptno (+) = d.deptno (+);

ORA-01468: outer-join된 테이블은 1개만 지정할 수 있습니다
-> ansi 문법으로는 가능하다.

select e.ename, d.loc
 from emp full outer join dept d
 on ( e.deptno = d.deptno) ;

-> jack, boston 까지 다 나옴

문제 335.
위의 SQL에 실행계획을 확인하시오

explain plan for
select e.ename, d.loc
 from emp full outer join dept d
 on ( e.deptno = d.deptno) ;

 select * from table(dbms_xplan.display);

067. 집합 연산자로 데이터를 위아래로 연결하기 1 (UNION ALL)

조인과 집합연산자

조인 join : 두 테이블의 결과를 양 옆으로 연결해서 보여주는 문법
집합연산자 : 두 테이블의 결과를 위아래로 연결해서 보여주는 문법

집합 연산자의 종류 4가지

1. union all : 합집합 연산자
2. union : 합집합 + 중복데이터 제거
3. intersect : 교집합
4. minus : 차집합

문제 336.
부서번호, 부서번호별 토탈월급을 출력하시오

select deptno, sum(sal)
 from emp
 group by deptno;

문제 337.
사원 테이블의 전체 토탈월급을 출력하시오

select sum(sal)
 from emp;

문제 338.
roll up 을 사용하여 부서번호, 부서번호별 토탈월급을 출력하는데,
맨 밑에 전체 토탈 월급이 출력되게 하시오

select deptno, sum(sal)
 from emp
 group by rollup(deptno);

roll up 말고 집합연산자 활용해보기

문제 339. 위의 결과를 roll up 을 이용하지 않고
union all 로 구현해보시오

select deptno, sum(sal)
  from emp
  group by deptno
union all
  select to_number(null) as deptno, sum(sal)
  from emp;

합집합 연산자 사용할 때 주의할 사항

1. 위의 SQL 과 아래의 SQL 의 컬럼의 갯수가 동일해야 한다.
2. 위의 SQL 과 아래의 SQL 의 컬럼의 유형이 동일해야 한다.
3. 가급적 컬럼명도 동일하게 해줘야 order by 할 때 깔끔하게 정렬된다.
   (컬럼별칭은 맨 위 쿼리에만 적용됨~!)
4. order by 절은 맨 아래에 있는 쿼리문에만 사용할 수 있다.
   

문제 340.
아래의 SQL의 결과를 UNION ALL 로 구현하시오.

select deptno, job, sum(sal)
 from emp
 group by grouping sets ( (deptno), (job) );

select deptno, to_char(null) as job, sum(sal)
 from emp
 group by deptno
union all
select to_number(null) as deptno, job, sum(sal)
 from emp
 group by job ;

-> 왜 순서가 다르게 나옴? ㅠ
order by 1 nulls first; 마지막에 붙이면 같게 나옴

문제 341.
아래의 SQL의 결과를 union all 로 수행하시오

select to_char( hiredate, 'RRRR'), null as job, sum(sal)
 from emp
 group by grouping sets (  (to_char(hiredate, 'RRRR')), (job)  );

select to_char( hiredate, 'RRRR'), to_char(null) as job, sum(sal)
 from emp
 group by to_char (hiredate, 'RRRR')
union all
select to_char(null), job, sum(sal)
 from emp
 group by job;

job은 애초에 문자라서 to_해줄필요 X

문제 342.
위의 결과를 아래와 같이 정렬해서 출력하시오 (년도 정렬)

select to_char( hiredate, 'RRRR') as 년도, to_char(null) as job, sum(sal)
 from emp
 group by to_char (hiredate, 'RRRR')
union all
select to_char(null) as 년도, job, sum(sal)
 from emp
 group by job
 order by 년도 asc;

컬럼명 동일하게 맞추기,
order by 절은 맨 마지막에만

delect from emp
where job is null;

commit;

문제 343. 아래와 같이 결과를 출력하시오 -> 년도와 직업까지 정렬되게

 select to_char( hiredate, 'RRRR') as 년도, 
        to_char(null) as job,  sum(sal)
  from emp
  group by to_char (hiredate, 'RRRR')
 union all
 select to_char(null) as 년도, job, sum(sal)
  from emp
  group by job
  order by 년도 asc , job asc;

둘다 정렬 하고 싶으면 order by 절에 쉼표 붙여서 두개 쓰면 됨!! (and 아님)

068. 집합 연산자로 데이터를 위아래로 연결하기 2 (UNION)

union 이 union all 과 다른점

1. 중복행이 제거된다
2. 맨앞의 컬럼을 기준으로 결과가 정렬이 된다.

문제 344.
부서번호가 10번, 20번인 사원들의 부서번호, 부서번호별 토탈월급을 출력하시오

select deptno, sum(sal)
 from emp
 where deptno in (10, 20)
 group by deptno;

문제 345.
부서번호가 20번, 30번인 사원들의 부서번호, 부서번호별 토탈월급을 출력하시오

select deptno, sum(sal)
 from emp
 where deptno in (20, 30)
 group by deptno;

문제 346.
문제 344번의 결과와 345번의 결과를 위아래로 연결해서 출력하시오

select deptno, sum(sal)
  from emp
  where deptno in (10, 20)
  group by deptno
union all
select deptno, sum(sal)
  from emp
  where deptno in (20, 30)
  group by deptno;

문제 347. 위의 결과를 union 으로 수행하시오

select deptno, sum(sal)
 from emp
 where deptno in (10, 20)
 group by deptno
union
select deptno, sum(sal)
 from emp
 where deptno in (20, 30)
 group by deptno;

union 을 쓰게되면 중복행이 제거되고 20이 한번만 나온다.

※ 만약에 굳이 중복행 제거하지 않아도 되고, 정렬 할 필요도 없다면 union 보다는 union all 을 사용하는게 성능상 더 좋습니다.

-> 대용량에서는 정렬작업이 오래걸리기 때문!

문제 348.

A. 정렬이 된다는 union all 이 아니라 union 이기 때문에 x
B. 컬럼이름 굳이 안맞아도 갠찮기때문에 x
C.
D. 중복 제거 되는 것은 UNION 이기때문에 x
E. NULL 은 중복 제거 할 때 무시된다. O (유니온 혹은 유니온올 에서)

UNION 에서는 NULLS 도 중복 제거 된다. (강사님이 체크해봄)
union all 에서는 nulls 값 중복제거 되지 않는다.

문제 348. (오늘의 마지막 문제) 다음과 같이 결과를 출력하세요
직업별 부서번호별 토탈월급들을 출력하는데,
맨 옆과 맨 아래에 집계값이 각각 나오게 출력하시오

select nvl(job,'토탈') as 직업,
         sum ( decode(deptno, '10', sal)) as "10",
         sum ( decode(deptno, '20', sal)) as "20",
         sum ( decode(deptno, '30', sal)) as "30",
         sum(sal) as 토탈
   from emp
   group by rollup(job)
   order by 1 asc;