서브쿼리 (subQuery)
1. 서브쿼리란?
- Main Query에 반대되는 개념으로 이름을 붙인것
- 메인쿼리를 구성하는 소단위 쿼리
- select, insert, delete, update 절에서 모두 사용 가능
- 서브쿼리의 결과 집합을 메인 쿼리가 중간 결과 값으로 사용
- 서브 쿼리 자체는 일반쿼리와 다를 바가 없다.
SELECT ROUND(AVG(SALARY))
FROM EMPLOYEES e
;
SELECT *
FROM EMPLOYEES e
WHERE SALARY < 6462
;
SELECT *
FROM EMPLOYEES e
WHERE SALARY < ( SELECT ROUND(AVG(SALARY))
FROM EMPLOYEES e )
;
SELECT LOCATION_ID
FROM LOCATIONS l
WHERE COUNTRY_ID = 'US'
;
SELECT *
FROM DEPARTMENTS d
WHERE LOCATION_ID IN (1400, 1500, 1600, 1700)
;
SELECT *
FROM DEPARTMENTS d
WHERE LOCATION_ID IN (SELECT LOCATION_ID
FROM LOCATIONS l
WHERE COUNTRY_ID = 'US')
;
SELECT *
FROM EMPLOYEES e
WHERE e.SALARY = ( SELECT MIN(SALARY)
FROM EMPLOYEES e2)
;
SELECT *
FROM EMPLOYEES e
WHERE e.SALARY = ( SELECT MAX(SALARY)
FROM EMPLOYEES e2)
;
문제
SELECT EMPLOYEE_ID , FIRST_NAME , JOB_ID , SALARY
FROM EMPLOYEES e
WHERE SALARY > ( SELECT SALARY
FROM EMPLOYEES e2
WHERE LAST_NAME = 'Kochhar')
;
SELECT EMPLOYEE_ID , FIRST_NAME , JOB_ID , SALARY , DEPARTMENT_ID
FROM EMPLOYEES e
WHERE SALARY <( SELECT ROUND(AVG(SALARY))
FROM EMPLOYEES e2
)
;
SELECT DEPARTMENT_ID , MIN(SALARY)
FROM EMPLOYEES e
GROUP BY DEPARTMENT_ID
HAVING MIN(SALARY) > ( SELECT MIN(SALARY)
FROM EMPLOYEES e
WHERE DEPARTMENT_ID = 100)
;
SELECT e.EMPLOYEE_ID , e.FIRST_NAME , e.JOB_ID ,e.SALARY
FROM EMPLOYEES e
WHERE (JOB_ID, SALARY) IN ( SELECT JOB_ID, MIN(SALARY)
FROM EMPLOYEES e2
GROUP BY JOB_ID )
ORDER BY JOB_ID
;
SELECT EMPLOYEE_ID , FIRST_NAME , JOB_ID , SALARY
FROM EMPLOYEES e
WHERE SALARY > ( SELECT SALARY
FROM EMPLOYEES e
WHERE EMPLOYEE_ID = 192)
AND JOB_ID = ( SELECT JOB_ID
FROM EMPLOYEES e
WHERE EMPLOYEE_ID = 123)
;
SELECT MIN(SALARY)
FROM EMPLOYEES e
WHERE DEPARTMENT_ID = 50
;
SELECT *
FROM EMPLOYEES e
WHERE SALARY > ( SELECT MIN(SALARY)
FROM EMPLOYEES e
WHERE DEPARTMENT_ID = 50 )
AND DEPARTMENT_ID <> 50
;