---
title: "오라클 DBMS 정리 07"
date: "2018-11-07"
tags: ["오라클DBMS"]
status: archived
verified: false
---

## 오라클 DBMS 정리 07 - UNION, JOIN, SEQUENCE

---

### 테이블간 부모자식 관계를 만들려면

* PK, FK 설정
* 데이터의 중복을 최소화하기 위해서
    * 정규화
    * 용량 = 비용
    * 학생정보 테이블과 매니저정보 테이블이 붙어있다면 학생들에 중복된 매니저 정보가 저장됨
    * **테이블을 쪼개서 줄이자 = 정규화**
    * 매니저.매니저번호(PK) 1 - N 학생.매니저번호(FK)
        * FK는 매니저테이블의 매니저번호의 도메인만 값으로 가질 수 있음 또는 NULL을 가짐

---

### UNION

* 여러개의 테이블을 **아래로 붙여서** 조회
* **UNION, UNION ALL**
    * UNION
        * 여러 테이블 내에 중복값이 있으면 출력하지 않음
    * UNION ALL
        * 중복값도 출력
* **SQL Injection(취약점)이 발생할 수 있음**
* 주의사항
    * **조회되는 쿼리의 컬럼의 수와 데이터형이 일치해야 함**
        * 이름은 달라도 괜찮음

```sql
SELECT 컬럼명, ...
FROM   테이블명
UNION [ALL]
SELECT 컬럼명, ...
FROM   테이블명
```

```sql
-- UNION : 중복데이터가 조회되지 않음, PK가 있다면 정렬해서 보여줌

-- emp : 미국본사, cp_emp3 : 한국지사라 가정했을 때
-- 미국본사와 한국지사 연봉이 2000이하인 사원급여를 올려주고 싶어
-- 2000이하인 사원의 사원번호, 사원명, 연봉, 직무를 조회
SELECT empno, ename, sal, job
FROM   emp
WHERE  sal <= 2000
UNION
SELECT empno, ename, sal, job
FROM   cp_emp3
WHERE  sal <=2000;

-- cp_emp3에 emp테이블에서 30번 부서 사원정보를 모두 추가
INSERT INTO cp_emp3(empno, ename, deptno, sal, job)
(SELECT empno, ename, deptno, sal, job
 FROM   emp
 WHERE  deptno=30);

-- UNION ALL : 중복데이터가 조회됨, 정렬 안된채로 조회
SELECT empno, ename, sal, job
FROM   emp
WHERE  sal <= 2000
UNION ALL
SELECT empno, ename, sal, job
FROM   cp_emp3
WHERE  sal <=2000;

-- 컬럼의 갯수가 일치하지 않으면 에러
SELECT empno, ename, sal
FROM   emp
WHERE  sal <= 2000
UNION
SELECT empno, ename
FROM   cp_emp3
WHERE  sal <= 2000;
```

![07-01](/md/legacy-assets/oracle/07-01.webp)

```sql
-- 데이터형이 일치하지 않으면 에러
SELECT empno, ename, sal
FROM   emp
WHERE  sal <= 2000
UNION ALL
SELECT empno, ename, job
FROM   cp_emp3
WHERE  sal <=2000;
```

![07-02](/md/legacy-assets/oracle/07-02.webp)

---

### INTERSECT

* 교집합
    * 서로다른 테이블의 중복된 값을 조회
* 사용방법은 UNION과 같음

```sql
-- 미국본사의 사원정보와 한국지사의 사원정보 중 일치하는 정보만 조회
SELECT empno, ename, sal, job
FROM   emp
WHERE  sal <= 2000
INTERSECT
SELECT empno, ename, sal, job
FROM   cp_emp3
WHERE  sal <=2000;
```

---

### MINUS

* 차집합
    * 상위 SELECT 조회된 결과에서 하위 SELECT의 결과를 뺀 결과를 조회
* **조회되는 테이블의 순서가 중요!**
    * 상위 SELECT는 레코드의 수가 하위 SELECT 레코드 수보다 많아야 한다

