Oracle - 서브쿼리, any, all

uglyduck.dev·2020년 9월 27일
0

따라해보기 🎭

목록 보기
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_IDFIRST_NAMELAST_NAME
198DonaldOConnell
199DouglasGrant
200JenniferWhalen
202PatFay
104BruceErnst

DEPARTMENTS 테이블에 COUNTRY_ID가 US인 경우

SELECT *
FROM DEPARTMENTS
WHERE LOCATION_ID IN (SELECT LOCATION_ID
                      FROM LOCATIONS
                      WHERE COUNTRY_ID = 'US');
DEPARTMENT_IDDEPARTMENT_NAMEMANAGER_IDLOCATION_ID
60IT1031400
50Shipping1211500
10Administration2001700
30Purchasing1141700
90Executive1001700
100Finance1081700
110Accounting2051700
120Treasury(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_NAMELAST_NAMEJOB_TITLE
TJOlsonStock

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

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_NAMELAST_NAMEJOB_TITLE
WilliamGietzPulbic Accountant
ShelleyHigginsAccounting Manager
StevenKinPresident
NeenaKochharAdministration Vice President
LexDe HaandAdministration Vice President
JoseManuelUrman 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_IDDEPARTMENT_IDSALARY
1009024000
1019017000
1029017000
1458014000
1468013500
2012013000
20511012008

부서번호가 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);
EMPLOYEE_IDDEPARTMENT_IDSALARY
1468013500
1458014000
1029017000
1019017000
1009024000

Reference

profile
시행착오, 문제해결 그 어디 즈음에.

0개의 댓글