[SQL 기본과 활용] Join_Set Operation(★★★)

JOIN

E.F.CODD의 연산자

SQL의 많은 기능이 RDB이론을 수립한 E.F.CODD.박사의 논문에서 소개됨

4개의 일반집합연산자 + 4개의 순수관계연산자 (1문제 자주 나옴)

일반집합연산자의 SQL구현

UNION(합집합) → UNION / UNION ALL (공통집합의 중복 허용)

INTERSECTION(교집합) → INTERSECT

DIFFERENCE(차집합) → MINUS(Oracle) / EXCEPT(MS-SQL)

PRODUCT(곱집합) → CROSS JOIN

*곱집합은 JOIN으로 해결됨

순수관계연산자의 SQL구현

SELECT → WHERE절

*여러가지 RECORD중 원하는ROW만 고를 수 있어야 함(조건문)

PROJECT → SELECT절

*원하는 COLUMN을 고르는 것

(Natural) JOIN → 매우 다양한 JOIN으로 세분화되어 구현됨(★★★)

*다양한 테이블을 연결해서 하나로 합치는 것

DIVIDE → 현재 사용 X

정규화와 JOIN

정규화

이상현상(Anomaly) 발생을 피하기 위해 테이블을 분할

학계/실무에서 주로 3NF(3차 정규형) 사용

JOIN

데이터의 통합 조회를 위해 여러 테이블을 연결(테이블 구조 변경 X)

실제 JOIN연산은 두 개의 테이블에 대해서만 적용됨

일반적인 경우 PK/FK의 연관에 의해 JOIN이 성립

그 외에도 논리적인 값들의 연관성으로만 JOIN 성립 가능

ex)

SELECT ENAME, DNAME "2(순서). ENAME, DNAME 가져와라"

FROM EMP, DEPT "1(순서). EMP, DEPT 라는 테이블에서"

WHERE EMP.DEPTNO = DEPT.DEPTNO; "3(순서). EMP의 DEPNO와 DEPT의 DEPNO 가 같게 해서"

JOIN의 유형

Equi Join(=) / Non-Equi Join

암시적 조인 / 명시적 조인(JOIN 유무)

Inner Join / Outer Join / Cross Join / Self Join

EQUI JOIN(동등 조인)

JOIN 조건으로 Equal (=) 연산 사용

- 중복 칼럼의 경우, 칼럼명 앞에 테이블명을 붙여야함

"중복되지 않는 칼럼도 칼렴명 앞에 테이블 명을 붙이는 것을 권장"

예)선수명, 팀ID, 팀명을 출력

SELECT EMP.ENAME, EMP.DEPTNO, DEPT.DNAME

FROM EMP, DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO;

*ALIAS 사용

SELECT E.ENAME, E.DEPTNO, D.DNAME

FROM EMP E, DEPT D

WHERE E.DEPTNO = D.DEPTNO;

*접두어 일부 생략

SELECT ENAME, E.DEPTNO, DNAME

FROM EMP E, DEPT D

WHERE E.DEPTNO = D.DEPTNO;

*ALIAS를 사용했으면 SELECT/WHERE 절에서 본명을 사용하면 안됨

SELECT EMP.ENAME, EMP.DEPTNO, DEPT.DNAME

FROM EMP E, DEPT D

WHERE E.DEPTNO - D.DEPTNO;

>> ERROR

셋 이상의 테이블의 조인은 실제로는 두 테이블 간 조인이 연쇄적으로 일어남

SELECT P.PLAYER_NAME, P.POSITION, T.REGION_NAME, T.TEAM_NAME, S.STADIUM_NAME

FROM PLAYER P, TEAM T, STADIUM S

WHERE P.TEAM_ID = T.TEAM_ID AND T.STADIUM_ID = S.STAIUM_ID;

Non EQUI JOIN

조인 조건으로 Equal (=) 이외의 전산을 사용

BETWEEN, >, ≥, <, ≤ 등

예) 사원별 급여 등급 조회

Q) EMP 테이블과 SALGRADE 테이블로부터 사원명, 급여, 급여 등급을 출력하는 질의를 완성하시오

SELECT E.NAME 사원명, E.SAL 급여, S.GRADE 급여등급

FROM EMP E, SALGRADE S

WHERE S.LOSAL ≤ E.SAL ≤ S.HISAL;

INNER JOIN (내부 조인) "교집합이라고 생각"

"연결되는 값이 없거나 대치되는 값이 없으면 무시!!"

서로 대응되는 내용만 검색하는 조인

조건절을 필수로 사용

조인의 DEFAULT값 이므로 'INNER' 생략 가능

INNER JOIN = JOIN

SELECT E.ENAME, E.DEPTNO, E.SAL, D.DNAME

FROM EMP E, DEPT D*

WHERE E.DEPTNO = D.DEPTNO AND E.SAL > 2000;

*이렇게 표시된 경우 조인조건과 일반조건 구분가능??

