[Oracle/SQL] 5. 서브쿼리 | 상관쿼리 | 스칼라 서브쿼리 | 인라인뷰

목차

1. 서브쿼리(SUBQUERY)
   1-1. 인라인뷰(INLINE-VIEW)
   1-2. RANK() / DENSE_RANK() 함수
   1-3. WITH
2. 서브쿼리 유형
   2-1. 단일행 서브쿼리
   2-2. 다중행 서브쿼리
   2-3. 다중열 서브쿼리
   2-4. 다중행 다중열 서브쿼리
   2-5. 상관(상호연관) 서브쿼리
   2-6. 스칼라 서브쿼리
3. 서브쿼리 사용 예제

---

학습점검

✅ 서브쿼리의 사용 목적에 대해 이해할 수 있다.
✅ 서브쿼리의 동작 순서에 대해 이해할 수 있다.
✅ 서브쿼리의 사용 가능 위치에 대해 이해할 수 있다.
✅ 서브쿼리의 유형별로 구분을 할 수 있다.
✅ 서브쿼리의 유형별 사용 가능 연산자에 대해 이해할 수 있다.
✅ 상관쿼리에 대해 이해할 수 있다.
✅ 스칼라 서브쿼리에 대해 이해할 수 있다.
✅ 필요한 서브쿼리 유형을 선택하여 원하는 결과를 조회할 수 있다.
✅ 인라인뷰의 사용 목적에 대해 이해할 수 있다.
✅ 인라인뷰를 이용하여 데이터를 조회할 수 있다.
✅ WITH 구문을 이용하여 인라인뷰에 이름을 부여하고 데이터를 조회할 수 있다.
✅ 인라인뷰를 이용한 TOP-N 분석을 활용할 수 있다.

---

 

1. 서브쿼리(SUBQUERY)

쿼리(query)는 질의, 질문이라는 뜻을 가진 영단어이다.

• 서브쿼리는 하나의  SELECT 문장 안에 포함된 또 하나의 SELECT 문장을 일컫는다.

❗ 조회하지 않고는 알 수 없는 조건에 대해 비교해야 하는 상황일 때 서브쿼리가 쓰인다.

• 서브쿼리는 메인쿼리가 실행되기 이전에 단 한 번 실행된다. 즉 서브쿼리 → 메인쿼리 순서로 동작한다.
• 비교연산자의 오른쪽에 기술하며, 이때 반드시 괄호로 묶어서 쓴다.
• 서브쿼리는 1개 이상의 결과를 낼 수 있다. 따라서 서브쿼리와 비교할 항목은 반드시 SELECT한 항목의 개수 및 자료형과 일치해야 한다.

✅ 서브쿼리는 ① SELECT, ② FROM, ③ WHERE, ④ HAVING, ⑤ ORDER BY절에서 사용할 수 있다.

 

---

 

1-1. 인라인뷰(INLINE-VIEW) FROM절에 작성된 서브쿼리

✅ FROM(SELECT......) V

• FROM절에서 테이블 대신 서브쿼리가 쓰인 것을 두고 인라인뷰(INLINE-VIEW)라고 한다.
• 즉 조회된 결과집합(RESULT SET)을 테이블처럼 사용하는 것이다.

-- 급여가 직급별 평균 급여액과 일치하는 사원 조회
SELECT
　　　　E.EMP_NAME
　　　 , J.JOB_NAME
　　　 , E.SALARY
  FROM (SELECT E2.JOB_CODE
　　　　　　　, TRUNC(AVG(E2.SALARY), -5) AS 직급별평균급여
　　　　 FROM EMPLOYEE E2
　　　　GROUP BY E2.JOB_CODE
　　　　) V -- 테이블 별칭 짓듯 작성
  JOIN EMPLOYEE E ON (E.SALARY = V.직급별평균급여 AND E.JOB_CODE = V.JOB_CODE)
  JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
 ORDER BY J.JOB_CODE; 

