[ORACLE] 서브쿼리 221128-01

😀서브쿼리

 - SQL구문안에 포함된 또 다른 SQL 구문
 - 서브쿼리는 알려지지 않은 조건에 근거한 검색을 수행하는 경우 사용
 - 서브쿼리는 메인쿼리의 해당 절이 실행되기 전 한번 실행됨
 - 서브쿼리는 '( )' 안에 기술하며, 연산자와 함께 사용하는 경우 연산자 우측에 위치해야 함                                                (예외: create나 insert는 제 외)
 - 분류
   . 연관성있는 서브쿼리/연관성 없는 서브쿼리 
   . 일반서브쿼리(SELECT 절)/인라인서브쿼리(FROM절)/중첩서브쿼리(WHERE절) 
   . 단일행/다중행, 단일열/다중열 (다중행 서브쿼리가 where절에서 사용시  in ,any, SUM, ALL,..등을 사용

---

1. 연관성없는 서브쿼리(Noncorrelated Subquery)
 - 메인쿼리와 서브쿼리에 사용된 테이블이 조인으로 연결되지 않은 경우

 

   ✔사용예)사원테이블에서 사원들의 평균 급여보다 많은 급여를 받는 사원들을 조회하시오.
                   Alias는 사원번호,사원명,급여,평균급여

                  (평균급여는 서브쿼리 알려지지 않았기 때문 , main쿼리는 제일 마지막에 실행되는 것)

메인쿼리 : 사원테이블에서 사원번호,사원명,급여,평균급여         
  SELECT EMPLOYEE_ID AS 사원번호,
         EMP_NAME AS 사원명,
         SALAY AS 급여,
         (SELECT ROUND(AVG(SALARY))
            FROM HR.EMPLOYEES) AS 평균급여 
    FROM HR.EMPLOYEES
   WHERE SALARY > (평균급여 : 서브쿼리) 
    
서브쿼리 : 평균급여
  SELECT AVG(SALARY)
    FROM HR.EMPLOYEES
    
결합
   SELECT EMPLOYEE_ID AS 사원번호,
         EMP_NAME AS 사원명,
         SALARY AS 급여,
         (SELECT ROUND(AVG(SALARY))
            FROM HR.EMPLOYEES) AS 평균급여 
    FROM HR.EMPLOYEES --실행순서 1.FROM 2. WHERE의 서브쿼리 3. WHERE 좌측 4. SELECT
   WHERE SALARY > ( SELECT AVG(SALARY)
                      FROM HR.EMPLOYEES); -- 서브쿼리는 연산자 우측에 위치

** EXISTS 연산자 사용

 - 연산자의 대상의 결과가 하나의 행이라도 존재하면 참(true)을 반환

   (연관성없는 서브쿼리가 연관성있는 서브쿼리로 바뀜)
  - 단일행 연산자
  - EXITS 우측의 서브쿼리 결과 값은 연산과 무관(보통 SELECT 절에 '1'을 사용)

  SELECT A.EMPLOYEE_ID AS 사원번호,
         A.EMP_NAME AS 사원명,
         A.DEPARTMENT_ID AS 부서코드,
         A.JOB_ID AS 직책코드
    FROM INSU94.EMPLOYEES A
   WHERE EXISTS (SELECT 1 -- 조건이 맞을때 숫자 1을 출력하라
                   FROM INSU94.DEPARTMENTS B
                  WHERE B.PARENT_ID IS NOT NULL
                    AND A.DEPARTMENT_ID = B.DEPARTMENT_ID);

2.연관성 있는 서브쿼리

     ✔사용예) HR계정의 직무변동테이블(JOB_HISTORY)의 부서코드,부서명,책임사원명을
                     조회하시오

메인쿼리 : 부서코드,부서명,책임사원명
  SELECT A.DEPARTMENT_ID AS 부서코드,
         A.DEPARTMENT_NAME AS 부서명,
         B.EMP_NAME AS 책임사원명
    FROM HR.DEPARTMENTS A, HR.EMPLOYEES B
   WHERE A.MANAGER_ID =B.EMPLOYEE_ID
     AND A.DEPARTMENT_ID = (서브쿼리 : 직무변동테이블(JOB_HISTORY)의 부서코드)

서브쿼리 : 직무변동테이블(JOB_HISTORY)의 부서코드
  SELECT DEPARTMENT_ID
    FROM HR.JOB_HISTORY

결합
  SELECT A.DEPARTMENT_ID AS 부서코드,
         A.DEPARTMENT_NAME AS 부서명,
         B.EMP_NAME AS 책임사원명
    FROM HR.DEPARTMENTS A, HR.EMPLOYEES B
   WHERE A.MANAGER_ID =B.EMPLOYEE_ID
     AND A.DEPARTMENT_ID IN(SELECT DEPARTMENT_ID --IN연산자에서 중복을 제거해준다
                              FROM HR.JOB_HISTORY)
   ORDER BY 1;