oracleDB XE11g R2, DBeaver 21.2.3
wihdow10
- 서브쿼리(subQuery)
- Main Query에 반대되는 개념으로 이름을 붙인 것
- 메인쿼리를 구성하는 소단위 쿼리
- SELECT, INSERT, DELETE, UPDATE 벌에서 모두 사용 가능
- 서브쿼리의 결과 집합을 메인 쿼리가 중간 결과값으로 사용
- 서브쿼리 자체는 일반 쿼리와 다를 바가 없다.
SELECT ROUND(AVG(SALARY)) FROM EMPLOYEES e;
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES e
WHERE SALARY < 6462
ORDER BY SALARY
;
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES e
WHERE SALARY < ROUND(AVG(SALARY)
ORDER BY SALARY
;
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES e
WHERE SALARY < (SELECT ROUND(AVG(SALARY)) FROM EMPLOYEES e)
ORDER BY SALARY
;
SELECT LOCATION_ID
FROM LOCATIONS l
WHERE COUNTRY_ID = 'US'
;
SELECT *
FROM DEPARTMENT d
WHERE LOCATION_ID IN (1400, 1500, 1600, 1700)
;
SELECT *
FROM DEPARTNMENT d
WHERE LOCATION_ID IN (SELECT LOCATION_ID
FROM LOCATIONS l
WHERE COUNTRY_ID = 'US')
;
SELECT MIN(SALARY) FROM EMPLOYEES e ;
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES e
WHERE SALARY = (SELECT MIN(SALARY) FROM EMPLOYEES e2)
;
SELECT MAX(SALARY) FROM EMPLOYEES e ;
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES e
WHERE SALARY = (SELECT MAX(SALARY) FROM EMPLOYEES e2)
;
SELECT MAX(SALARY) FROM EMPLOYEES e ;
SELECT e.FIRST_NAME, e.LAST_NAME, e.JOB_ID, e.SALARY
FROM EMPLOYEES e
INNER JOIN j
ON e.JOB_ID = j.JOB_ID
WHERE SALARY = (SELECT MAX(SALARY) FROM EMPLOYEES e)
;
SELECT e.FIRST_NAME, e.LAST_NAME, e.JOB_ID, e.SALARY
FROM EMPLOYEES e
INNER JOIN j
ON e.JOB_ID = j.JOB_ID
WHERE SALARY = (SELECT AVG(SALARY) FROM EMPLOYEES e)
;