sqld 2-2 sql 활용(1)

일반 집합 연산자

(1) Union
수학에서 말하는 흔히 합집합연산이라고 보면 된다.
공통 교집합의 중복을 없애기 위한 사전작업으로 정렬 작업을 먼저 수행한다. 그리고 합집합연산을 수행한다.
cf) Union all :공통집함을 중복해서 그대로 보여줌 이때 정렬 작업이 일어나지 않는다.

(2) Intersection
교집합이라고 할 수 있으며 두 집합의 공통부분을 추출한다.

(3) Difference
차집합이라고 할 수 있으며 첫 번째 집합에서 두 번째 집합과의 공통집합을 제외하게 된다.

(4) Product
Cross Product라고 부르는 곱집합이며 join조건이 없는 경우 생길 수 있는 모든 데이터의 조합이 출력된다.

순수 관계 연산자

(5) Select
여기서는 select 연산을 말하는 것이다. WHERE 절의 조건절 기능으로 구현한다.

(6) Project
select 절의 칼럼을 선택할때 쓰인다.

(7) (Natural) JOIN

WHERE절 : INNER JOIN
FROM절 : NATURAL JOIN, INNER JOIN, OUTER JOIN, USING/ON 조건절 등으로 많이 쓰인다.

(8) Divide
-> 현재 사용되지 않음

join절이 흩어진 데이터들을 연결해서 원하는 데이터로 가져오게 할때 쓰이고 관계형 데이터베이스에 있어서 JOIN은 SQL에서 가장 중요한 기능이라고 볼 수 있기때문에 반드시 이해해야된다.

INNER JOIN

join 조건에서 동일한 값만 있는 행만 반환한다. 조건정의를 할때 FROM절에서 정의할때에는 USING 조건절이나 ON절을 꼭 써줘야 한다.

USING 조건절

FROM절에서 USING 조건절을 이용하면 같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 equi join을 할 수 있다.
별도의 칼럼 순서를 지정하지 않으면 USING 조건절의 기준이 되는 칼럼이 먼저 출력된다.
USING 절의 열 부분은 식별자를 가질 수 없다. 즉 ALIAS나 테이블 이름을 앞에 붙일 수 없다.

ON 조건절

JOIN (ON 조건절)와 비 JOIN (WHERE 조건절)를 분리하여 이해가 쉽고 칼럼명이 다르더라도 JOIN 조건을 사용할 수 있다는 장점이 있다.
임의의 조건을 추가하거나 이름이 다른 칼럼명을 JOIN 조건으로 사용하거나 JOIN 칼럼을 명시하기 위해서는 ON 조건절을 사용한다.

예시를 보면

SELECT E.EMPNO, E.NAME, E.DEPTNO, D.DNAME FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO);

사원 테이블과 부서 테이블에서 사원 번호와 사원 이름, 소속부서 코드, 소속부서 이름을 출력하기 위해서 코드를 입력하였다.
USING절에서 테이블 명과 같은 접두사를 앞에 붙힌다면 오류가 발생하지만 SELECT에 사용되는 칼럼을 논리적으로 명확하게 하기 위해서 ON절 뒤에는 위에 처럼 꼭 접두사를 앞에 붙여줘야한다. 반드시는 아니지만 붙여주는것을 추천한다.

where 절과 혼용
ON 조건은 where과 충돌없이 사용할 수 있다는 특징이 있다.

SELECT  E.EMPNO, E.NAME, E.DEPTNO, D.DNAME FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO) WHERE E.DEPTNO = 30 ;

부서코드가 30인 부서의 소속사원 데이터행만 추출하였다.
where 절을 사용 하지 않고도 ON 조건절에 데이터 검증 조건을 추가하는 방법이 있다. 하지만 검색 조건 목적인 경우는 WHERE 절을 사용할 것을 추천한다.

CROSS JOIN

PRODUCT 곱집합의 개념으로 테이블간 join 조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 뜻한다. cartesian product , cross product와 같은 표현이다.

OUTER JOIN

inner join과 다르게 JOIN 조건에서 동일한 값이 없는행도 반환할 때 사용할 수 있다.

LEFT OUTER JOIN

join 수행시 먼저 표기된 테이블에 해당하는 데이터를 먼저 읽은 후 나중에 표기된 테이블에서 JOIN 대상 데이터를 읽어 온다. 만약 나중에 표기된 테이블에서 해당 값이 없는 경우는 NULL으로 채운다.
OUTER를 생략하고 LEFT JOIN으로 표기할 수 있다.

RIGHT OUTER JOIN

join 수행시 나중에 표기된 테이블이 기준이 되어 결과를 만든다. 먼저 표기된 테이블에서 해당 값이 없는 경우 NULL으로 채운다.
OUTER를 생략하고 RIGHT JOIN으로 표기할 수 있다.

FULL OUTER JOIN

join 수행시 먼저 표기된 테이블, 나중에 표기된 테이블의 모든 데이터를 읽어 조인하여 결과를 만든다. 즉 두 개의 합집합이라고 볼 수 있다.

계층적 질의와 셀프 조인

계층형 질의

계층형 데이터란 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 말한다. 만일 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 계층형 질의를 사용한다.

오라클에서 계층형 질의의 예시이다.

SELECT ... FROM 테이블 WHERE condition AND condition... 
START WITH condition CONNECT BY condition [ORDER SIBLINGS BY 칼럼, 칼럼....]

start with 구문은 계층의 루트로 사용될 행을 지정한다. 어떤 행을 최상위로 할지 결정한다.
connect by 구문은 다음에 전개될 자식 데이터를 지정하는 구문이다. 연결고리를 만들때 사용되는데 prior 연산자로 계층구조를 표현할 수 있다.

• prior 자식 = 부모 (순방향)
• prior 부모 = 자식 (역방향)
• NOCYCLE : 데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개 중에 다시 나타난다면 사이클이 형성되었다고 말한다. 이렇게 되면 오류가 발생하게 되는데 NOCYCLE을 추가하면 사이클이 발생한 이후의 데이터는 전개하지 않는다.
• ORDER SIBLINGS BY : 동일 레벨 사이에서 정렬을 수행한다.

오라클에서 계층형 질의에서 사용되는 가상칼럼이다.

LEVEL : 루트 데이터면 1 그 하위 데이터면 2이다. 리프 데이터로 가면 1씩 증가한다.
CONNECT BY ISLEAF : 전개 과정에서 해당 데이터가 리프 데이터면 1 그렇지 않으면 0이다.
CONNECT BY ISCYCLE : 전개 과정에서 해당 데이터가 자신으로부터 루트까지의 경로에 존재한다면 1, 그렇지 않으면 0을 표시한다. (CYCLE 옵션을 사용했을때만 사용가능)

셀프 조인

셀프조인이란 동일 테이블 사이의 조인을 말한다. 동일 테이블 사이의 조인을 수행한다면 테이블과 칼럼 이름이 같기 때문에 식별을 위해 반드시 별칭을 사용해야 된다. 칼럼에도 테이블 별칭을 사용해서 어느 테이블 칼럼인지 식별해줘야 한다.