조인 조건과 일반 조건이 혼용되어 가독성이 떨어짐

→ 명시적 조인( = 표준 조인) 의 필요성

명시적 조인에서는 조인 관련 조건은 ON 절에, 그 외에 조건은 WHERE 절에 기술함

대부분의 DBMS는 명시적 조인을 표준으로 채택하지만, 기존의 암시적 조건도 허용

SELECT E.ENAME, E.DEPTNO, E.SAL, D.DNAME

FROM EMP E (INNER) JOIN DEPT D *두 테이블은 INNER JOIN 해라

ON E.DEPTNO = D.DEPTNO *조인 조건

WHERE E.SAL > 2000; *일반 조건

NATURAL JOIN

INNER JOIN 의 특수한 경우

NATURAL INNER JOIN = NATURAL JOIN

두 테이블 간 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI JOIN 수행

칼럼 간 데이터 타입도 동일해야 함

별도의 조인 칼럼 및 조건을 지정할 수 없음

조인의 대상이 되는 칼럼에는 접두사(테이블명 또는 ALIAS)를 사용할 수 없음

Q) 다음 NATURAL JOIN 과 동일한 출력을 갖는 INNER JOIN 질의를 작성하시오

SELECT EMPNO, ENAME, DEPTNO, DNAME

FROM EMP NATURAL JOIN DEPT;

SELECT EMPNO, ENAME, EMP.DEPTNO, DNAME

FROM EMP INNER JOIN DEPT

ON EMP.DEPTNO = DEPT.DEPTNO;

*INNER JOIN의 경우 중복되는 COLUMN에 대해 반드시 접두어를 써주어야 하고 NATURAL의 경우 접두어 쓰면 안됨!!!

Q) INNER JOIN 과 NATURAL JOIN 의 실행결과를 비교하시오

칼럼 출력 순서 및 칼럼 갯수

SELECT *

FROM EMP NATURAL JOIN DEPT;

→중복칼럼 맨 앞으로 보내고 중복된거 표시 안됨

SELECT *

FROM EMP INNER JOIN DEPT

ON EMP.DEPTNO = DEPT.DEPTNO;

ON 조건절

암시적 JOIN

모든 조건을 WHERE절에 기술

명시적 JOIN

JOIN 기준 조건은 ON절에 기술

ON 절의 괄호는 생략 가능

JOIN과 무관한 일반조건은 WHERE 절에 기술

예) 이름에 'S'를 포함하는 사원의 사원이름, 부서코드 부서명 출력

SELECT E.EMPNO, E.ENAME, E.DEPTNO, D.DNAME

FROM EMP E JOIN DEPT D

ON (E.DEPTNO = D.DEPTNO)

WHERE E.ENAME LIKE '%S%';

USING 조건절

ON 절의 "=" 연산자 대신 USING 절 사용가능

ON 절에서는 괄호 생략 가능, USING 서는 괄호 생략 불가

접두사(테이블명 또는 ALIAS )를 사용할 수 없음

SELECT E.NAME, E.DEPTNO, D.DNAME

FROM EMP EJOIN DEPT D

ON (E.DEPTNO = D.DEPTNO);

SELECT E.NAME, E.DEPTNO, D.DNAME

FROM EMP EJOIN DEPT D

USING (DEPTNO);

WHERE, IN, USING 절 조건 기술 비교(★)

OUTER 조인 ( 외부조인 ) *INNER JOIN 의 반대

서로 대응되지 않는 행도 출력하는 조인

조건절을 필수로 사용

성능 저하의 원인이 될 수 있으므로 필요한 경우에만 사용

LEFT, RIGHT, FULL (OUTER) JOIN

LEFT OUTER JOIN

왼쪽 테이블의 데이터를 모두 읽은 후, 오른쪽 테이블에서 JOIN 데이터를 가져옴

오른쪽 테이블이 JOIN 조건에 해당되지 않는 경우, 해당 칼럼은 NULL로 채움

RIGHT OUTER JOIN

오른쪽 테이블의 데이터를 모두 읽은 후 왼쪽 테이블에서 JOIN 데이터를 가져옴

왼쪽 테이블이 JOIN 조건에 해당되지 않는 경우 해당 칼럼은 NULL로 채움

FULL OUTER JOIN

양쪽 테이블의 데이터를 모두 읽은 후 상대 테이블에서 JOIN 데이터를 가져옴

JOIN에 해당하지 않는 경우 해당 칼럼은 NULL로 채움

RIGHT OUTER JOIN 과 LEFT OUTER JOIN의 합집합과 동일(중복 제거 후)

*UNION ALL(중복제거 X) 이 아닌 UNION(중복제거하면서 합치기)과 동일

SELECT E.ENAME, E.DEPTNO, D.DNAME

FROM EMP E FULL OUTER JOIN DEPT D

ON E.DEPTNO = D.DEPTNO;