-- 인사관리부 직원들의 이름, 부서명, 직급이름 조회
SELECT
　　　　V.EMP_NAME
　　　 , V.부서명
　　　 , V.직급이름
　　　 , V.JOB_NAME V.직급이름으로 호출 가능
　　　 , V.SALARY V에 SALARY 컬럼 없음
  FROM (SELECT E.EMP_NAME
　　　　  　　 , D.DEPT_TITLE AS 부서명
　　　         　, J.JOB_NAME AS 직급이름
　　　　  FROM EMPLOYEE E
　　　　  LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID) -- NULL값 포함되도록 LEFT JOIN 사용
　　　　  JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
　　　　) V
 WHERE V.DEPT_TITLE = '인사관리부'; 부적합... V.부서명으로 호출 가능
 WHERE V.부서명 = '인사관리부';

• 서브쿼리 V에서 별칭 지어진 컬럼들은 별칭으로만 호출될 수 있다. '부서명'이라고 명명된 컬럼을 다시금 V.DEPT_TITLE로 호출하면 부적합한 식별자라는 오류 메시지가 발생한다.
• 메인쿼리 SELECT절에서 V.JOB_NAME을 호출한 경우도 마찬가지다. 서브쿼리 V에는 JOB_NAME이라는 컬럼은 없고, '직급이름'이라는 컬럼만 있는 것이다. 따라서 V.직급이름 으로 작성해야 한다.

서브쿼리 V의 Result Set

• 특히 주의할 점은 서브쿼리 V에서 EMPLOYEE 테이블을 사용하고 있다고 해서 V.SALARY처럼 EMPLOYEE의 모든 테이블 컬럼을 쓸 수 있다고 생각하면 안 된다. 메인쿼리에서 사용하고 있는 테이블은 결국 FROM V이기 때문이다. V에 명시한 컬럼만 사용 대상이다.

 

• 인라인뷰는 TOP 5 같은 순위 조회에 많이 쓰인다.

인라인뷰 활용한 TOP-N 분석

ROWNUM

STOPKEY

• ORDER BY 한 결과에 ROWNUM을 붙인다. 여기서 ROWNUM이란 행 번호를 의미하며, 프로그램 내부적으로 존재하는 값이다.
• 번호는 WHERE절에서 붙여지게 된다. 따라서 서브쿼리(인라인뷰) ORDER BY 실행 → 메인쿼리 WHERE절 ROWNUM 사용 순서가 되어야 한다.

ROWNUM 잘못 사용된 예시

• SALARY에 대해서는 DESC 키워드따라 금액이 큰 순부터 알맞게 조회되었으나, ROWNUM은 뒤죽박죽인 모습이다. 여기서 적용된 ROWNUM은 EMPLOYEE 테이블에서 비롯된 것이기 때문이다.
• 이 결과를 인라인뷰 사용으로 바로잡으면 아래와 같다.

-- 급여가 높은 사원 TOP 5 조회
SELECT
　　　　ROWNUM
　　　 , V.EMP_NAME
　　　 , V.SALARY
  FROM (SELFECT E.* 
　　　　   FROM EMPLOYEE E
　　　　  ORDER BY E.SALARY DESC
　　　　) V
 WHERE ROWNUM <= 5;

-- 급여 TOP 6~10 조회 잘못된 예시
SELECT
　　　　ROWNUM
　　　 , V.EMP_NAME
　　　 , V.SALARY
  FROM(SELECT E.*
　　　　FROM EMPLOYEE E
　　　 ORDER BY E.SALARY DESC
　　　 ) V
WHERE ROWNUM BETWEEN 6 AND 10;

• ROWNUM은 1번 행부터 조회하기 때문에 첫 행부터 마지막 행까지 1만 검색하다 끝난다.
• WHERE절 조건이 BETWEEN 1 AND 10;이었다면 1을 포함하고 있으므로 정상실행 될 것이다.
• 1번을 차치하고 TOP 6~10까지 조회하기 위해서는 아래처럼 작성해야 한다.

SELECT
　　　　V2.RNUM
　　　 , V2.EMP_NAME
　　　 , V2.SALARY
  FROM (SELECT ROWNUM AS RNUM -- ROWNUM 별칭 사용
　　　　　　　, V.EMP_NAME
　　　　　　　, V.SALARY
　　　　 FROM (SELECT E.*
　　　　　　　　 FROM EMPLOYEE E
　　　　　　　  ORDER BY E.SALARY DESC
　　　　　　　  ) V
　　　　WHERE ROWNUM < 11 -- STOPKEY 사용
　　　  ) V2
 WHERE RNUM BETWEEN 6 AND 10;

• 인라인뷰를 한 번 더 감싸서 조회하는 것이다.
• 여기서 요점은 ROWNUM을 하나의 컬럼으로 만들어 쓰는 데 있다.

 

