[SQLD]SQL 기본 및 활용(2)

표준조인(Standard Join)

1) INNER JOIN

동일한 값이 있는 행만 반환된다. 

반드시 USING이나 ON조건절을 동반한다.

SELECT  A.ID A.NAME B.ID B.DATE
FROM STUDENT A INNER JOIN REGISTER B
ON A.ID=B.ID;

2) NATURAL JOIN

SQL Server은 지원하지 않는 기능이며 where절에서 JOIN조건은 정의 할 수 없다.

일치되는 모든 컬럼들에 대해서 JOIN이 이루어진다.

별명사용이 불가하다고 한다.

SELECT ID NAME DATE
FROM STUDENT NATURAL JOIN REGISTER;

 

3) USING 조건절

이친구도 별명사용이 불가하다고 한다.

동일한 이름을 가진 컬럼중 원하는 컬럼을 선택해서 조인할 수 있다. SQL Server은 지원하지 않는다.

SELECT ID NAME DATE
FROM STUDENT JOIN REGISTER
USING (ID);

 

4) ON 조건절

이친구는 별명 쓰는게 가능하다. 이친구는 USING과 다르게 컬럼명이 달라도 조인을 사용할 수 있지만 정확하게 어느 테이블의 컬럼인지 지정해주어야한다.

SELECT  A.ID A.NAME B.ID B.DATE
FROM STUDENT A JOIN REGISTER B
ON (A.ID=B.ID);

 

(+)

ON 조건절은 WHERE절과 충돌 없이 사용 가능하다

SELECT  A.ID A.NAME B.ID B.DATE
FROM STUDENT A JOIN REGISTER B
ON (A.ID=B.ID)
WHERE B.DATE = '20210831';

WHERE과 큰 차이점은 WHERE은 해당 절을 추출하지만, ON 조건절은 해당 부분만 JOIN하고 나머지 부분도 존재한다는 것이다.

 

5) 다중 테이블 JOIN

SELECT  A.ID A.NAME B.ID B.DATE
FROM STUDENT A JOIN REGISTER B
ON A.ID=B.ID
JOIN PROFESSOR C
ON B.PROFESSOR_ID = C.ID;

SELECT  A.ID A.NAME B.ID B.DATE
FROM STUDENT A, REGISTER B
WHERE A.ID=B.ID;

 

 

6) CROSS JOIN

두 테이블간 JOIN조건이 없는경우 모든 경우의 데이터 조합을 말한다.

CARTESIAN PRODUCT나 CROSS PRODUCT라고 부르기도 한다.

SELECT  A.ID A.NAME B.ID B.NAME
FROM STUDENT A, PROFESSOR B
ORDER BY A.ID;

 

 

7)OUTER JOIN

LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN

해당 방향쪽으로 조인시 없는 데이터도 다 포함해서 출력된다.

 

 

 

 

 

 

---

오라클에서의 JOIN

LEFT OUTER JOIN

SELECT  A.ID A.NAME B.ID B.NAME
FROM STUDENT A, PROFESSOR B
WHERE A.ID = B.ID(+);

RIGHT OUTER JOIN

SELECT  A.ID A.NAME B.ID B.NAME
FROM STUDENT A, PROFESSOR B
WHERE A.ID = B.ID(+);

FULL OUTER JOIN은 표현할 수 없다.

 

 

 

 

 

---

집합 연산자

1) UNION (중복을 포함하지 않는 합집합)

중복되지 않는 데이터는 포함하지 않는다.

원래 있던 중복도 없어진다.

앞에나온 테이블 명을 따라간다.

SELECT ID, NAME
FROM STUDENT
UNION
SELECT ID, NAME
FROM OTHER_STUDENT;

2) UNION ALL (중복을 포함하는 합집합)

중복되는 데이터도 포함한다.

앞에나온 테이블 명을 따라간다.

SELECT ID, NAME
FROM STUDENT
UNION ALL
SELECT ID, NAME
FROM OTHER_STUDENT;

