[Oracle SQL]10일차_23.07.03

망구씨·2023년 7월 3일

Full Outer Join JOIN LEFT/RIGHT OUTER JOIN NATURALJOIN UNION ALL USING절 on절 selfjoin union

Oracle SQL

목록 보기

10/21

오늘의 TIL

여러 테이블의 데이터를 조인해서 출력하기 4(SELF JOIN)
여러 테이블의 데이터를 조인해서 출력하기 5(ON절)
여러 테이블의 데이터를 조인해서 출력하기 5(USING절)
여러 테이블의 데이터를 조인해서 출력하기 6(NATURAL JOIN)
여러 테이블의 데이터를 조인해서 출력하기 7(LEFT/RIGHT OUTER JOIN)
여러 테이블의 데이터를 조인해서 출력하기 8(FULL OUTER JOIN)
집합 연산자로 데이터를 위아래로 연결하기 1(UNION ALL)
집합 연산자로 데이터를 위아래로 연결하기 2(UNION)

복습

1. select문의 6가지 절

select 보고싶은 컬럼명

from 테이블명

where 검색조건

group by 그룹핑할 컬럼

having 그룹핑할 컬럼으로 검색조건

order by 정렬할 컬럼

2. 함수

단일행함수 : 문자, 숫자, 날짜, 변환, 일반

복수행 함수 : max, min, avg, sum, count

데이터 분석함수 : rank, dense_rank, ntile, cume_dist, listagg, lag, lead, 누적데이터 구하기 , 기타

3. 조인

오라클 조인 문법
1. equi jion
2. non equi jion
3. outer join
4. self join

1999 ANSI (미국 국립 표준 협회)조인문법 (10g)
1. on절을 사용한 조인
2. using 절을 사용한 조인
3. left / right / full outer join
4. natural join
5. cross join

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

자기 자신과 조인하는 조인문법!

select empno, ename, mgr
 from emp;

예제. 사원이름, 자기 자신의 관리자를 출력

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

emp와 dept의 경우는 emp.deptno = dept.deptno 라는 조건이 있다. 사원 테이블과 관리자 테이블의 경우는 조인조건이 어떻게 되는가?
블레이크의 mgr(관리자)번호는 7839, 이것은 king의 사원번호와 같다.
where 사원.mgr = 관리자.empno;

이 조인을 self join 이라고 합니다. 자기 자신의 테이블과 조인한다고해서 self join 입니다!!

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

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

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

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

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

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

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

select e.last_name, m.last_name, e.manager_id
 from employees e , employees m
 where e.manager_id = m.employee_id and e.manager_id = 100;

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

1999 ansi 조인문법(on절 사용한 조인)

// * on절을 사용한 ansi문법! *
select e.last_name, m.last_name, e.manager_id
 from employees e join employees m
 on e.manager_id = m.employee_id 
 where e.manager_id = 100;

// * 오라클 문법! *
select e.last_name, m.last_name, e.manager_id
 from employees e , employees m
 where e.manager_id = m.employee_id -> 조인조건
 and e.manager_id = 100; -> 검색조건

on절에 조인조건을 주고 where절에 검색조건을 주는것이 on절을 사용한 1999 ansi 조인문법이다. 오라클 조인문법은 (원래하던 것) where절에 조인, 검색조건을 모두 써주었던 것이 차이점!
성능적으로 차이는 없다.
ansi 로 on절 쓰려면 from 절에 join을 써야한다

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

select d.loc, e.ename
 from dept d join emp e
 on (e.deptno = d.deptno); // 가급적 괄호 써주기!

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

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

문제 325. 직업이 SALESMAN인 사원들의 이름, 직업, 부서위치를 출력하시오

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

오라클 조인 문법 4가지: 1. equi join , 2. non equi join, 3. outer join, 4. self join
-> 조인을 크게 두가지로 나눈다면 outer join 아니면 inner join이다.
outer 조인이 아니면 모두 다 inner join 이다.

문제 326. (복습) emp와 salgrade를 조인해서, 이름, 월급, 급여등급(grade)를 출력하는데 3등급만 출력하시오 (non equi join)

오라클

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;

on절 사용

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등급인 사원들의 이름, 월급, 부서위치, 등급 출력

오라클 join문법

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

on절 사용

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;

on절 사용한 3개이상 join은 from절에 join + join조건인 on 을 함께 써준다!

