[SQL] SubQuery

👀 유용한 정보

세션 정보 확인

📘 SubQuery

Scalar Subquery

CORRELATED SUBQUERY (상관서브쿼리) - 매우 비효율적

In-Line View (FROM 절에 사용된 SUBQUERY)

TOP-N, BOTTOM-M (서브쿼리, ROWNUM을 활용)

📘 DML 연산과 Subquery

---

👀 유용한 정보

세션 정보 확인

SHOW USER; -- system 계정으로 로그인
SELECT * FROM V$SESSION;
SELECT * FROM V$SESSION WHERE USERNAME = 'SYSTEM';

---

📘 SubQuery

• SubQuery : Main Query (SELECT, INSERT, DELETE, UPDATE, CREATE) 안에 포함되는 SELECT

💡

서브 쿼리가 먼저 실행된다. (Nested 구조에서 안쪽이 먼저 실행되는 것으로 이해!) SMITH가 일하는 부서의 직원 이름, 직업 출력 된다.

• Subquery의 종류

💡

Scalar Subquery (SELECT) : 컬럼처럼 사용되어 1개의 행, 열 값을 리턴하여 컬럼처럼 사용되는 서브쿼리 Inline View (FROM) : 한 줄의 뷰, FROM 절에 서브쿼리를 통해 테이블 대신 사용 가능 Nested Subquery (WHERE, HAVING) : WHERE, HAVING 절에 사용되는 서브쿼리 cf)

• Query의 2가지 의미 ① SQL ② SELECT

• Scalar : 단일값

• 실행 순서

💡

일반적인 서브 쿼리 실행 순서

SUBQUERY 실행 (결과 집합(데이터)이 만들어 진다.) → MAIN QUERY실행 예외) Correlated Subquery (상관서브쿼리)

Correlated Subquery 서브쿼리가 메인쿼리의 컬럼을 참조 한다. (서브쿼리가 메인쿼리의 결과 데이터를 참조) 메인 쿼리가 먼저 실행되고 서브쿼리에서 필터링 하는 목적으로 사용

서브 쿼리 사용의 성능 향상

• 서브 쿼리를 사용함으로써, 중간 결과 데이터가 DBMS 내에서 처리하게 되어 성능 향상이 된다.

• 서브 쿼리를 사용하지 않으면, 여러 번의 요청에 따른 처리 결과의 응답 과정이 추가된다. 또한 중간 결과 처리 과정을 클라이언트가 담당하는 단점이 있다.

💡

SINGLE COLUMN,SINGLE ROW ①

SELECT ENAME,JOB FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = 'SMITH' );

→ SMITH 의 부서에 일하는 직원 이름, 직업 출력

②

SELECT ENAME,SAL FROM EMP WHERE SAL < ( SELECT AVG(SAL) FROM EMP);

→ 직원 급여 평균보다 적은 급여를 받는 직원 이름과 급여 출력

SINGLE COLUMN, MULTIPLE ROW RETURN SUBQUERY ①

SELECT ENAME,JOB FROM EMP WHERE DEPTNO = 10,30; --? 에러

→ 에러 발생 : = 연산자 오른쪽 R-VALUE는 하나만 와야 한다.

② 복수개 값 처리 위해선 IN, ANY, ALL 리스트 연산자 사용해야 된다.

-- Multiple Rows -- IN,ANY,ALL
SELECT ENAME,JOB FROM EMP WHERE DEPTNO IN (10,30); 

→ 10번, 20번 부서의 직원 이름과 직업

③

SELECT DNAME,LOC FROM DEPT -- 3인 이상 근무 부서 정보조회 ??
WHERE DEPTNO = (SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING COUNT(*) > 3 );

• (SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING COUNT(*) > 3 )

  

  → 서브 쿼리 결과 복수 개의 행이 출력 되었다.

• WHERE 절 뒤 = 비교 연산자에 R-value에는 단일 값만 올 수 있다. 따라서 List 연산자를 사용하여 해당 연산을 할 수 있다.

MULTIPLE COLUMN, MULTIPLE ROW RETURN ①

SELECT DEPTNO,JOB,ENAME,SAL FROM EMP
WHERE (DEPTNO,JOB) IN (SELECT DEPTNO,JOB FROM EMP
											 GROUP BY DEPTNO,JOB HAVING AVG(SAL) > 2000);

Scalar Subquery

💡

[장점] 편리성 [질문] 반복되는 실행을 하는가? 실행횟수 * 입/출력값 , Query Execution Cache , hashing ①

SELECT DEPTNO,ENAME,JOB,SAL,(
			SELECT ROUND(AVG(SAL),0)
			FROM EMP S WHERE S.JOB=M.JOB) AS JOB_AVG_SAL
