SQLD 요약 - (6) SQL활용_JOIN, 계층형질의,서브쿼리

FROM절 JOIN 형태

1. INNER JOIN 2. NATURAL JOIN 3. USING 조건절 4. ON 조건절 5. CROSS JOIN 6. OUTER JOIN

 

INNER JOIN

JOIN조건을 FROM절에서 정의, USING조건절, ON조건절 필수적

 

NATURAL JOIN

두 테이블 간 동일한 이름을 갖는 모든 컬럼에 대해 Equal Join 수행 (Using, ON 절 정의 불가, SQL Server 미지원)

JOIN에 사용된 컬럼들은 같은 데이터 유형이어야 하며, ALIAS나 테이블명과 같은 접두사를 붙일 수 없다.

💡 NATURAL JOIN은 JOIN에 사용된 같은 이름의 칼럼을 하나로 처리, INNER JOIN의 경우는 2개의 칼럼으로 표시한다.

 

USING 조건절

FROM 절에 USING 조건절을 이용하면 같은 이름을 가진 컬럼들 중에서 원하는 컬럼에 대해서만 선택적으로 EQUI JOIN(동등 조인)을 할 수 있다.

ex) SELECT * FROM DEPT JOIN DEPT_TEMP USING (DEPTNO);

💡 JOIN 칼럼에 대해서는 ALIAS나 테이블 이름과 같은 접두사를 붙일 수 없다.

 

ON 조건절

두 테이블 간 특정 칼럼으로 Equal Join 수행, 칼럼명 다르더라도 JOIN조건 사용할 수 있는 장점, ALIAS나 테이블명 반드시 사용

(ON 조건절 사용시 and 조건 추가 가능, where 절과 의미 구분 필요)

 

CROSS JOIN 

두 테이블의 Cartesian Product 임 (곱 조인) M*N건 조합 발생

 

OUTER JOIN (LEFT, RIGHT, FULL) 

JOIN 조건에서 동일한 값이 없는 행도 반환 가능하고 USING ON이나 조건절 반드시 사용

➡ LEFT OUTER JOIN : 먼저 표기된 좌측 테이블에 해당하는 데이터를 읽은 후 표기된 우측 테이블에서 JOIN 대상 데이터를 읽어 온다. 우측 값에서 같은 값이 없는 경우 NULL 값으로 채운다.

➡ RIGHT OUTER JOIN : LEFT OUTER JOIN의 반대

➡ FULL OUTER JOIN : 좌우측 테이블의 모든 데이터를 읽어 JOIN하여 결과를 생성하고 중복 데이터는 삭제한다.

 

SELF JOIN

동일 테이블 사이의 조인

ex) SELECT WORKER.EMPNO 사원번호, WORKER.ENAME 사원명, MANAGER.ENAME 관리자명 FROM EMP WORKER, EMP MANAGER WHERE WORKER.MGR = MANAGER.EMPNO;

 

---

집합 연산자

두 개 이상의 테이블에서 조인을 사용 하지 않고 연관된 데이터를 조회할 때 사용

SELECT 절의 칼럼 수가 동일하고 SELECT 절의 동 일 위치에 존재하는 칼럼의 데이터 타입이 상호 호환할 때 사용 가능

 

- UNION : 여러개의 SQL의 결과에 대한 합집합으로 결과에서 모든 중복된 행을 하나의 행으로 만든다. 

- UNION ALL : 중복된 행도 그대로 결과에 표시한다

- INTERSECT : 교집합 중복된 행은 하나의 행으로 만든다.

- MINUS : 차집합 중복된 행은 하나의 행으로 만든다.

 

---

계층형 질의 

테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해 사용

 

- START WITH : 계층 구조 전개의 시작 위치 지정

- CONNECT BY : 다음에 전개될 자식 데이터 지정

- PRIOR : CONNECT BY절에 사용되며 현재 읽은 칼럼을 지정한다.

        ✔PRIOR 자식 =부모 (부모->자식 방향으로 전개. 순방향 전개)

        ✔PRIOR 부모 =자식 (자식->부모 방향으로 전개. 역방향 전개)