```sql
-- MINUS : 상위 SELECT에서 조회된 결과에서 하위 SELECT에서 조회된 결과를 뺀 나머지를 조회
SELECT empno, ename, sal, job
FROM   emp
MINUS
SELECT empno, ename, sal, job
FROM   cp_emp3
WHERE  sal <=2000;
```

---

### JOIN
* 서로 다른 테이블을 합쳐 조회할 때 사용
    * **옆으로 붙는다**
* ANSI 쿼리 지원(길다), Oracle 쿼리 지원
* INNER JOIN, OUTERJOIN, CROSS JOIN, SELF JOIN 존재
    * **ORACLE 문법은 FULL OUTER JOIN을 지원하지 않는다(ANSI로만 가능)**
* **EQUI JOIN, NON EQUI JOIN 두가지 형태로 사용**
* 조인조건을 잘못 설정하면 **카티션프로덕트(Cartesian Product)**가 발생하고 나온 결과를 사용할 수 없다
* **DRIVING TABLE 선정에 따라 결과가 나오는 속도가 달라짐**
    * DRIVING TABLE : 조인되는 키로 사용되는 테이블


---

### INNER JOIN

* **조인하는 모든 테이블에 동일한 값이 있는 것만 조회**
    * EQUI JOIN/ NON EQUI JOIN은 조인조건에 따라 결정됨

```sql
-- 테이블간 같은 이름의 컬럼이 없으면 그냥 컬럼명으로 조회 -> 어느 테이블의 컬럼인지 확인이 힘들어 권장하지 않음! 
-- 테이블간 같은 이름의 컬럼명이 있을 땐 테이블명.컬럼명 으로 조회
-- 테이블명이 길다면 alias 붙일 수 있다 - 권장
-- 테이블은 8개 이상 조인하지 않는다(엄청 느려짐)

-- ANSI INNER JOIN
SELECT 테이블1.컬럼명, 조인할테이블.컬럼명, ...
FROM   테이블1 alias
INNER JOIN 조인할테이블 alias
   ON 조인조건;

-- Oracle INNER JOIN
SELECT 테이블1.컬럼명, 조인할테이블.컬럼명,...
FROM   테이블1 alias, 조인할테이블 alias
WHERE  (조인조건) AND 검색조건
```

```sql
-- 사원번호, 사원명, 부서번호, 부서명, 부서위치, 연봉 조회
-- ANSI INNER JOIN
SELECT     e.empno, e.ename, e.deptno,
           d.dname, d.loc, e.sal
FROM       emp e
INNER JOIN dept d
        ON e.deptno = d.deptno;

-- ORACLE INNER JOIN
SELECT e.empno, e.ename, e.deptno,
       d.dname, d.loc, e.sal
FROM   emp e, dept d
WHERE  e.deptno=d.deptno;
```

---


### DRIVING TABLE 선정

* DRIVING TABLE을 잘 정하면 많은 양의 데이터를 가져올 때 속도가 빠름
    * DRIVING TABLE : 조인되는 키로 사용되는 테이블
* **PK와 FK의 관계가 있을 때**
    * PK가 DRIVING TABLE이 되면 좋다
* **관계가 없는 테이블을 조회할 때**
    * 레코드가 적거나, 레코드의 다양성이 적은 컬럼이 DRIVING TABLE로 선정되는게 좋다.

```sql
SELECT     e.empno, e.ename, e.deptno,
           d.dname, d.loc, e.sal
FROM       emp e
INNER JOIN dept d
        ON e.deptno = d.deptno;
```


* d.deptno(PK), e.deptno(FK)
* '=' 오른쪽이 key, 얘를 기준으로 같은걸 뽑아냄
    * 검색시 사용되는게 KEY
* 예) e.deptno=**d.deptno**
    * d.deptno 10, 20, 30, 40. 4개의 레코드
    * 10을 갖고 e.dept와 비교, 20을 갖고 비교, 30을 갖고 비교, 40을 갖고 비교
        * 레코드 수 만큼 4번 비교
