Oracle(2022.02.23)2-서브쿼리

SUBQUERY
  - SQL 구문 안에 기술된 또 다른 SQL문
  - 서브쿼리는 '( )'안에 기술해야함(단, INSERT INTO 문에 사용되는 SUBQUERY는 예외, VALUES 절사용x

 => INSERT INTO SUBQUERY)
  - 연산자와 사용될 경우 연산자 오른쪽에 기술 해야함. //WHERE절에서(?)
  - 알려지지 않은 조건에 근거한 검색명령이나, JOIN의 감소 등의 목적으로 사용
  --알려지지 않은 조건에 근거한 검색명령 ex)평균 급여보다 더 많은 급여를 받는 직원
  --SUBQUERY와 JOIN 중 누가 더 효율적..? 상황에 따라 다르다, 다만, JOIN이 많으면 복잡해지긴 함
  - 서브쿼리를 포함하고 있는 쿼리를 메인쿼리라 하고 메인쿼리 수행 전에 서브쿼리부터 한번 수행됨
  --메인쿼리는 최종적으로 반환되어지는 컬럼들을 포함하고 있는것
  --서브쿼리는 최종 결과를 반환하기 위해서 사용되는 중간 계산 자료들
  - 서브쿼리의 분류(의미가 없다, 자격증 시험에 나옴)
    . 연관성 없는 서브쿼리 vs 연관성 있는 서브쿼리 (기준은 메인쿼리에 사용된 테이블과 서브쿼리에 사용된 테이블            의 JOIN 연결 유무)
    . 일반서브쿼리(SELECT 절에 위치), inline view subquery(FROM 절에 위치), nested subquery(WHERE 절에 위치)
    . 단일행 서브쿼리, 다중행 서브쿼리
    --단일행 서브쿼리 : WHERE 컬럼명 연산자(=,!=,>< 등)(SUBQUERY)에서 
    --다중행 서브쿼리 : IN, ANY, SOME

사용예) 사원테이블에서 사원들의 평균 급여보다 더 많은 급여를 받는 사원정보 조회
          Alias는 사원번호,사원명,부서번호,급여 -->중첩서브쿼리 ,관련성 없는 서브쿼리(조인이 없으므로),연산자 오른쪽에 나와야한다~
    (메인쿼리:사원정보 조회)
    SELECT EMPLOYEE_ID AS 사원번호,
           EMP_NAME AS 사원명,
           DEPARTMENT_ID AS 부서번호,
           SALARY AS 급여
       FROM HR.EMPLOYEES
      WHERE SALARY > (평균급여)
     ORDER BY 3;
     (서브쿼리:평균급여 계산 즉 중간계산)
     SELECT AVG(SALARY)
       FROM HR.EMPLOYEES

  (결합)
  SELECT EMPLOYEE_ID AS 사원번호, 
           EMP_NAME AS 사원명, 
           DEPARTMENT_ID AS 부서번호, 
           SALARY AS 급여,
           (SELECT ROUND(AVG(SALARY))--일반 서브쿼리
              FROM HR.EMPLOYEES) AS 평균급여 --WHERE절이 참일때 수행되어짐. 51행이 나왔으니까 51번 실행됨
    FROM HR.EMPLOYEES
    WHERE SALARY > (SELECT AVG(SALARY)  --WHERE 절에 사용되었으니까 중첩서브쿼리(중요한건 아니다), 관련성 없는 서브쿼리(JOIN 없음), 참조될수없음(WHERE절 끝나면 서브퀴리 사라짐)
                      FROM HR.EMPLOYEES) --직원수만큼 서브쿼리 수행됨
    ORDER BY 4 DESC;

데이터 일부..

---

inline-view subquery -**반드시 실행되어져야 합니다.

   사용예) 사원테이블에서 사원들의 평균 급여보다 더 많은 급여를 받는 사원정보 조회
          Alias는 사원번호,사원명,부서번호,급여 -->이 서브쿼리는 한번만 실행, 위에는 한사원의 수만큼  
     SELECT A.EMPLOYEE_ID AS 사원번호,
            A.EMP_NAME AS 사원명,
            A.DEPARTMENT_ID AS 부서번호,
            A.SALARY AS 급여,
            ROUND(B.ASAL) AS 평균급여
       FROM HR.EMPLOYEES A, (SELECT AVG(SALARY) AS ASAL
                               FROM HR.EMPLOYEES) B --**반드시 실행되어져야 합니다.
      WHERE A.SALARY > B.ASAL --조인조건
      ORDER BY 4 DESC;

설명+

 

(inline view subquery) --(중요) inline view subquery는 독립실행되어져야 한다! 
    SELECT A.EMPLOYEE_ID AS 사원번호, 
           A.EMP_NAME AS 사원명, 
           A.DEPARTMENT_ID AS 부서번호, 
           A.SALARY AS 급여,
           ROUND(B.ASAL) AS 평균급여
    FROM HR.EMPLOYEES A,( SELECT AVG(SALARY) AS ASAL --서브쿼리 FROM절 할때 1번 실행되고 결과가 VIEW로 저장되며 참조(B.ASAL)될 수 있음, 만약 자료의 수가 많고 아주 복잡한 서브쿼리 였다면 수행속도에서 훨씬 빠르다.
                            FROM HR.EMPLOYEES) B 
    WHERE A.SALARY > B.ASAL --JOIN => 관련성 있는 서브쿼리
    ORDER BY 4 DESC;

 

다른버전
      사용예) 사원테이블에서 사원들의 평균 급여보다 더 많은 급여를 받는 사원정보 조회
          Alias는 사원번호,사원명,부서번호,급여
       SELECT EMPLOYEE_ID AS 사원번호,
              EMP_NAME AS 사원명,
              DEPARTMENT_ID AS 부서번호,
              SALARY AS 급여,
            (SELECT ROUND(AVG(SALARY))
                FROM HR.EMPLOYEES) AS 평균급여   -->WHERE절에서 결과가 나온걸로 평균급여가 나온다.
         FROM HR.EMPLOYEES
        WHERE SALARY > (SELECT AVG(SALARY)
                          FROM HR.EMPLOYEES)
        ORDER BY 4 DESC;

사용예)회원테이블에서 여성회원의 평균마일리지보다 더 많은 마일리지를 보유한
      회원의 회원번호,회원명,마일리지를 조회하시오.
      
 (메인쿼리:회원의 회원번호,회원명,마일리지를 조회)
    SELECT MEM_ID AS 회원번호,
           MEM_NAME AS 회원명 ,
           MEM_MILEAGE AS 마일리지 
      FROM MEMBER
     WHERE MEM_MILEAGE>(여성회원의 평균마일리지)
 (서브쿼리:여성회원의 평균마일리지)
    SELECT AVG(MEM_MILEAGE)
      FROM MEMBER
     WHERE SUBSTR(MEM_REGNO2,1,1) IN('2','4');

(결합)
    SELECT MEM_ID AS 회원번호,
           MEM_NAME AS 회원명 ,
           MEM_MILEAGE AS 마일리지 
      FROM MEMBER
     WHERE MEM_MILEAGE>(SELECT AVG(MEM_MILEAGE)
                         FROM MEMBER
                        WHERE SUBSTR(MEM_REGNO2,1,1) IN('2','4'));