Oracle - 서브쿼리, any, all

uglyduck.dev·2020년 9월 27일

따라해보기 🎭

목록 보기

11/12

서브쿼리(SubQuery)

Main Query에 반대되는 개념
메인쿼리를 구성하는 소단위 쿼리
SELECT, INSERT, DELETE, UPDATE 절에서 모두 사용 가능.
서브쿼리의 결과 집합을 메인 쿼리가 중간 결과값으로 사용.
서브쿼리 자체는 일반 쿼리와 다를 바가 없음.

모든 사원의 급여 평균

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE SALARY < (SELECT ROUND(AVG(SALARY))
                FROM EMPLPYEES);

EMPLOYEE_ID	FIRST_NAME	LAST_NAME
198	Donald	OConnell
199	Douglas	Grant
200	Jennifer	Whalen
202	Pat	Fay
104	Bruce	Ernst

DEPARTMENTS 테이블에 COUNTRY_ID가 US인 경우

SELECT *
FROM DEPARTMENTS
WHERE LOCATION_ID IN (SELECT LOCATION_ID
                      FROM LOCATIONS
                      WHERE COUNTRY_ID = 'US');

DEPARTMENT_ID	DEPARTMENT_NAME	MANAGER_ID	LOCATION_ID
60	IT	103	1400
50	Shipping	121	1500
10	Administration	200	1700
30	Purchasing	114	1700
90	Executive	100	1700
100	Finance	108	1700
110	Accounting	205	1700
120	Treasury	(null)	1700

월급이 가장 적은 사원

SELECT EMP.FIRST_NAME, EMP.LAST_NAME, JOB.JOB_TITLE
FROM EMPLOYEES EMP, JOBS JOB
WHERE EMP.SALARY = (SELECT MIN(SALARY) FROM EMPLOYEES)
AND EMP.JOB_ID = JOB.JOB_ID;

FIRST_NAME	LAST_NAME	JOB_TITLE
TJ	Olson	Stock

평균 급여보다 많이 받는 사원들의 명단 조회

SELECT EMP.FIRST_NAME, EMP.LAST_NAME, JOB.JOB_TITLE
FROM EMPLOYEE EMP, JOBS JOB
WHERE EMP.SALARY > ( SELECT AVG(SALARY) FROM EMPLOYEES)
AND EMP.JOB_ID = JOB.JOB_ID;

FIRST_NAME	LAST_NAME	JOB_TITLE
William	Gietz	Pulbic Accountant
Shelley	Higgins	Accounting Manager
Steven	Kin	President
Neena	Kochhar	Administration Vice President
Lex	De Haand	Administration Vice President
Jose	Manuel	Urman Accountant

ANY, ALL

어느 한 부서에서 받는 급여 출력

SELECT SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 20;

SALARY
13000
6000

부서번호가 20인 부서의 급여보다 더 많이 받는 사람(6000이상)을 조회

ANY

SELECT EMPLOYEE_ID, DEPARTMENT_ID, SALARY
FROM EMPLOYEES 
WHERE SALARY > ANY (SELECT SALARY
                    FROM EMPLOYEES
                    WHERE DEPARTMENT_ID = 20);

MIN() 집계 함수 사용

SELECT EMPLOYEE_ID, DEPARTMENT_ID, SALARY
FROM EMPLOYEES 
WHERE SALARY > (SELECT MIN(SALARY)
                FROM EMPLOYEES
                WHERE DEPARTMENT_ID = 20);

EMPLOYEE_ID	DEPARTMENT_ID	SALARY
100	90	24000
101	90	17000
102	90	17000
145	80	14000
146	80	13500
201	20	13000
205	110	12008

부서번호가 20인 부서의 급여보다 더 많이 받는 사람(13000이상)을 조회

ALL

SELECT EMPLOYEE_ID, DEPARTMENT_ID, SALARY
FROM EMPLOYEES 
WHERE SALARY > ALL (SELECT SALARY
                    FROM EMPLOYEES
                    WHERE DEPARTMENT_ID = 20);

MIN() 집계 함수 사용

SELECT EMPLOYEE_ID, DEPARTMENT_ID, SALARY
FROM EMPLOYEES 
WHERE SALARY > (SELECT MAX(SALARY)
                FROM EMPLOYEES
                WHERE DEPARTMENT_ID = 20);