• 이때 굳이 전체 행을 다 조회할 필요는 없다. 따라서 범위를 지정해 값을 가져오도록 인라인뷰 V2 WHERE절에 WHERE ROWNUM < 11 과 같이 STOPKEY를 사용한다. 효율적인 SELECT에 대한 연습이자 이후 배우게 될 튜닝 작업의 일환이다.

---

 

1-2. RANK() / DENSE_RANK() 함수 SELECT절에서 사용

✅ RANK() OVER(ORDER BY 컬럼명 정렬방식)
✅ DENSE_RANK() OVER(ORDER BY 컬럼명 정렬방식)

• RANK() 함수는 동순위 발생 시 그 수만큼 건너뛰고 다음 등수를 배정하는 방식이다. 예를 들어 공동 1등이 나왔다면 1등, 1등, 세 번째 사람은 3등이 되는 것이다.
• DENSE_RANK() 함수는 중복된 순위 이후의 등수를 그대로 적용한다. 즉 공동 1등이 나왔다면 1등, 1등, 세 번째 사람은 2등이 되는 것이다.

-- 급여 높은 순 조회
-- RANK() 함수 사용
SELECT
　　　　E.EMP_NAME
　　　 , E.SALARY
　　　 , RANK() OVER(ORDER BY E.SALARY DESC) 순위
  FROM EMPLOYEE E;

-- DENSE_RANK() 함수 사용
SELECT
　　　　E.EMP_NAME
　　　 , E.SALARY
　　　 , DENSE_RANK() OVER(ORDER BY E.SALARY DESC) 순위
  FROM EMPLOYEE E;

 

　　　　RANK() OVER(ORDER BY (E.SALARY + (E.SALARY * NVL(BONUS, 0))) * 12 DESC) 순위
...
WHERE V.순위 < 6;

• 보너스 포함한 연봉이 높은 TOP 5를 구하고자 할 때도 RANK() 함수 사용해 위처럼 쓸 수 있다. 자세한 예제는 하단 페이지에서 확인하자.

 

---

 

1-3. WITH 서브쿼리 명명

✅ WITH 이름 AS (SELECT......)

• WITH 키워드로 서브쿼리에 이름을 붙여 사용할 수 있다.
• 인라인뷰로 사용될 서브쿼리에서 쓰인다. 즉 인라인뷰 사용 시 FROM절에 이 이름을 대신 기술해 쓸 수 있는 것이다.
• 같은 서브쿼리가 여러 번 사용될 때 중복 작성을 피할 수 있어 빠른 실행 속도를 유지한다는 장점이 있다.

WITH
　　　　SUM_SAL -- 사용할 이름
   AS (SELECT SUM(E.SALARY) 앞 -- 구분을 위해 쓴 별칭이다. WITH 키워드 사용 시에 꼭 필요하진 않다.
　　　 FROM EMPLOYEE E
　　  )
　　　, AVG_SAL
   AS (SELECT AVG(E2.SALARY) 뒤
　　　 FROM EMPLOYEE E2
)
SELECT
　　　　S.*
  FROM SUM_SAL S
 UNION
SELECT
　　　　A.*
  FROM AVG_SAL A;

• WITH 키워드로 여러 서브쿼리에 이름 짓고 사용할 수 있다. 두 개 이상의 서브쿼리를 명명하기 위해서는 WITH 이름 AS (), AS ()... 처럼 쉼표(,)로 구분해 나열하면 된다.