FROM EMP M
ORDER BY JOB; -- 실행계획(?) outer-join ➔ 결과가 없으면 NULL 리턴

• SELECT LIST 부분에 서브 쿼리 존재 : Scalar Subquery

• 서브 쿼리 내부에, 메인 쿼리의 JOB 의 컬럼 데이터를 참조하고 있다.

• 따라서, 메인 쿼리가 먼저 실행되고 서브 쿼리가 실행된다 → Correlated Subquey

• 메인 쿼리를 참조해야 되므로, 매번 한 행의 레코드를 처리할 때마다 해당 서브 쿼리가 계속적으로 실행된다. 이는 매우 비효율적이다.

CORRELATED SUBQUERY (상관서브쿼리) - 매우 비효율적

💡

[주의]

• Subquery는 Mainquery의 컬럼을 참조할수 있지만 Mainquery는 Subquery의 컬럼을 참조할수 없다.

[질문]

• Mainquery에서 Subquery의 컬럼을 참조 하려면 → ① Join 으로 변환 ② Scalar Subquery

①

SELECT DEPTNO,ENAME,JOB,SAL FROM EMP M
WHERE SAL > ( SELECT AVG(SAL) AS AVG_SAL FROM EMP WHERE JOB = M.JOB );

• 서브 쿼리에서, 메인 쿼리 문의 M.JOB을 참조하고 있다.

  마찬가지로, 매 레코드를 처리할 때마다 해당 서브쿼리를 매번 실행해야 하므로, 비효율적이다.

In-Line View (FROM 절에 사용된 SUBQUERY)

💡

[설명]

SQL이 실행되는 시점에 동적으로 생성되는 View의 역할을 한다고 해서 Dynamic View 라고도 한다.

일반적으로 Subquery의 컬럼을 Mainquery에서 사용할수 없지만 Inline View에서 Subquery의 컬럼을 Mainquery 에서 사용이 가능하다.

①

SELECT DEPTNO, ENAME,EMP.JOB,SAL,IV.AVG_SAL
FROM EMP, (SELECT JOB,ROUND(AVG(SAL)) AS AVG_SAL FROM EMP GROUP BY JOB) IV
WHERE EMP.JOB = IV.JOB AND SAL > IV.AVG_SAL
ORDER BY DEPTNO ,SAL DESC;

• FROM 절에 사용된 서브 쿼리 확인

TOP-N, BOTTOM-M (서브쿼리, ROWNUM을 활용)

💡

①

SELECT *
FROM (SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL ASC) BM
WHERE ROWNUM <= 5;

• In-Line View 서브 쿼리를 통해 동적으로 원하는 테이블 구조를 만들 수 있다.

• 서브 쿼리 내에서 데이터 정렬 후 ROWNUM을 통해 일부 데이터를 가져올 수 있다!

→ SAL 기준 하위 5개의 레코드 출력

②

SELECT TN.EMPNO,TN.ENAME,TN.SAL
FROM (SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC) TN
WHERE ROWNUM < 5;

→ SAL 기준 상위 5개의 레코드 출력

📘 DML 연산과 Subquery

💡

① // SUBQUERY로 한번에 N개 Rows INSERT

INSERT INTO BONUS(ENAME,JOB,SAL,COMM) SELECT ENAME,JOB,SAL,COMM FROM EMP;
SELECT * FROM BONUS; // 결과 확인
ROLLBACK; // 다음번 실습을 위해서
SELECT * FROM BONUS; // 결과 확인

② // 부서별 성과별 보너스 계산후(데이터 연산) N개 Rows INSERT + 데이터 연산(가공)

INSERT INTO BONUS(ENAME,JOB,SAL,COMM)
SELECT ENAME,JOB,SAL,DECODE(DEPTNO,10,SAL*0.3,20,SAL*0.2)+NVL(COMM,0)
FROM EMP WHERE DEPTNO IN (10,20);
SELECT * FROM BONUS;
COMMIT;

→ 데이터 가공(보너스 계산), 계산 Insert까지 한번에 가능 → 프로그래밍 로직 개발 처리를 쿼리로 처리 가능하다.

③ // 평상시 COMM을 받지 못하는 사원들에게 평균 COMM 금액의 50%를 보너스로 지급

UPDATE EMP SET COMM = (SELECT AVG(COMM)/2 FROM EMP) -- 전체 직원 커미션의 50%
WHERE COMM IS NULL OR COMM = 0;
COMMIT;

④ // 평균 이상의 급여를 받는 사원들은 보너스 지급 대상자에서 제외

DELETE FROM BONUS WHERE SAL > (SELECT AVG(SAL) FROM EMP) ;
COMMIT;

---

Uploaded by N2T