문제 328. (OCP문제) HR유저의 employees와 departments와 jobs를 조인해서 last_name, department_name, job_title 출력하기

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);

A -> only는 아니다.
C -> 컬럼 이름 달라도 된다. 데이터만 맞으면 조인가능
D -> 조인할 때 반드시 프라이머리키, 폴인키 제약이 되어있어야 함(아직안배움) : 반드시 그럴필요 없다.
정답은 B

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

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

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

※ 설명! using 절에 연결고리가 되는 컬럼인 deptno 만 작성하면 됨. 주의사항은, e.deptno또는 d.deptno 라고 하면 에러가 발생한다.
※ using절은 조인조건이 이퀄조건이었던 equi join만 가능하고, between ...and 로 주었던 non equi join은 구현할 수 없다!!!

select e.ename, e.sal, s.grade
  from emp e join salgrade
   using (?) ; // 뭘 넣을수가 없음!!!!!!!!!!!

문제 329. employees , departments테이블을 조인해서 last_name, department_name을 출력하는데 using절 사용

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

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

예제. 이름, 부서위치를 출력하는데 natural join으로 수행하세요!

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

오라클이 알아서 조인조건을 찾아 조인해줍니다. emp e natural join dept d;

문제 330. 부서위치, 부서위치별 토탈월급을 출력하시오 (시험때 두개가 다를것이다.. 하나가 에러날 것이다.. 이런식으로 나온다.)

오라클 조인 문법(equi join)
select d.loc , sum(e.sal)
  from emp e, dept d
  where e.deptno = d.deptno 
  group by d.loc;
ansi 문법 (using절)
select d.loc , sum(e.sal)
  from emp e join dept d
  using (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;  
출력결과 (3개 모두 동일!)

select d.loc, listagg(e.ename || '(' || e.sal || ')' || ',') within group (order by e.sal desc)
 from dept d , emp e
 where e.deptno = d.deptno and d.loc != 'BOSTON'
 group by d.loc;

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

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

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

BOSTON이 출력되는것을 확인할 수 있다! (데이터가 모자라게 나오는 NULL값..! 쪽에 (+) 써주기)

2. ANSI문법

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

오라클 조인문법은 모자른쪽에 (+)를 쓰지만, right outer join은 뭔가 아웃사이더처럼 나와있는 애가 있는 쪽으로 써준다.
오른쪽(BOSTON)에 outer한 데이터가 출력되니까 지금은 right outer join 사용.

3. 오라클 조인 문법

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

4. ANSI문법

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

이번에는 JACK이 우리가 보았을 때 왼쪽에 있으니까, left outer join를 사용해준다.

문제 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);