- NOCYCLE : 동일한 데이터가 전개되지 않음

- ORDER SIBLINGS BY : 형제 노드간의 정렬 수행

- WHERE : 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출한다.( 필터링 )

- LEVEL : 루트 데이터이면 1, 그 하위 데이터면 2, 리프 데이터까지 1씩 증가

- CONNECT_BY_ISLEAF : 해당 데이터가 리프 데이터이면 1, 그렇지 않으면 0

- CONNECT_BY_ISCYCLE : 해당 데이터가 조상이면 1, 아니면 0 (CYCLE 아니면 옵션 사용했을 시만 사용 가능) 

- SYS_CONNECT_BY_PATH : 루트 데이터부터 현재 전개할 데이터까지의 경로를 표시한다.

- CONNECT_BY_ROOT : 현재 전개할 데이터의 루트 데이터를 표시한다.

 

ex) SELECT LEVEL, LPAD(' '. 4*(LEVEL-1) || EMPNO 사원, MGR 관리자, CONNECT_BY_ISLEAF ISLEAF FROM EMP START WITH MGR IS NULL CONNECT BY PRIOR EMPNO=MGR;

 

---

서브쿼리

➡ 하나의 SQL문안에 포함되어 있는 또 다른 SQL문

 

1) 서브쿼리는 메인커리의 칼럼을 모두 사용할 수 있지만 메인쿼리는 서브쿼리의 칼럼을 사용할 수 없다.

질의 결과에 서브쿼리의 칼럼을 표시해야 한다면 조인방식으로 변환하거나 함수, 칼라 서브 쿼리 등을 사용해야 한다.

2) 서브쿼리는 단일 행 또는 복수 행 비교 연산자와 함께 사용 가능하다.

단일 행 비교 연산자는 서브쿼리 의 결과가 반드시 1건 이하여야 하고 복수 행 비교 연산자는 결과 건수와 상관없다.

3) 서브쿼리에서는 ORDER BY 절을 사용하지 못한다.

ORDER BY 절은 SELECT 절에서 오직 한 개만 올 수 있기 때문에 ORDER BY 절은 메인쿼리의 마지막 문장에 위치해야 한다.

4) SELECT, FROM, WHERE, HAVING, ORDER BY, INSERT-VALUES, UPDATE-SET 절에 사용 가능

 

단일행 서브쿼리

서브쿼리가 단일행 비교 연산자 (=, <, <=, >, >=, <>)와 함께 사용할 때는 서브쿼리의 결과건수 가 반드시 1건 이하이어야 한다.

 

다중행 서브쿼리

서브쿼리의 결과가 2건 이상 반환될 수 있다면 반드시 다중행 비교연산자 (IN, ALL, ANY, SOME) 와 함께 사용해야 한다. ex) IN (서브쿼리), ALL(서브쿼리), ANY(서브쿼리), EXISTS(서브쿼리)

 

다중칼럼 서브쿼리

서브쿼리의 결과로 여러개의 칼럼이 반환되어 메인쿼리의 조건과 동시에 비교되는 것을 의미

ex) WHERE (TEAM_ID, HEIGHT) IN (SELECT TEAM_ID, MIN(HEIGHT) FROM PLAYER GROUP BY TEAM_ID)

 

동작 방식에 따른 서브쿼리 분류

▶ 비연관 서브쿼리 : 서브쿼리가 메인쿼리 칼럼을 가지고 있지 않는 형태

▶ 연관 서브쿼리 : 서브쿼리가 메인쿼리 칼럼을 가지고 있는 형태

💡 EXISTS서브쿼리는 항상 연관 서브쿼리로 사용된다. 

 

그 밖의 위치에서 사용하는 서브쿼리

▶ SELECT절에 서브쿼리 : 스칼라 서브쿼리 ( 한 행, 한 칼럼만을 반환하는 서브쿼리)

▶ FROM절에 서브쿼리 : 인라인 뷰(Inline Veiw)

▶ HAVING 절, UPDATE문의 SET절, INSERT문의 VALUES절에 서브쿼리 사용 가능.