SQL - 서브쿼리

1) 단일 행 서브쿼리 

SELECT FIRST_NAME FROM EMPLOYEES 
WHERE SALARY = (SELECT MIN(SALARY) FROM EMPLOYEES); 

-- 예제 'De Haan'과 salary가 동일한 직원 
SELECT *  
FROM EMPLOYEES A 
WHERE A.SALARY = ( 
                  SELECT SALARY FROM EMPLOYEES 
                  WHERE LAST_NAME = 'De Haan' 
                  );      -- '=': 단일 행 연산자 

-- 'Taylor'와 salary가 동일한 직원은? 
SELECT SALARY FROM EMPLOYEES WHERE LAST_NAME = 'Taylor'; -- 서브쿼리 
SELECT *  
FROM EMPLOYEES A 
WHERE A.SALARY = ( 
                  SELECT SALARY FROM EMPLOYEES 
                  WHERE LAST_NAME = 'Taylor' 
                  ); 
-- 서브쿼리가 다중 행일 경우 단일 행 연산자를 사용할 수 없음 
-- 즉, A.SALARY = 8600이면서 3200일수는 없음 
-- '=any' 등 연산자를 추가해줄 경우 해결 가능

 

2) 다중 행 서브쿼리 

-- 예제 department_id별로 가장 낮은 salary 조회, 해당 직원은 누구인가? 
SELECT MIN(SALARY) 최저급여 FROM EMPLOYEES GROUP BY DEPARTMENT_ID; -- 서브쿼리 
SELECT *  
FROM EMPLOYEES A 
WHERE A.SALARY IN ( 
                  SELECT MIN(SALARY) 최저급여 FROM EMPLOYEES 
                  GROUP BY DEPARTMENT_ID 
                  ) 
ORDER BY A.SALARY DESC;      -- 'IN': 다중 행 연산자 

-- '=ANY': 다중 행 연산자로, ANY와 ALL은 반드시 비교 연산자(>/=/<)를 앞에 같이 써야 함 
SELECT *  
FROM EMPLOYEES A 
WHERE A.SALARY =any ( 
                  SELECT MIN(SALARY) 최저급여 FROM EMPLOYEES 
                  GROUP BY DEPARTMENT_ID 
                  ) 
ORDER BY A.SALARY DESC;

 

3) 다중 열 서브쿼리: WHERE 조건식에서 비교되는 열이 여러 개

-- 예제 job_id별로 가장 낮은 salary 조회, 해당 직원은 누구인가? 
SELECT JOB_ID, MIN(SALARY) 그룹별급여 FROM EMPLOYEES GROUP BY JOB_ID; -- 서브쿼리 
SELECT * FROM EMPLOYEES A 
WHERE (A.JOB_ID, A.SALARY) IN ( 
                               SELECT JOB_ID, MIN(SALARY) 그룹별급여 
                               FROM EMPLOYEES 
                               GROUP BY JOB_ID 
                               ) 
ORDER BY A.SALARY DESC;

 

4) FROM절 서브쿼리: 인라인 뷰 

SELECT *  
FROM EMPLOYEES A,  
    (SELECT TO_CHAR(HIRE_DATE, 'YYYY'), SUM(SALARY) 
     FROM EMPLOYEES  
     GROUP BY TO_CHAR(HIRE_DATE, 'YYYY')) B; 

-- 예제 직원 중 department_name이 IT인 직원의 정보를 인라인 뷰를 이용해 출력 
SELECT * FROM EMPLOYEES A,  
            ( SELECT DEPARTMENT_ID 
              FROM DEPARTMENTS 
              WHERE DEPARTMENT_NAME = 'IT') B 
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID;