Database_66_Subquery 응용_다중 열 서브쿼리, 쌍 비교, 비쌍 비교, 스칼라 서브쿼리, Correlated 상호관련 서브쿼리, EXISTS 연산

-- Advanced Subquery
    -- 다중 열 서브쿼리
        -- 두 개 이상의 열을 비교하기 위해 논리 연산자를 사용하여 혼합 WHERE 절을 작성합니다.
        -- 메인쿼리의 각 행은 Multiple_row/Multiple_column 서브쿼리의 값과 비교됩니다.
        /*
        SELECT column, column..
        FROM table
        WHERE ( column, column..) IN ( SELECT column, column..
                                                FROM table
                                                WHERE condition );
        */
        -- 비교 방식
            -- Pairwise_쌍 비교
            -- Nonpairwise_비쌍 비교

-- 데이터의 전체 개수를 확인합니다.
SELECT COUNT(*) FROM empl_demo;

  
-- 이름이 John인 직원을 조회합니다.
SELECT first_name, manager_id, department_id
FROM empl_demo
WHERE first_name='John';
        -- 이름이 같은 직원이 여럿 있는 경우 여러 개의 컬럼 값이 일치하는 사람을 찾아야 합니다.

    
-- 조건 1식
SELECT manager_id, department_id
FROM empl_demo
WHERE first_name='John';

    
    -- 쌍 비교
        -- 나열된 값을 순선대로 작성합니다.
        -- 서브쿼리가 독립적으로 사용되므로 메인쿼리 실행 시 John이 두번 조회될 수 있습니다.
            -- 중복을 막기 위해 AND 절을 작성합니다.
SELECT employee_id, manager_id, department_id
FROM empl_demo
WHERE (manager_id, department_id) IN ( SELECT manager_id, department_id
                                                        FROM empl_demo
                                                        WHERE first_name='John')
AND first_name <> 'John';

-- 조건 1식
SELECT manager_id 
FROM empl_demo
WHERE first_name = 'John';

-- 조건 2식
SELECT department_id
FROM empl_demo
WHERE first_name = 'John';

    
    -- 비쌍 비교
SELECT employee_id, manager_id, department_id
FROM empl_demo
WHERE manager_id IN ( SELECT manager_id 
                                FROM empl_demo
                                WHERE first_name = 'John' )
AND department_id IN ( SELECT department_id
                                FROM empl_demo
                                WHERE first_name = 'John' ) 
AND first_name <> 'John';

    
        -- 스칼라 서브쿼리식
            -- SELECT 문에서 열 또는 표현식 처럼 사용되는 하나의 행에서 하나의 열 값만 반환하는 서브쿼리입니다.
            -- 결과는 반드시 하나의 열에 하나의 값이어야 합니다.
            -- 서브 쿼리의 SELECT 목록에 있는 항목의 값입니다.
            -- NULL 값을 가질 수 있습니다.
            -- 두 개 이상의 값을 반환하는 경우 오류가 발생합니다.
            -- 사용하는 경우
                -- SELECT 절의 DECODE 및 CASE의 조건 및 표현식 부분
                -- GROUP BY를 제외한 SELECT의 모든 절
            
SELECT employee_id, last_name,
               CASE WHEN department_id=( SELECT department_id
                                                                FROM departments
                                                                WHERE location_id=1800 )
               THEN 'Canada' ELSE 'USA' END AS location
FROM employees;

-- JOIN을 사용하지 않고 JOIN 결과를 반환할 수 있습니다.
    -- 서브 쿼리에 결과 데이터가 없는 경우 NULL을 반환합니다.
SELECT d.department_id, d.department_name,
              ( SELECT MAX(salary)
                FROM employees
                WHERE department_id=d.department_id) AS MAX
FROM departments d;

        -- Correlated_상호관련 서브쿼리
            /*
            SELECT column1, column2...
            FROM table1 outer_table
            WHERE column1 operator ( SELECT column1, column2
                                                FROM table2
                                                WHERE expr1 = outer_table.expr2);
                    GET >> EXECUTE >> USET>> GET.... 반복
                    -- GET : 외부 질의의 후보 행을 호출합니다.
                    -- EXECUTE : 후보 행 값을 사용하여 내부 질의를 실행합니다.
                    -- USE : 내부 질의의 결과 값을 사용하여 후보 행을 검증합니다.
                    -- 후보 행이 남지 않을 때까지 반복합니다.
            */
            -- 서브쿼리가 상위 문에서 참조되는 테이블의 열을 참조합니다.
            -- 각 서브쿼리는 상위 문에서 처리되는 각 행에 대해 한 번씩 평가되어 질의의 모든 행에 대해 한 번씩 실행됩니다.
            -- 서브쿼리는 상위 쿼리 테이블의 열을 참조합니다.

-- 회사의 모든 부서의 평균 급여와 비교합니다.
SELECT employee_id, last_name, salary
FROM employees
WHERE salary> ANY( SELECT AVG(salary) 
                            FROM employees
                            GROUP BY department_id);

    
-- 부서별 평균 급여를 조회합니다.
SELECT department_id, AVG(salary) 
FROM employees
GROUP BY department_id;

    
-- 직원들의 부서와 급여를 조회합니다.
SELECT employee_id, last_name, salary, department_id
FROM employees;

    
    -- 서브쿼리의 부서 번호를 변수와 비교하여 값을 출력해야 합니다.
SELECT last_name, salary, department_id
FROM employees outer
WHERE salary>( SELECT AVG(salary)
                    FROM employees 
                    WHERE department_id= outer.department_id);

        -- EXISTS 연산자
            -- 서브쿼리의 결과 집합에 행이 있는지 테스트합니다.
            -- 데이터가 많지만 중복이 많은 경우에 많이 사용됩니다.
            -- 값이 존재하는 지만 확인하는 데에 많이 사용됩니다.
            -- 서브쿼리 행에 값이 있는 경우
                -- 검색이 INNER QUERY에서 계속 수행되지 않습니다.
                -- 조건은 TRUE로 플래그가 지정됩니다.
                -- 조건은 TRUE로 플래그가 지정됩니다.
            -- 서브쿼리 행에 값이 없는 경우
                -- 조건은 FALSE로 플래그가 지정됩니다.
                -- 검색이 INNER QUERY에서 계속 수행됩니다.
        -- NOT EXISTS 연산자
     
-- 데이터를 조회합니다.   
SELECT employee_id, last_name, manager_id
FROM employees;

    
-- EXISTS 연산자를 사용하여 부하직원이 있는 사원을 조회합니다.
SELECT employee_id, last_name, job_id, department_id
FROM employees outer
WHERE EXISTS ( SELECT 'X'
                        FROM employees
                        WHERE manager_id=outer.employee_id );
        -- 'X' : 플래그, 표시자라고 합니다.
        
-- IN 연산자를 사용한 결과와 동일합니다.
SELECT employee_id,last_name,job_id,department_id
FROM employees 
WHERE employee_id IN ( SELECT manager_id
                                FROM employees
                                WHERE manager_id IS NOT NULL );

    -- 성능 면에서 EXISTS 연산자를 사용하는 것이 좋습니다.
    
-- NOT EXISTS 연산자를 사용하여 사원이 없는 부서를 조회합니다.
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS ( SELECT 'X'
                            FROM employees
                            WHERE department_id = d.department_id );

-- NOT IN 연산자를 사용한 결과와 동일합니다.
SELECT department_id, department_name
FROM departments d
WHERE department_id NOT IN ( SELECT department_id
                            FROM employees
                            WHERE department_id = d.department_id );