[SQLD 요약] II. SQL 기본 및 활용 - 02. SQL 활용 (2) - Connect by, Subquery (서브쿼리)

 

 

SQLD 요약 / SQL Developer 요약

🔑 계층형조회/ Connect by/ START WITH/ CONNECT BY PRIOR/ 메인쿼리/ 서브쿼리/ 인라인뷰/ 스칼라 서브쿼리/

       단일행서브쿼리/ 다중행서브쿼리/ 다중행 비교연산자/ IN/ ANY/ ALL/ EXISTS/ 연관 서브쿼리

 

II. SQL 기본 및 활용

   02. SQL 활용

          [1] 조인 (JOIN)

          [2] 계층형 조회 (Connect by)

          [3] 서브쿼리 (Subquery)

 

 

---

---

 

02. SQL 활용

 

[2] 계층형 조회 | 계층형으로 데이터 조회

(1) Connect by | 트리 형태의 구조로 질의를 수행

• START WITH, CONNECT BY PRIOR

# 예시: 사원들 간 관리체계를 계층형 구조로 조회
SELECT LEVEL, EMPNO, MGR, ENAME
  FROM Company.EMP
  START WITH MGR IS NULL		# MGR 칼럼 값이 NULL인 행이 최상위 ROOT가 됨
  CONNECT BY PRIOR EMPNO = MGR;	# 상위 노드의 사원 = 하위 노드의 관리자

계층형 조회: Connect by	CONNECT BY 키워드	키워드 설명
- 트리 형태의 구조를 위에서 아래로 탐색하면서 조회	LEVEL	검색 항목의 깊이
- 역방향 조회도 가능	CONNECT_BY_ROOT	계층구조에서 최상위 값
- START WITH: 시작 조건	CONNECT_BY_ISLEAF	계층구조에서 최하위 값
- CONNECT BY PRIOR: 조인 조건	SYS_CONNECT_BY_PATH	계층구조의 전체 전개 경로
- MAX(LEVEL): 최대 계층 수 = Leaf Node의 계층값	NOCYCLE	순환구조가 발생시점까지만 전개
- LPAD: 계층형 조회 결과 = 트리 형태를 명확히 볼 수 있음	CONNECT_BY_ISCYCLE	순환구조의 발생시점을 표시

 

 

• SELECT 행에서 LPAD함수를 사용하여 → 트리 형태 조회 결과를 명확히 볼 수 있음
  
  
• SELECT LEVEL, LPAD(' ', 4 * (LEVEL-1)) || EMPNO, MGR, CONNECT_BY_ISLEAF
  - LEVEL = 1 : 최상위 노드 ROOT에 해당함 → LPAD(' ', 0) → 변화 없음
  - LEVEL = 2 → 4*(LEVEL-1) = 4 → LPACE(' ', 4) → EMPNO 왼쪽에 공백 4칸을 두고 화면에 출력됨

 

---

 

[3] 서브쿼리 (Subquery)

(1) Main query & Subquery

• 서브쿼리: SELECT문 내에, 다시 SELECT문을 사용하는 SQL문
• 메인쿼리: 서브쿼리 밖에 있는 SELECT문

인라인 뷰 (Inline view)	스칼라 서브쿼리 (Scala Subquery)	서브쿼리 (Subquery)
- FROM구에 SELECT문 사용 - 가상의 테이블을 만드는 효과	- SELECT문에 SELECT문 사용	- WHERE구에 SELECT문 사용 - 괄호 내에 SELECT문을 사용

# 서브쿼리 Subquery : WHERE구에 있는 SELECT문
SELECT * FROM EMP
  WHERE DEPTNO = 
        (SELECT DEPTNO FROM DEPT 
        WHERE DEPTNO = 10);
    
    
# 인라인 뷰 Inline View : FROM구에 있는 SELECT문
SELECT * 
  FROM (SELECT ROWNUM NUM, ENAME 
        FROM EMP) a
  WHERE NUM < 5;

 

 

---

 

 

(2) 단일 행 & 다중 행 서브쿼리

• 서브쿼리 종류: 반환하는 행 개수에 따라서 → 단일 행 서브쿼리 & 다중 행 서브쿼리

단일 행 서브쿼리	다중 행 서브쿼리
서브쿼리 실행결과로 반드시 1개 행만 조회됨	서브쿼리 실행결과로 여러 개의 행이 조회됨
사용하는 연산자: 비교연산자 =, <, <=, >, =<, <>	사용하는 연산자: 다중 행 비교 연산자 IN, ANY, ALL, EXISTS

 

 

