[SQLD] SQL 활용 - 계층형 조회, 서브쿼리

< 계층형 조회(Connect by) >

- Oracle 데이터베이스에서 지원하는 것으로 계층형으로 데이터를 조회할 수 있음

ex) 부장에서 차장, 차장에서 과장, 과장에서 대리, 대리에서 사원 순으로 트리 형태의 구조를 위에서 아래로 탐색하면서 조회하는 것

(역방향도 가능)

- Connect by는 트리 형태의 구조로 질의를 수행하는 것

- START WITH 구는 시작 조건을 의미

- CONNCT BY PRIOR는 조인 조건

- Root 노드로 부터 하위 노드의 질의를 실행

 

-  최대 계층 수 구하기

SELECT MAX(LEVEL) // LEVEL은 계층값을 가짐
FROM EMP2
START WITH MGR IS NULL // 가장 상위 관리자인 경우
CONNECT BY PRIOR EMPNO = MGR; // 자신의 관리자 직원을 찾음

 

SELECT 컬럼명

FROM 테이블명

START WITH 시작 조건

CONNECT BY PRIOR 조인 조건;

 

 

- 계층을 조회 결과를 직관적으로 보기 위한 LPAD 함수

 

SELECT LEVEL, LPAD(EMPNO,4 * (LEVEL),' '),MGR,CONNECT_BY_ISLEAF
FROM EMP2
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;

* LPAD 함수 : LPAD('값', 만들어질 문자 길이, '채움문자')

 

트리 형태로 조회 가능

 

-CONNECT BY 키워드

키워드	설명
LEVEL	검색 항목의 깊이를 의미 즉, 계층 구조에서 가장 상위 레벨 1이 됨
CONNECT_BY_ROOT	계층 구조에서 가장 최상위 값을 표시
CONNECT_BY_ISLEEF	게층 구조에서 가장 최하위를 표시
SYS_CONNECT_BY_PATH	계층구조의 전체 전개 경로를 표시
NOCYCLE	순환구조가 발생지점까지만 전개됨
CONNCET_BY_ISCYCLE	순환구조 발생 지점을 표시

 

 

 

< 서브쿼리(Subquery) >

1. Main query와 Subquery

- Subquery는 SELECT문 내에 다시 SELECT문을 사용하는 SQL문

- 인라인뷰 : FROM구에 SELECT문을 사용하는 Subquery의 형태

- 스칼라 서브쿼리 : SELECT문에 Subquery를 사용하는 형태

- WHERE구에 SELECT문을 사용하면 Subquery라고 함

SELECT * // Main Query
FROM EMP
WHERE DEPTNO = 
    (SELECT DEPTNO FROM DEPT WHERE DEPTNO = 1001); // Subquery

 

SELECT *
FROM (SELECT ROWNUM NUM, ENAME FROM EMP ) // 인라인뷰
WHERE NUM <5 ;

 

 

2. 단일 행 서브쿼리와 다중 행 서브쿼리

- 서브쿼리는 반환하는 행 개수가 한 개, 여러 개인 것에 따라 단일 행 서브쿼리&멀티 행 서브쿼리로 나뉨

 

- 서브쿼리 종료 (반환 행)

서브쿼리 종류	설명
단일 행 서브쿼리	- 하나의 행만 반환하는 서브쿼리 - 비교 연산자 =, <, <=, >=, <> 를 사용
멀티 행 서브쿼리	- 여러개의 행을 반환하는 서브쿼리 - 다중행 비교 연산자 IN, ANY, ALL, EXISTS를 사용

 

 

 

3. 다중 행 Subquery

- 다중 행 서브쿼리는 서브쿼리 결과가 여러 개의 행을 반환하는 것으로 다중 행 연산자를 사용해야함

다중 행 연산	설명
IN (Subquery)	- Main query의 비교 조건이 Subquery의 결과 중 하나만 동일하면 참 (OR 조건)
ALL (Subquery)	- Main query와 Subquery의 결과가 모두 동일하면 참 - < ALL : 최솟값을 반환 - > ALL : 최댓값을 반환
ANY (Subquery)	- Main query의 비교조건이 Subquery의 결과 중 하나 이상 동일하면 참 - < ANY : 하나라도 크게 되면 참 - > ANY : 하나라도 작게 되면 참
EXISTS (Subquery)	Main query와 Subquery의 결과가 하나라도 존재하면 참

* IN, ANY 는 하나라도 값을 만족하면 TRUE (ANY는 비교 연산자를 쓸 수 있다는 점에서 다름)

* ALL 전체값을 모두 만족해야 TRUE

* EXISTS는 IN과 비슷하지만 서브쿼리에만 사용가능하고 성능이 더 좋음

 

 

IN

- 반환되는 여러 개의 행 중에서 하나만 참이 되어도 참이 되는 연산

SELECT b.DEPTNAME, ENAME, JOB, SAL
FROM EMP2 a, DEPT2 b
WHERE a.DEPTNO = b.DEPT
AND a.EMPNO IN (SELECT EMPNO FROM EMP2 WHERE SAL > 2000)
order by b.DEPTNAME ;

서브쿼리에서 급여가 2000보다 큰 사원번호를 조회

조인한 메인쿼리의 사원번호와 비교해 모두 조회

 

 

ALL

- 메인쿼리와 서브쿼리의 결과가 모두 동일하면 참

SELECT *
FROM EMP2
WHERE DEPTNO <= ALL (20,30);

DEPTNO 이 20,30보다 작거나 같으면 조회

 

 

EXISTS

- Subquery로 어떤 데이터 존재 여부를 확인 (즉, 참과 거짓이 반환)

SELECT b.DEPTNAME, ENAME, JOB, SAL
FROM EMP2 a, DEPT2 b
WHERE a.DEPTNO = b.DEPT
AND EXISTS (SELECT 1 FROM EMP2 WHERE SAL > 2000);

급여가 2000보다 큰 사원이 있으면 TRUE가 조회 (참, 거짓만 판별)

 

 

< 스칼라(Scala) Subquery >

- 스칼라 Subquery는 반드시 한 행과 한 칼럼만 반환하는 서브쿼리 

- 만약 여러 행이 반환되면 오류가 발생 (SELECT문에 사용하기 때문)

SELECT ENAME AS "이름", SAL AS "급여", (SELECT FLOOR(AVG(SAL)) FROM EMP2) AS "평균 급여"
FROM EMP2
WHERE EMPNO=1000;

사원번호가 1000인 사원의 이름, 급여를 출력하고 사원 전체의 평균 급여 출력 (소수점 버림)

 

 

 

< 연관(Correlated) Subquery >

- Subquery내에서 Main Query내의 칼럼을 사용하는 것

SELECT *
FROM EMP2 a
WHERE a.DEPTNO = (SELECT DEPT FROM DEPT2 b WHERE b.DEPT=a.DEPTNO); 

서브쿼리에서 메인쿼리의 EMP2 테이블 컬럼을 사용해 조인함