(SELECT SUM(E.SALARY)  앞

• 집합연산 UNION을 사용하면 결과적으로 가장 앞에 서술된 컬럼 헤드로 값이 출력된다.

 

---

 

2. 서브쿼리 유형

• 서브쿼리의 유형은 다음과 같다: 1. 단일행 서브쿼리, 2. 다중행 서브쿼리, 3. 다중열 서브쿼리, 4. 다중행 다중열 서브쿼리, 5. 상관(상호연관) 서브쿼리, 6. 스칼라 서브쿼리

 

2-1. 단일행 서브쿼리(SINGLE ROW SUBQUERY) Result Set 1행

• 서브쿼리의 조회 결과값이 1개 행일 때를 말한다.

✅ 단일행 서브쿼리 앞에는 일반 비교 연산자가 쓰인다: >, <, >=, <=, =, !=^=<>
크다작다크거나같다작거나같다같다같지않다

-- 부서코드가 노옹철 사원과 같은 소속 직원 명단 조회 서브쿼리 WHERE절 사용 예시
SELECT
　　　　E.EMP_NAME
  FROM EMPLOYEE E
 WHERE E.DEPT_CODE = (SELECT E2.DEPT_CODE
　　　　　　　　　　　    FROM EMPLOYEE E2
　　　　　　　　　　　  WHERE EMP_NAME = '노옹철'
　　　　　　　　　　　    );

 

-- 전 직원의 평균 급여보다 많은 급여를 받고 있는 직원의 서브쿼리 WHERE절 사용 예시
-- 사번, 이름, 직급코드, 급여를 조회
SELECT
　　　　E.EMP_ID
　　　 , E.EMP_NAME
　　　 , E.JOB_CODE
　　　 , E.SALARY
  FROM EMPLOYEE E
 WHERE E.SALARY >= (SELECT AVG(E2.SALARY)
　　　　　　　　       FROM EMPLOYEE E2
　　　　　　　　      );

 

-- 부서별 급여의 합계 중 가장 큰 부서의 부서명, 급여 합계 조회 서브쿼리 HAVING절 사용 예시
SELECT
　　　　D.DEPT_TITLE
　　　 , SUM(E.SALARY)
  FROM EMPLOYEE E
  LEFT JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID) -- 여기서는 그냥 JOIN도 OK
 GROUP BY D.DEPT_TITLE
HAVING SUM(E.SALARY) = (SELECT MAX(SUM(E2.SALARY))
　　　　　　　　　　　　   FROM EMPLOYEE E2
　　　　　　　　　　　　  GROUP BY E2.DEPT_CODE
　　　　　　　　　　　　 );

 

---

 

2-2. 다중행 서브쿼리(MULTIPLE ROW SUBQUERY) Result Set n행

• 서브쿼리의 조회 결과값 행이 여러 개일 때를 말한다.
• 다중행 서브쿼리 앞에서는 일반 비교 연산자 사용할 수 없다.

✅ 단일행이 아닌 서브쿼리 앞에는 다음과 같은 연산자 사용한다.
-- IN / NOT IN : 여러 개의 결과값 중 1개라도 일치하는 값이 있는지 없는지 이 중에 있는지 없는지
-- > ANY / < ANY : 여러 개의 결과값 중 1개보다 큰지 작은지 하나라도 이기는지
-- > ALL / < ALL : 모든 값보다 큰지 작은지 모든 값을 이기는지
-- EXISTS / NOT EXISTS : 값이 존재하는지 안 하는지 !서브쿼리에만 쓰인다!

 

-- 부서별 최고 급여를 받는 직원의 이름, 직급, 부서, 급여 조회 서브쿼리 WHERE절 사용 예시
SELECT
　　　　E.EMP_NAME
　　　 , E.JOB_CODE
　　　 , E.DEPT_CODE
　　　 , E.SALARY
  FROM EMPLOYEE E
 WHERE E.SALARY  IN (SELECT MAX(E2.SALARY)
　　　　　　　　　　　FROM EMPLOYEE E2
　　　　　　　　　　 GROUP BY E2.DEPT_CODE
　　　　　　　　　　 );

• 서브쿼리에서 GROUP BY 지정하지 않으면 직원 중 최고 급여 받는 1인만 출력된다.

 

-- 관리자에 해당하는 직원에 대한 정보와 관리자가 아닌 직원의 정보를 추출하여 조회 서브쿼리 WHERE절 사용 예시
-- 사번, 이름, 부서명, 직급, '관리자' AS 구분 / '직원' AS 구분
-- SET OPERATOR UNION 사용
SELECT -- 관리자 구분
　　　　E.EMP_ID 사번
　　　 , E.EMP_NAME 이름
　　　 , D.DEPT_TITLE 부서명
　　　 , J.JOB_NAME 직급
　　　 , '관리자' AS 구분
  FROM EMPLOYEE E
  LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
  LEFT JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
 WHERE E.EMP_ID IN (SELECT DISTINCT E2.MANAGER_ID -- 중복값 제외
　　　　　　　　　　  FROM EMPLOYEE E2
　　　　　　　　　　WHERE E2.MANAGER_ID IS NOT NULL -- NULL값 제외
　　　　　　　　　　)
 UNION