• 다중 행 비교연산자: IN, ANY, ALL, EXISTS

IN (Subquery)	ALL (Subquery)	ANY (Subquery)	EXISTS (Subquery)
메인쿼리의 비교조건이 서브쿼리의 결과 중에서 하나만 동일하면 TRUE	메인쿼리의 비교조건이 서브쿼리의 결과 중에서 하나 이상 동일하면 TRUE	메인쿼리와 서브쿼리의 결과가 모두 동일하면 TRUE	메인쿼리와 서브쿼리의 결과가 하나라도 존재하면 TRUE
- OR 조건과 동일함	- < ALL : 최솟값 반환 - > ALL : 최댓값 반환	- < ANY : 하나라도 크게되면 참 - > ANY : 하나라도 작게되면 참	- 서브쿼리로 어떤 데이터의   존재여부를 확인함 - 결과로 참 / 거짓을 반환

 

 

• IN (Subquery): 여러 행 중에서 하나만 참이 되어도 참이 되는 연산

# SAL가 2000보다 큰 사원번호 조회한 후 >> EMP.EMPNO를 조회
SELECT ENAME, DNAME, SAL
  FROM EMP, DEPT
  WHERE EMP.DEPTNO = DEPT.DEPTNO
    AND EMP.EMPNO
    IN (SELECT EMPNO FROM EMP
    	WHERE SAL > 2000);

 

 

• ANY (Subquery): 동일한/만족하는 결과가 하나라도 있으면 참이 되는 연산
• ALL (Subquery): 모든 결과가 동일/만족해야 참이 되는 연산

ANY 예시		ALL 예시
10 > ANY (5, 10, 20)	TRUE	10 > ALL (5, 10, 20)	FALSE
10 = ANY (5, 10, 20)	TRUE	10 = ALL (5, 10, 20)	FALSE
20 > ANY (5, 10, 20)	FALSE	30 > ALL (5, 10, 20)	TRUE

 

 

• EXISTS (Subquery): 서브쿼리 결과가 하나라도 존재하면 TRUE, 전혀 없으면 FALSE
  
  - IN : 서브쿼리 결과를 모두 수행함
  - EXISTS : 일치하는 결과가 나오면, 더 이상 쿼리를 수행하지 않음

# 예시: 직원 중에서 SAL > 2000이 있으면 TRUE, 없으면 FALSE를 반환함
SELECT ENAME, DNAME, SAL
  FROM EMP, DEPT
  WHERE EMP.DEPTNO = DEPT.DEPTNO
  AND EXISTS (SELECT 1		# 칼럼이 불필요하므로, 의미없는 1 을 사용
              FROM EMP
              WHERE SAL > 2000);

 

---

 

 

(3) 스칼라 서브쿼리 (Scala Subquery) | SELECT문에서 SELECT문 사용

• 반드시 1개 행, 1개 칼럼만 반환하는 서브쿼리
• 여러 행이 반환되면 오류발생

# 직원 평균 급여를 계산하여 조회
SELECT ENAME AS "이름", 
       SAL AS "급여",
       (SELECT AVG(SAL) FROM EMP) AS "평균급여"
  FROM EMP
  WHERE EMPNO = 1000;

 

 

(4) 연관 서브쿼리 (Correlated Subquery)

• 서브쿼리 내에서, 메인쿼리 내의 칼럼을 사용함
• 메인쿼리 값을 서브쿼리에 주고 → 서브쿼리 실행 → 서브쿼리 실행결과를 다시 메인쿼리로 반환 → 메인쿼리 실행

# E1.DEPTNO: 메인쿼리에서 부서번호를 서브쿼리로 전달하고
# AVG(SAL): 받은 부서번호를 사용해서 평균급여 계산하고
# WHERE SAL > ... : 메인쿼리에서 서브쿼리의 평균급여보다 급여가 높은 직원 출력
SELECT ENAME, DEPTNO, SAL
  FROM EMP E1
  WHERE SAL > (SELECT AVG(SAL)
               FROM EMP E2
               WHERE E2.DEPTNO = E1.DEPTNO)
  ORDER BY DEPTNO;

 

 

---

 

참고도서: SQL 개발자 이론서+기출문제_이기적, 2020
참고링크 1: gent.tistory.com/287

참고링크 2: ttend.tistory.com/624