01/09 국비학원 31회차 오라클수업(Join)

사원집합의 부서명은 존재하지 않는 컬럼명

제 3정규화를 지키기위한 테이블 설계

부서이름은 부서 번호에 종속적이지 사원번호에 종속적이지 않다.- 반정규화, 역 정규화

 

join이란?

둘 이상의 테이블을 연결하여 데이터를 검색하는 방법이다.

보통 둘 이상의 테이블을 연결하여 데이터를 검색하는 방법이다.

두 개의 테이블을 select문에서 결합

주로 where절에 조인 조건이라고 해서 사용한다.

 

조인은 세가지가 있다.

1. 내부조인  : 교집합

결국 inner join도 양쪽에 존재하는 값들만 검색되는 것이다.

 

2. 외부조인 : 합집합

두개(emp, dept) 이상의 테이블 조인시 한 쪽(dept) 테이블의 행(40)에 대해 다른쪽 테이블(emp)에 일치하는 행(10,20,30만 있었다.)이 없더라도 다른쪽 테이블의 행을 null로 하여 행을 리턴하는 것이다.

 outer join 연산자는 +이다.

40번에 해당하는 직원이 없다.

그러면 null로해서 리턴

 

3. ANSI조인

 

 

공통된 컬럼을 비교한다.

부서집합과 사원 집합은 서로 상속관계에있다.

두 집합의 관계형태는 1 : n  관계이다.

 

오라클과같은 관계지향형 특징은 관계를 통해서 조인이 필요할 수 밖에 없다.

 

select문 처리 순서
1. 파싱(parsing)- 문법 맞는지 여부
2. DBMS 가 실행계획을 세움
3. 실행계획을 옵티마이저에게 넘김
4. open...cursor를 이동 해당하는 곳에 데이터가 있으면 트루반환 아니면 폴스
    fetch....close로 위변조 방지
    
같은 셀렉트문을 두번 연속하여 요청하면 1단계와 2단계는 생략하고 3단계 진행됨 - 속도가 빠름

 

실제로 데이터를 찾는것은 옵티마이저가 한다.
일하는 기준(순서)이 있다.
2개 이상의 테이블이 오는 경우 - 조인
어떤 집합을 먼저 드라이브하는가에 따라서 검색 속도가 달라질 수 있다.
3초 안에 조회 결과가 나와야한다. 그렇지 않으면 에러로 취급이 된다.
속도문제-순서와 관계가 있고 실행계획을 통해서 확인이 가능하다.
all_rows라는 컨셉으로 실행계획을 세웠다.
 Hash join방식으로 데이터를 검색하였다.
 테이블을 각각 먼저 따로 스캔하고 조건을 따지면서 결과를 출력내보냄
 옵티마이저에게 rule base 옵티마이저 모드로 실행계획을 세워라.

 

/*+rule*/힌트문은 옵티마이저에게 개발자의 생각을 전달할 때 쓴다.

그 규칙을 우선해서 실행계획을 세워줘

 

CBO방식에 정한 순서와 다르게 검색을 한다.

EMP테이블 풀 스캔 -> dept deptno 스캔 ->사원테이블의 deptno와 같니?->검색하는 방법이다.

 

연습문제 - non-equal조인
temp와 emp_level을 이용해 emp_level의 과장 직급의 연봉 상한/하한 범위 내에
드는 직원의 사번과, 성명, 직급, salary를 읽어보자.

 

-- 어떤 테이블에서 읽어오는가에 따라 다른 의미가 되기도 한다.

콕 찝어서 지정을 해줘야된다.

외래키는 인덱스가 없다. 중복이 허용된다.

외래키는 인덱스가 제공되지 않는다.

외래키는 부모테이블과 자손테이블의 조인시 사용된다.

컬럼 정보를 출력할때에 index가 존재하는 컬럼을 조회하는것이 빠르다.

왜냐면 인덱스만 읽고도 데이터를 가져오니까

그리고 기본적으로 오름차순 정렬이 되어있어서 검색속도에 영향이 있다

외부조인 사용 (+)

부서번호 40이 나왔다. 사람은 없지만. null로 나옴

 

Outer Join연습문제

 

문제1

각 사번의 성명, 이름, salary, 연봉하한금액, 연봉상한금액을 보고자 한다.
temp와 emp_level을 조인하여 결과를 보여주되, 연봉의 상하한이 등록되어 있지 않은
'수습' 사원은 성명, 이름, salary 까지만이라도 나올 수 있도록 쿼리를 작성하시오.

temp에 없는 from_sal, to_sal을 불러오라했다. 오류이다.

모든 경우의 수가 다 나온다. 쓰레기값이 포함되었다.

나의 답=쌤의답

문제2

 

15개 너무 복잡하니까 3개만 해보자. 그래서 rownum<4 해주었다. 

나를 포함해서 하면 갯수가 등수가 된다.

오더바이로 정렬을 해 주었다.

 

rownum에 대한 연습문제
각 행에 1학년 부터 4학년 까지를 분리해서 한 행에 하나의 학년만 나오도록
하고자 한다. 현재 12행이 있으니 총 48행의 결과가 대학별/학과별로 인원수가
나와야 한다.

 

 

실전문제
다음 결과가 출력되는 SQL문을 작성하시오

숫자는 무시하기

각 직업의 부서별 급여 합계 구하기

 

첫번째 줄 만듦

 

셀프조인(self join)

-자기 자신과의 관계

 

관계형태중에서 n:n은 조언을 걸지 않는다.
왜냐면 쓰레기값이 포함되어있음
업무에 대한 정의가 덜 되어있다.
학생과 교과목, 회원과 상품, 회원과 책-> n:n인 관게형태를 갖음 -> 교차엔티티(행위 엔티티)
1:1, 1:n 고려대상

 

1.

 

tdept테이블에 자신의 상위 부서 정보를 관리하고 있다.
이 테이블을 이용하여 부서코드, 부서명, 상위부서코드, 상위부서명을 읽어오는
쿼리를 만들어 보자.

tdept테이블을 두개 붙였다

그리고 tdept a에 있는 부서코드는 b에 있는 부모코드와 같다. -> self join

 

부서코드 부서명을 b에서 가져왔다

a에서 상위부서코드를 가져왔다. 어떻게? 조건에 where절에서 a부서코드가 b부모코드랑 같다해놨음

 

 

 

 

2.

 

self조인 연습문제temp와 tdept를 이용하여 다음 컬럼을 보여주는 SQL을 만들어 보자.
상위부서가 'CA0001'인 부서에 소속된 직원을 1.사번, 2.성명, 3.부서코드
4.부서명, 5.상위부서코드, 6.상위부서명, 7.상위부서장코드, 8.상위부서장성명
순서로 보여주면 된다.