SELECT E.ENAME, E.DEPTNO, D.DNAME

FROM EMP E LEFT OUTER JOIN DEPT D

ON E.DEPTNO = D.DEPTNO

UNION

SELECT E.ENAME, E.DEPTNO, D.DNAME

FROM EMP E RIGHT OUTER JOIN DEPT D

ON E.DEPTNO = D.DEPTNO;

CROSS JOIN ( 교차조인)

두 테이블의 곱집합 ( Cartesian Product)을 출력하는 조인

별도의 조인 조건이 없음

*RIGHT OUTER JOIN 주의!! (중복되어 나오는것도 생각)

다 더한게 FULL

SELF JOIN ( 셀프 조인)

동일한 테이블(하나의 테이블) 사이의 조인

FROM 절에 동일 테이블이 두 번 이상 나타남

테이블 식별을 위해 반드시 별칭(ALIAS) 사용해야 함

동일한 테이블을 개념적으로 서로 다른 두 개의 테이블로 사용함

예 ) FROM EMP E INNER JOIN EMP M

계층형 데이터

동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터

엔터티가 순환관계 모델로 설계된 경우 발생

계층형 질의(Hierarchical Query)를 통해 접근 가능

계층형 질의의 방향

순방향(부모→자식), 역방향(자식→부모)

계층형 질의의 구조

START WITH - 시작조건 지정

예) START WITH MGR IS NULL

예) START WITH EMPNO = 'D'

CONNECT BY - 다음에 전개될 방향 지정

(순방향) PRIOR 자식 = 부모

예) CONNECT BY PRIOR EMPNO = MGR

(역방향) PRIOR 부모 = 자식

예) CONNECT BY PRIOR MGR = EMPNO

SELECT 칼럼명

FROM 테이블명

WHERE 조건

START WITH 시작조건

CONNECT BY PRIOR 방향

순방향 계층형 질의 예

SELECT LEVEL, EMPNO 사원, MGR 관리자, CONNECT_BY_ISLEAF ISLEAF

FROM EMP

START WITH MGR IS NULL

CONNECT BY PRIOR EMPNO = MGR;

*LEVEL : 시작노드 = 1, LEAF까지 1씩 증가

*CONNECT_BY_ISLEAF : 해당 노드의 후속 노드가 없으면 1

즉 LEAF면 1, 그렇지 않으면 0

SELECT CONNECT_BY_ROOT EMPNO 시작사원, SYS_CONNECT_BY_PATH(EMPNO,'/') 경로, EMPNO 사워느 MGR 관리자

FROM EMP

START WITH EMPNO in (7566 , 7698, 7782)

CONNECT BY PRIOR EMPNO = MGR;

*CONNECTED_BY_ROOT : 시작 노드의 해당 칼럼 표시

*SYS_CONNECTED_BY_PATH : 시작 노드부터 현재 노드까지 경로 표시

역방향 계층형 질의 예

SELECT LEVEL, EMPNO 사원, MGR 관리자, CONNECT_BY_ISLEAF ISLEAF

FROM EMP

START WITH EMPNO = '7876'

CONNECT BY PRIOR EMPNO = MGR;

집합 연산자 개요

여러 질의(SELECT 문) 결과를 하나로 통합하기 위해 사용

집합 연산의 대상이 되는 두 질의는..

SELECT 절의 칼럼 수가 동일해야 하고

SELECT 절의 동일 위치에 존재하는 칼럼의 데이터 타입이 상호 호환 가능해야 함

*반드시 동일한 데이터 타입일 필요는 없음

집합연산자

UNION : 여러 SQL문의 결과에 대한 합집합 ( 중복된 행은 제거한 후 하나의 행만 출력 )

UNION ALL : 여러 SQL문의 결과에 대한 합집합 ( 중복된 행은 삭제하지 않고 모두 출력 → 속도가 빠르므로 우 선 고려)

INTERSECT : 여러 SQL문에 대한 교집합 ( 중복된 행은 제거 후 하나의 행만 출력)

MINUS(Oracle) / EXCEPT (MS-SQL) : 앞의 SQL문의 결과에서 뒤의 SQL문의 결과를 뺀 차집합 (중복 행 제거)

집합 연산 질의 예

집합 연산은 둘 이상의 SELECT 문을 결합하는 것

ORDER BY 는 집합 연산을 적용한 최종 결과에 대한 정렬

맨 마지막 줄에 한번만 기술함

UNION ALL

이질적 성격의 데이터를 한꺼번에 출력하는 연산도 가능(상수를 써서 구분)

SELECT 'T' 구분코드, PLAYER_NAME, TEAM_ID

FROM PLAYER

WHERE TEAM_ID = 'K06'

UNION ALL

SELECT 'P' 구분코드, PLAYER_NAME, TEAM_ID

FROM PLAYER

WHERE POSITION = 'GK'

ORDER BY 구분코드, TEAM_ID;