3) INTERSECT (교집합)

SELECT ID, NAME
FROM STUDENT
INTERSECT
SELECT ID, NAME
FROM OTHER_STUDENT;

4) MINUS (차집합)

SELECT ID, NAME
FROM STUDENT
MINUS
SELECT ID, NAME
FROM OTHER_STUDENT;

 

 

 

 

---

계층형 쿼리

상하 수직관계의 트리형태의 구조로 이루어진 형태

SELECT 컬럼
FROM 테이블명
WHERE 조건
START WITH 최상위 조건
CONNECT BY [NOCYCLE][PRIOR 계층형 구조 조건];

https://coding-factory.tistory.com/461 << 좋은 예시! 이해안가면 다시보기

[Oracle] 오라클 계층형 쿼리(START WITH.. CONNECT BY)

용어	기능
START WITH	최상위노드
CONNECT BY PRIOR A = B	B->A방향으로 데이터가 전개됨 대체로 B가 부모, A가 자식이다
ORDER SIBLINGS BY	계층 내에서 정렬 가능

---

뷰(View)

장점

1. 독립성 : 테이블 구조가 변경되어도 뷰를 사용하는 응용프로그램은 변경하지 않아도 된다.

2. 편리성 : 복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성할 수 있다. 

3. 보안성 : 직원의 급여정보와 같이 숨기고 싶은 정보는 사용자로부터 감출 수 있다.

 

 

---

그룹함수

 

https://velog.io/@dongchyeon/%EC%98%A4%EB%9D%BC%ED%81%B4Oracle-%EA%B7%B8%EB%A3%B9-%ED%95%A8%EC%88%98-ROLLUP-CUBE-GROUPING-%EB%93%B1

[오라클(Oracle)] 그룹 함수 (ROLLUP, CUBE, GROUPING 등)

https://myjamong.tistory.com/173

[Oracle] 오라클 GROUP BY ROLLUP, CUBE, GROUPING SETS 정리 :: 마이자몽

 

---

옵티마이저와 실행계획

사용자가 질의한 SQL문에 대해 최적의 실행방법을 결정하는 역할

그리고 그 최적의 실행방법을 실행계획이라 한다.

어디까지나 계획이기 때문에 실제 정보와 다를 수 있다.

 

-규칙기반 옵티마이저(RBO, Rule Based Optimizer)

우선순위가 높은 규칙이 적은 일량으로 해당 작업을 수행하는 방법

제일 낮은 우선순위는 전체 테이블 스캔이고 제일 높은 우선순위는 ROWID,인덱스를 활용해서 테이블을 액세스하는 방법이다.

-비용기반 옵티마이저(Cost Based Optimizer)

SQL문을 실행하는데 소요될 처리시간 및 CPU, I/O 자원량 등을 계산하여 가장 효율적일 것으로 예상되는 실행계획을 선택

 

---

INDEX

원하는 데이터를 속도적인 측면에서 빠르게 찾을 수 있도록 만든 것

DML작업시 INDEX도 변경해야 하기 때문에 오히려 느려질 수 있음에 주의해야한다.

 

종류

1) B-트리 인덱스

   브랜치 블록과 리프블록으로 구성되며, 브랜치 블록은 분기를 목적으로 하고, 

   리프블록은 인덱스를 구성하는 컬럼의 값으로 정렬된다.

   일반적으로 OLTP시스템 환경에서 많이 사용된다.

 

2) 비트맵 인덱스

   시스템에 사용될 질의를 시스템 구현 시에 모두 알 수 없는 경우인 DW 및 AD-HOC 질의 환경을 위해서

   설계되었으며, 하나의 인덱스 키 엔트리가 많은 행에 대한 포인터를 저장하고 있는 구조

 

3) 클러스터드 인덱스

   인덱스의 리프 페이지가 곧 데이터 페이지이며, 리프 페이지의 모든 데이터는

   인덱스 키 컬럼 순으로 물리적으로 정렬되어 저장