* 예) d.deptno=**e.deptno**
    * e.deptno가 키가 되면 20,10,20,10,30,20,10,... 이런식으로 14개의 레코드
    * 20가져와서 d.dept와 비교, 10갖고 비교, 20갖고 비교, ...
        * 레코드 수 만큼 14번 비교
* 결국 레코드의 수 만큼 비교를함
* **DRIVING TABLE로 레코드가 적은 테이블을 '키'로 선정해야 검색 속도가 빨라진다!**

---

### OUTER JOIN

* **어느 한 쪽에만 레코드가 있어도 조인결과를 보여줄 때 사용**
* LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN이 존재
    * **ORACLE JOIN 문법은 FULL OUTER JOIN을 지원하지 않음**
* 레코드의 구조를 잘 알아야 원하는 결과를 얻을 수 있음

```sql
-- ANSI
SELECT 컬럼명, 테이블명.컬럼명, alias.컬럼명, ...
FROM   테이블명
LEFT OUTER JOIN 조인할테이블명
             ON 조인조건
--RIGHT OUTER JOIN / FULL OUTER JOIN 바꿔서 사용할 수 있음
-- LEFT, RIGHT로 모두 보고 싶은 테이블(데이터가 있는 방향을) 설정
 

-- ORACLE
-- 조인조건에 레코드가 없는 테이블에 (+)를 붙여줌
-- FK에 붙여주면 됨
-- 조인조건 양쪽에 (+)를 붙이면 error 발생
SELECT 컬럼명, 테이블명.컬럼명, alias.컬럼명, ...
FROM   테이블명 alias, 테이블명2 alias, ...
WHERE  alias.컬럼명(+) = alias.컬럼명;
```

```sql
-- OUTER JOIN : 어느 한쪽 테이블에만 레코드가 존재하더라도 조회
-- 모든 부서의 부서번호, 부서명, 사원번호, 사원명, 입사일 조회
-- ANSI) LEFT나 RIGHT를 쓰되 레코드가 있는 쪽 테이블을 지정
-- 레코드의 구조를 잘 모른다면 FULL OUTER JOIN을 하여 양쪽 테이블 모두 조회가능
SELECT d.deptno, d.dname, e.empno, e.ename, e.hiredate
FROM dept d
LEFT OUTER JOIN emp e
  ON e.deptno = d.deptno;

-- ORACLE) 
SELECT d.deptno, d.dname, e.empno, e.ename, e.hiredate
FROM   dept d, emp e
WHERE  e.deptno(+) = d.deptno;
```

```sql
-- FULL OUTER JOIN은 ANSI문법만 지원(ORACLE 문법은 지원 안함)
SELECT d.deptno, e.deptno, d.dname, e.empno, e.ename, e.hiredate
FROM   dept d
FULL OUTER JOIN emp e
ON     e.deptno = d.deptno;
```

![07-03](/md/legacy-assets/oracle/07-03.webp)

---

### 헷갈리는 조인 시 포함되는 내용 재정리.. 


* ANSI
    * LEFT OUTER JOIN - 기존테이블을 기준으로 왼쪽으로 OUTER JOIN을 할거
    * RIGHT OUTER JOIN - 기존테이블을 기준으로 오른쪽으로 OUTER JOIN을 할거
* ORACLE
    * (+) 붙은 쪽이 레코드가 없고 OUTER JOIN으로 붙을거
    * (+) 가 추가될 테이블

---

### SELF JOIN

* 서브쿼리로 대체해서 사용 가능(하지만 길어짐)
* **테이블 하나를 사용하여 JOIN 할 때 사용**
    * **테이블을 조회목적과 비교목적 두가지로 구분하여 사용**
    * NON EQUI JOIN을 주로 사용

```sql
-- SELF JOIN은 두 테이블명이 같음, alias로 구분해서 사용
SELECT 컬럼명, 테이블명.컬럼명, alias.컬럼명, ...
FROM   테이블명 alias, 조인할테이블명 alias
WHERE  조인조건
```

