Database_41_SUB QUERY_HAVING 절에서 서브쿼리 사용

 

-- HAVING 절에서 서브쿼리 사용
        -- Oracle server는 서브쿼리를 먼저 실행합니다.
        -- 메인쿼리의 HAVING 절에 결과를 반환합니다.
        -- 메인쿼리를 실행합니다.
        
    -- 최소 급여가 부서 50번의 급여보다 큰 부서를 조회
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >(
                SELECT MIN(salary)
                FROM employees
                WHERE department_id = 50);
    /*
     DP_ID   MIN(SALARY)        
     --------------------------
                7000
    90       17000
    20         6000
    110     8300
    80         8600
    60         4200
    10         440    
    */

    -- 서브쿼리 오류
        -- 서브쿼리의 일반적인 오류는 단일 행 서브쿼리에서 여러 행이 반환되는 경우입니다.
/*
SELECT employee_id, last_name
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);

<오류>---------------------------------------------------------------
ORA-01427: single-row subquery returns more than one row
01427. 00000 -  "single-row subquery returns more than one row"
*Cause:    
*Action:
-----------------------------------------------------------------------
*/

    -- 서브쿼리에서 발생할 수 있는 문제
        -- 내부 질의에서 행을 반환하지 않는 경우
/*
SELECT last_name, manager_id
FROM employees
WHERE job_id =
                (SELECT job_id
                FROM employees
                WHERE last_name = 'Haas');
        -- 존재하지 않는 데이터로 출력되는 결과가 없습니다.
        
SELECT last_name, manager_id
FROM employees
WHERE job_id =
                (SELECT job_id
                FROM employees
                WHERE last_name = 'abel');
        -- 대소문자 오류로 출력되는 결과가 없습니다.
*/
SELECT last_name, manager_id
FROM employees
WHERE job_id =
                (SELECT job_id
                FROM employees
                WHERE last_name = 'Abel');
    /*
    LAST_NAME   MANAGER_ID
    -------------------------------
    Abel            149
    Talyor          149
    Grant           149
    */

/*
SELECT *
FROM employees
WHERE hire_date = (
                    SELECT MAX(hire_date)
                    FROM employees
                    GROUP BY department_id);
        -- 서브쿼리가 다중 행을 반환하므로 오류가 발생합니다.
        <오류>------------------------------------------------------------------
        ORA-01427: single-row subquery returns more than one row
        01427. 00000 -  "single-row subquery returns more than one row"
        *Cause:    
        *Action:
        --------------------------------------------------------------------------
*/