나는 on 말고 using (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 nulls first;

여기서는 using절을 쓰면 에러가난다.

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

ANSI 문법(ON절)

select e.ename, m.ename
 from emp e join emp m
 on (e.mgr = m.empno);

참고! - 오라클 조인문법

select e.ename, m.ename
  from emp e, emp m
  where e.mgr = m.empno;

문제 334. (OCP시험) 위 결과에서 사원이름쪽에 KING이 나오게 하세요!

select e.ename, m.ename
 from emp e left outer join emp m
 on (e.mgr = m.empno);
KING의 mgr번호가 null이라서, inner join으로는 출력되지 않는다.
사원이름쪽은 왼쪽이니까 left outer join 을 사용했다!

여러 테이블의 데이터를 조인해서 출력하기 8(FULL 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 full outer join dept d
  on (e.deptno = d.deptno);

문제 335. 위 sql의 실행계획 확인하기

explain plan for  
  select e.ename, d.loc
  from emp e full outer join dept d
  on (e.deptno = d.deptno); // 하나씩
 
select * from table(dbms_xplan.display); // 실행!
이렇게 나온다면 잘 나온 것. full outer join 은 속도를 많이 느려지게 할 수 있어서

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

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

집합연산자의 종류 4가지!

union all : 합집합 ( 중복되어도 그냥 다 합쳐버리는것 )
union : 합집합 + 중복 데이터 제거 ( 위에서 중복되는것은 제거하고 합치는 것 )
intersect : 교집합 ( 중복되는 것 )
minus : 차집합 ( 중복되는 것 빼기)

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

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

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

select sum(sal)
  from emp;

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

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

문제 339. 위 결과를 rollup을 이용하지 않고 union all로 구현해보기

select deptno  as 부서번호, sum(sal) as 토탈값
  from emp
  group by deptno
union all
  select to_number(null) as deptno, sum(sal)
  from emp
  order by 부서번호 asc;
합집합 연산자 사용할 때 주의사항!

위의 SQL과 아래의 SQL의 컬럼의 갯수가 동일해야 합니다. (UNION ALL 기준 위, 아래)

위의 SQL과 아래의 SQL의 컬럼의 유형이 동일해야 합니다. (문자형인지, 숫자형인지..)
-> NULL값을 TO_NUMBER로 바꿔주었다!

가급적 컬럼명도 동일하게 해줘야 ORDER BY 할 때 깔끔하게 정렬됩니다.

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;

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

select to_char(hiredate, 'RRRR'), 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;

문제 342. 아래와 같이 년도가 정렬되어 출력되게 만드세요.

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
 order by 1 asc; // asc 생략가능

별칭사용

select to_char(hiredate, 'RRRR') as 년도 , to_char(null) as 직업, 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 1 asc;

문제 343. 아래와 같이 결과를 출력하시오! (직업도 정렬되게 만들기!)

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

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

유니온 올과 다른점

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

문제 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번의 결과를 위아래로 연결해서 출력하세요(union all)

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;
사진처럼 union all을 기준으로 위 결과, 아래결과를 그냥 다 가져온다.

문제 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 보다는 union all을 사용하는것이 성능상 더 좋다! (기억할것!)

문제 348. (OCP 시험)

A. UNION ALL은 정렬되서 결과가 나오지 않는다.
B. 위아래 컬럼이름은 동일하지 않아도 괜찮다. (데이터 유형은 맞아야함)
C. 셀렉절의 컬럼의 갯수는 위아래가 동일해야한다. -> 답
D. UNION ALL은 중복이 자동으로 제거되지 않는다.
E. null은 체크가 불가해서 중복가능할 때 무시된다. -> 답

union all은 null을 중복제고 하지 않지만, union은 중복제거가 된다.

  select nvl(comm, 9000)
  from(
select comm
  from emp
  where empno in (7566, 7654, 7499)
  union 
select comm
  from emp
  where empno in (7499, 7844, 7900)
  );

null값이 있으면 9000으로 나오는 것!

문제 348. 다음과 같이 결과를 출력하세요!
( 직업별 부서번호별 토탈월급들을 출력하는데, 맨 옆과 맨 아래에 집계값들이 같이 출력)

내 답
select nvl(직업,'토탈'), "10", "20", "30", sum(토탈) as 토탈
from (
select job 직업, sum(DECODE(DEPTNO, 10, SAL, 0)) AS "10", // 부서번호가 10이면 월급출력
             sum(DECODE(DEPTNO, 20, SAL, 0)) AS "20",
             sum(DECODE(DEPTNO, 30, SAL, 0)) AS "30",
             sum(DECODE(DEPTNO, 10, SAL, 20, SAL, 30, SAL,0)) AS 토탈
 from emp 
 group by rollup(job)
 )
 group by (직업, "10","20", "30")
 order by 직업 ;
해석

부서번호 10, 20, 30을 가로로 출력하고 싶어서 sum+decode를 사용

sum(DECODE(DEPTNO, 10, SAL, 20, SAL, 30, SAL,0)) AS 토탈 는 부서번호 10, 20, 30의 월급들만 더하고싶어서 작성

from절 서브쿼리는 2번 부분 코드의 별칭을 사용하고싶어 사용

roll up 사용해서 직업별 토탈 월급을 출력했고, null값이 나오면 nvl(직업,'토탈')로 써주어서 직업 맨 밑에 토탈이라고 출력되게 함.

다른분들 답(roll up 사용)
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);
다른분들 답(grouping sets 사용)
select NVL(job, '토탈') as 직업, sum(decode(deptno, 10, sal, null)) as "10",
       sum(decode(deptno, 20, sal, null)) as "20",
       sum(decode(deptno, 30, sal, null)) as "30",
       sum(sal) as 토탈
  from emp
  group by grouping sets(job, ()) // job별로, 전체 () 별로 
  order by 직업;
rollup은 정렬이 그냥 ABC 순으로 되네!는줄 알았는데 grouping sets도 정렬 알아서 됨