```sql
-- 사원테이블에서 'SCOTT'인 사원보다 연봉을 작게 받는 사원의
-- 사원번호, 사원명, 입사일, 연봉을 조회

-- 서브쿼리를 사용하는 방법
SELECT empno, ename, hiredate, sal
FROM   emp
WHERE  sal < (SELECT sal
              FROM   emp
              WHERE  ename='SCOTT');

-- SELF JOIN을 사용하는 방법
-- 테이블 한개는 조회용으로만, 또다른 테이블은 비교(조건)용
SELECT e1.empno, e1.ename, e1.hiredate, e1.sal
FROM   emp e1, emp e2
WHERE  (e1.sal < e2.sal) AND e2.ename='SCOTT';
```

```sql
-- 조인한 복수행 서브쿼리로 얻어진 결과(INLINE VIEW)를 조회할 땐 컬럼명을 그냥 쓴다
SELECT ename, sal, dname
FROM(SELECT e.ename, e.sal, d.dname
     FROM   emp e, dept d);
```

![07-04](/md/legacy-assets/oracle/07-04.webp)

---

### SEQUENCE

* **증가하는 수를 반환하는 객체**
* user_sequences 딕셔너리에서 시퀀스를 조회가능
* **nextval, currval 가상 컬럼(pseudo column) 사용**
    * **nextvar**
        * File 영역에 존재하는 번호를 Server Instance 영역에 올리고 현재번호의 다음 번호를 얻는 일을 함
    * **currval**
        * Server Instance 영역에 존재하는 시퀀스의 현재번호를 얻는 일을 함
        * **Server Instance에 sequence가 올라와 있지 않으면 error 발생**
            * **nextvar이 먼저 사용되고 사용되어야 됨**
* 서버가 종료되면 일정개수의 수(CACHE)를 잃어버림
    * 기본적으로 20개
* **쿼리문이 실행 실패하더라도 번호는 소진됨**
    * 순차적인 번호를 얻을 순 있지만 넣을 순 없다.
* 증가하는 수를 가지고 PK를 사용해야할 때 좋음
* 생성
    * CACHE - 메모리에 올려놓을 수(기본 20개)
    * CYCLE은 끝값까지 사용하면 처음부터 반복실행할 것인지 결정(NO CYCLE이 기본)

```sql
CREATE SEQUENCE 시퀀스명 
 INCREMENT BY 증가값 
 START WITH 시작값
 MAXVALUE 끝값
 CACHE 메모리에올려놓을수
 CYCLE[NOCYCLE]
```

* 사용

```sql
시퀀스명.nextval
시퀀스명.currval
```

```sql
-- SEQUENCE : 순차적으로 증가하는 수를 사용하는 객체
-- 1에서부터 9999999까지 1씩 증가하는 시퀀스를 생성
CREATE SEQUENCE seq_test
 INCREMENT BY 1
 START WITH 1
 MAXVALUE 999999;
```

![07-05](/md/legacy-assets/oracle/07-05.webp)

```sql
-- nextval 안하고 먼저 currval 사용하면 error!
-- currval은 메모리에 올라와 있는 시퀀스의 현재 번호를 얻는 일을 함
SELECT seq_test.currval
FROM   DUAL;

-- 서버가 종료되면 메모리에 존재하던 시퀀스는 사라짐
-- 정의된 LAST_NUMBER부터 시작
```

![07-06](/md/legacy-assets/oracle/07-06.webp)

```sql
-- 쿼리문의 실행 실패에도 시퀀스는 사라진다
INSERT INTO cp_emp(empno, ename, hiredate, sal)
VALUES(seq_test.nextval, '안기모씨', SYSDATE, 300);

-- ename의 크기가 10이라 한글4글자가 안들어감, 위에 INSERT 쿼리 여러 시도 후 (실패)
-- 제대로 넣으면 실패한 만큼 시퀀스 수가 올라가 있는걸 확인 가능하다
```

```sql
-- 시퀀스 삭제
DROP SEQUENCE seq_test;
```

---


[숙제풀이](https://github.com/younggeun0/SSangYoung/blob/master/dev/query/date181030/homework.sql)