SELECT -- 직원 구분
　　　　E.EMP_ID 사번
　　　 , E.EMP_NAME 이름
　　　 , D.DEPT_TITLE 부서명
　　　 , J.JOB_NAME 직급
　　　 , '직원' AS 구분
  FROM EMPLOYEE E
  LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
  LEFT JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
 WHERE E.EMP_ID NOT IN (SELECT DISTINCT E2.MANAGER_ID
　　　　　　　　　　　　   FROM EMPLOYEE E2
　　　　　　　　　　　　  WHERE E2.MANAGER_ID IS NOT NULL
　　　　　　　　　　　　 );

-- 위 문제 동일 서브쿼리 SELECT절 사용 예시
SELECT
　　　　E.EMP_ID 사번
　　　 , E.EMP_NAME 이름
　　　 , D.DEPT_TITLE 부서명
　　　 , CASE
　　　    WHEN E.EMP_ID IN (SELECT DISTINCT E2.MANAGER_ID
　　　　　　　　　　　　　　 FROM EMPLOYEE E2
　　　　　　　　　　　　     WHERE E2.MANAGER_ID IS NOT NULL
　　　　　　　　　　　　     )
　　　　 THEN '관리자'
　　　　 ELSE '직원'
　　　　END AS 구분
  FROM EMPLOYEE E
  LEFT JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID); 

• LEFT JOIN으로 써야 한다. 일반 JOIN으로 쓴다면 DEPT_CODE가 NULL인 값들이 결과서 제외되기 때문이다.

 

---

 

2-3. 다중열 서브쿼리 Result Set n열

• 서브쿼리의 조회 결과값 컬럼이 여러 개일 때를 말한다.

WHERE AND 조건으로 나열한 예시

• 사진에서처럼 다중열 서브쿼리 대신 WHERE AND 조건을 연달아 작성해 값을 구할 수는 있다.
• 같은 문제에 다중열 서브쿼리 적용한 예시는 다음과 같다.

-- 퇴사한 여자 직원과 같은 부서, 같은 직급에 해당하는
-- 사원의 이름, 직급, 부서, 입사일을 조회 서브쿼리 WHERE절 사용 예시
SELECT
　　　　E.EMP_NAME
　　　 , E.JOB_CODE
　　　 , E.DEPT_CODE
　　　 , E.HIRE_DATE
  FROM EMPLOYEE E
 WHERE (E.DEPT_CODE, E.JOB_CODE) = (SELECT E2.DEPT_CODE -- 같은 부서, 같은 직급
　　　　　　　　　　　　　　　　　　　　　 , E2.JOB_CODE
　　　　　　　　　　　　　　　　　　   FROM EMPLOYEE E2
　　　　　　　　　　　　　　　　　　 WHERE SUBSTR(E2.EMP_NO, 8, 1) = 2
　　　　　　　　　　　　　　　　　　    AND E2.ENT_YN = 'Y'
　　　　　　　　　　　　　　　　　　)
   AND E.EMP_ID != (SELECT E3.EMP_ID -- 본인 제외
　　　　　　　　      FROM EMPLOYEE E3
　　　　　　　　    WHERE SUBSTR(E3.EMP_NO, 8, 1) = 2
　　　　　　　　　　  AND E3.ENT_YN = 'Y';

• 다중열 서브쿼리 작성을 위해서는 소괄호 () 안에 조건을 나열한다: (E.DEPT_CODE, E.JOB_CODE)
• 이때 왼편, 오른편에 서술된 비교군 순서가 반드시 같아야 한다: ... E2.DEPT_CODE, E2.JOB_CODE ...

 

---

 

2-4. 다중행 다중열 서브쿼리 Result Set n행 n열

• 조회 결과 행 수와 열 수 모두 여러 개일 때를 말한다.

-- 직급별 최소 급여를 받는 직원의 사번, 이름, 직급, 급여 조회
SELECT
　　　　E.EMP_ID
　　　 , E.EMP_NAME
　　　 , E.JOB_CODE
　　　 , E.SALARY
  FROM EMPLOYEE E
 WHERE (E.JOB_CODE, E.SALARY) IN (SELECT E2.JOB_CODE
　　　　　　　　　　　　  　　　　　　　 , MIN(E2.SALARY)
　　　　　　　　　　　　　　　　　 FROM EMPLOYEE E2
　　　　　　　　　　　　　　　　  GROUP BY E2.JOB_CODE
　　　　　　　　　　　　　　　　  )
 ORDER BY 3;

 

---

 

2-5. 상관(상호연관) 서브쿼리

• 일반적으로는 서브쿼리가 만든 결과값을 → 메인쿼리가 비교 연산 진행한다.
• 상관 서브쿼리는 메인쿼리가 사용하는 테이블의 값을 토대로 → 서브쿼리가 결과를 만든다.
• 여기서 메인쿼리의 테이블 값이 변경되면, 서브쿼리의 결과값도 바뀌게 되는 것이다.

-- 관리자 사번이 EMPLOYEE 테이블에 존재하는 직원에 대한
-- 사번, 이름, 부서코드, 관리자 사번 조회
SELECT
　　　　E.EMP_ID
　　　 , E.EMP_NAME
　　　 , E.DEPT_CODE
　　　 , E.MANAGER_ID
  FROM EMPLOYEE E
 WHERE EXISTS (SELECT E2.EMP_ID
　　　　　　　　 FROM EMPLOYEE E2
　　　　　　　  WHERE E.MANAGER_ID = E2.EMP_ID -- 서브쿼리만 실행 시 오류
　　　　　　　  );

• 이때 서브쿼리 혼자서는 부적합한 식별자로 오류 발생해 동작하지 않는다. 메인쿼리의 E.MANAGER_ID를 사용하고 있기 때문이다.
• 그러나 전체에 대해 조회하면 정상 실행된다. 결과적으로 MANAGER_ID가 NULL이 아닌 사람이 조회되는 것이다.

 

---

 

2-6. 스칼라 서브쿼리

• 단일행 서브쿼리 + 상관쿼리 조합을 스칼라 서브쿼리라고 한다.
• SELECT절, WHERE절, ORDER BY절에서의 사용 예시는 다음과 같다.

-- SELECT절에서 스칼라 서브쿼리 이용
-- 모든 사원의 사번, 이름, 관리자사번, 관리자명을 조회
SELECT
　　　　E.EMP_ID 사번
　　　 , E.EMP_NAME 이름
　　　 , E.MANAGER_ID 관리자사번
　　　 , NVL((SELECT E2.EMP_NAME
　　　　　　  FROM EMPLOYEE E2
　　　　　　WHERE E.MANAGER_ID = E2.EMP_ID
　　　　), '없음') 관리자명
  FROM EMPLOYEE E
 ORDER BY 1;

• SELECT절에서의 서브쿼리는 무조건 결과값이 1행이어야 한다.

 

-- WHERE절에서 스칼라 서브쿼리 이용
-- 자신의 직급별 평균 급여보다 높은 급여를 받는 사원의 이름, 직급코드, 급여 조회
SELECT
　　　　E.EMP_NAME
　　　 , E.JOB_CODE
　　　 , E.SALARY
  FROM EMPLOYEE E
 WHERE E.SALARY > (SELECT TRUNC(AVG(E2.SALARY), -5)
　　　　　　　　　　  FROM EMPLOYEE E2
　　　　　　　　　   WHERE E.JOB_CODE = E2.JOB_CODE
　　　　　　　　　   );

• 이때 서브쿼리는 E.JOB_CODE라는 값을 메인쿼리로부터 받아와 사용하고 있기 때문에 혼자서는 정상 동작할 수 없다.

 

-- ORDER BY절에서 스칼라 서브쿼리 이용
-- 모든 직원의 사번, 이름, 소속부서 조회
-- 단, 부서명 내림차순 정렬
SELECT
　　　　E.EMP_ID
　　　 , E.EMP_NAME
　　　 , E.DEPT_CODE
  FROM EMPLOYEE E
 ORDER BY (SELECT D.DEPT_TITLE
　　　　　　 FROM DEPARTMENT D
　　　　　  WHERE E.DEPT_CODE = D.DEPT_ID
　　　　　  ) DESC NULLS LAST;

 

---

 

3. 서브쿼리 사용 예제

▶단일행 서브쿼리 예시이다. WHERE절에 쓰인 비교연산자를 보면 구분 가능하다.

-- 노옹철 사원의 급여보다 많이 받는 직원의
-- 사번, 이름, 부서, 직급, 급여를 조회 ✅
SELECT
　　　　E.EMP_ID 사번
　　　 , E.EMP_NAME 이름
　　　 , E.DEPT_CODE 부서
　　　 , E.JOB_CODE 직급
　　　 , E.SALARY 급여
FROM EMPLOYEE E
WHERE E.SALARY > (SELECT E2.SALARY
　　　　　　　　　   FROM EMPLOYEE E2
　　　　　　　　　 WHERE E2.EMP_NAME = '노옹철'
　　　　　　　　　 );

▶단일행 서브쿼리

-- 가장 적은 급여를 받는 직원의
-- 사번, 이름, 직급, 부서, 급여, 입사일 조회 ✅
SELECT
　　　　E.EMP_ID
　　　 , E.EMP_NAME
　　　 , E.JOB_CODE
　　　 , E.DEPT_CODE
　　　 , E.SALARY
　　　 , E.HIRE_DATE
  FROM EMPLOYEE E
 WHERE E.SALARY = (SELECT MIN(E2.SALARY)
　　　　　　　　　　 FROM EMPLOYEE E2
　　　　　　　　　  );

 

▶다중행 서브쿼리

최소 급여라기에 MIN(E2.SALARY)를 말하는 줄 알았다. 그냥 과장 월급 중 하나라도 이기는지 조회하면 된다.

또, J2.JOB_NAME이라는 조건을 사용하므로 JOIN을 추가해야 한다.

-- 대리 직급의 직원들 중에서 과장 직급의 최소 급여보다 많이 받는
-- 직원의 사번, 이름, 직급명, 급여 조회 ✅✅
SELECT
　　　　E.EMP_ID
　　　 , E.EMP_NAME
　　　 , J.JOB_NAME
　　　 , E.SALARY
  FROM EMPLOYEE E
  JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
 WHERE J.JOB_NAME = '대리'
    AND E.SALARY > ANY (SELECT E2.SALARY
　　　　　　　　　　       FROM EMPLOYEE E2
　　　　　　　　　　　　   JOIN JOB J2 ON (E.JOB_CODE = J2.JOB_CODE)
　　　　　　　　　　      WHERE J2.JOB_NAME = '과장'
　　　　　　　　　　      );

▶다중행 서브쿼리

-- 차장 직급 급여의 가장 큰 값보다 많이 받는 과장 직급의
-- 사번, 이름, 직급, 급여를 조회 ✅
-- 단, > ALL 혹은 < ALL 연산자 사용
SELECT
　　　　E.EMP_ID
　　　 , E.EMP_NAME
　　　 , J.JOB_NAME
　　　 , E.SALARY
  FROM EMPLOYEE E
  JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
 WHERE J.JOB_NAME = '과장'
    AND E.SALARY > ALL (SELECT E2.SALARY
　　　　　　　　　　　　 FROM EMPLOYEE E2
　　　　　　　　　　　　  JOIN JOB J2 ON (E2.JOB_CODE = J2.JOB_CODE)
　　　　　　　　　　　  WHERE J2.JOB_CODE = '차장'
　　　　　　　　　　　　); 

 

▶다중행 다중열 서브쿼리

사진상에서는 E.SALARY만을 조건으로 내걸었기 때문에 다른 직급의 급여를 만족하는 경우까지 출력되고 있다. 이때 다중열 적용해 (조건1, 조건2) 작성해 바로잡을 수 있다. IN 연산자 앞뒤로 위치한 비교군 순서에 주의해 쓰도록 하자.

잘못된 예시

-- 자기 직급의 평균 급여를 받고 있는 직원의
-- 사번, 이름, 직급코드, 급여를 조회 ✅✅
-- 단, 급여와 급여 평균은 십만원 단위로 계산하세요. TRUNC(컬럼명, -5)
SELECT
　　　　E.EMP_ID
　　　 , E.EMP_NAME
　　　 , E.JOB_CODE
　　　 , E.SALARY
  FROM EMPLOYEE E
 WHERE (E.JOB_CODE, E.SALARY) IN (SELECT E2.JOB_CODE -- 비교연산자 IN 사용
　　　　　　　　　　　　　　　　　　　    , TRUNC(AVG(E2.SALARY), -5)
　　　　　　　　　　　　　　　　    FROM EMPLOYEE E2
　　　　　　　　　　　　　　　　  GROUP BY E2.JOB_CODE -- 직급별
　　　　　　　　　　　　　　　　  );

 

-- 급여 평균 3위 안에 드는 부서의
-- 부서코드와 부서명, 평균 급여 조회 ✅✅✅
SELECT
　　　　V.DEPT_CODE
　　　 , V.DEPT_TITLE
　　　 , V.평균급여 -- 인라인뷰에서 별칭 적용해 가져온다.
  FROM (SELECT ROWNUM
　　　　　　　   E.DEPT_CODE
　　　　　　　 , D.DEPT_TITLE
　　　　　　　 , AVG(E.SALARY) 평균급여
　　　　 FROM EMPLOYEE E
　　　　   JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
　　　　GROUP BY E.DEPT_CODE, D.DEPT_TITLE -- 둘 다 그룹으로 묶어야 한다.
　　　　ORDER BY AVG(E.SALARY) DESC -- 급여 높은 순으로 정렬 필요하다.
　　　　) V
 WHERE ROWNUM <= 3;

• 여기서 ROWNUM은 인라인뷰 서브쿼리 V ORDER BY절에서 넘버링된다. 따라서 SELECT절에 ROWNUM 따로 작성할 필요 없다. 메인쿼리 WHERE절에서 호출된 ROWNUM이 바로 이것이다.

 

-- 급여 높은 순 TOP 5 조회 ✅✅
-- 인라인뷰, RANK() 함수 사용
SELECT
　　　　V.*
  FROM (SELECT E.EMP_NAME
　　　　　　　, E.SALARY
　　　　　　　, RANK() OVER(ORDER BY E.SALARY DESC) 순위
　　　　 FROM EMPLOYEE E
　　　  ) V
 WHERE V.순위 <= 5;

 

-- 직원 테이블에서 보너스를 포함한 연봉이 높은 5명의
-- 사번, 이름, 부서명, 직급명, 입사일을 조회 ✅✅✅
SELECT
　　　　V.EMP_ID
　　　 , V.EMP_NAME
　　　 , V.DEPT_TITLE
　　　 , V.JOB_NAME
　　　 , V.HIRE_DATE
  FROM (SELECT E.EMP_ID
　　　　　　　, E.EMP_NAME
　　　　　　   , D.DEPT_TITLE
　　　　　　   , J.JOB_NAME
　　　　　　　, E.HIRE_DATE
　　　　　　　, E.SALARY
　　　　　　　, (E.SALARY + (E.SALARY * NVL(E.BONUS, 0))) * 12 연봉
　　　　　　　, RANK() OVER(ORDER BY (E.SALARY + (E.SALARY * NVL(E.BONUS, 0))) * 12 DESC) 순위
　　　　 FROM EMPLOYEE E
　　　　 LEFT JOIN DEPARTMENT ON (E.DEPT_CODE = D.DEPT_ID) -- JOIN으로 써도 무방하다
　　　　 LEFT JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE) -- JOIN으로 써도 무방하다
　　　　 ORDER BY (E.SALARY + (E.SALARY * NVL(BONUS, 0))) * 12 DESC 오류
　　　　 ) V
WHERE V.연봉 <= 5;

 

-- 부서별 급여 합계가 전체 급여 총합의 20%보다 많은
-- 부서의 부서명과 부서별 급여 합계 조회 ✅✅
-- 서브쿼리 HAVING절 사용
SELECT
　　　　D.DEPT_TITLE
　　　 , SUM(E.SALARY)
  FROM EMPLOYEE E
  LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
 GROUP BY D.DEPT_TITLE
HAVING SUM(E.SALARY) > (SELECT SUM(E2.SALARY) * 0.2
　　　　　　　　　　　　    FROM EMPLOYEE E2
　　　　　　　　　　　　  );
-- WITH 사용
  WITH
　　　　TOTAL_SAL
     AS (SELECT D.DEPT_TITLE DTITLE
　　　　　　  , SUM(E.SALARY) SSAL
　　　　FROM EMPLOYEE E
　　　　LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
　　　　GROUP BY D.DEPT_TITLE
　　　　)
SELECT
　　　　DTITLE
　　　 , SSAL
  FROM TOTAL_SAL
 WHERE SSAL > (SELECT SUM(E.SALARY) * 0.2
　　　　　　　　  FROM EMPLOYEE 
);