220418 데이터베이스 서브쿼리(subQuery)

1. 서브쿼리(subQuery)

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

-- 1. 평균 급여보다 적은 사람 구하기
-- 평균 급여
SELECT	ROUND(AVG(SALARY)) 
FROM 	EMPLOYEES e 
;
-- 6,462가 출력됨

-- 구한 6,462를 타이핑 해서 사용
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 < (SELECT ROUND(AVG(SALARY)) FROM EMPLOYEES e )
ORDER BY SALARY 
;


-- 2. locations 테이블에서 country_id가 'US'인 location_id를 가진 부서 전체를 조회
-- country_id가 'US'인 location_id
SELECT 	LOCATION_ID 
FROM 	LOCATIONS l 
WHERE 	COUNTRY_ID = 'US'
;
-- 1400, 1500, 1600, 1700 출력됨

-- 구한 LOCATION_ID를 타이핑 해서 조회
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')
;

 

 

 

2. 예제

-- 1. 월급이 가장 적은 사람의 first_name, last_name 출력
SELECT 	min(SALARY)
FROM 	EMPLOYEES e 
;

SELECT 	FIRST_NAME , LAST_NAME 
FROM 	EMPLOYEES e 
WHERE 	SALARY = (SELECT min(SALARY)	FROM EMPLOYEES e )
;


-- 2. 월급이 가장 많은 사람의 first_name, last_name, job_title 출력
SELECT 	MAX(SALARY)
FROM 	EMPLOYEES e 
;

SELECT 	e.FIRST_NAME , e.LAST_NAME , j.JOB_TITLE 
FROM 	EMPLOYEES e 
	LEFT OUTER JOIN JOBS j ON e.JOB_ID = j.JOB_ID 
WHERE 	SALARY = (SELECT 	MAX(SALARY)
		  FROM 		EMPLOYEES e )	
;


-- 3. employees 테이블에서 Kochhar의 급여보다 많은 사원의 
-- 사원번호, 이름, 담당업무(job_id), 급여를 출력하라
SELECT 	SALARY 
FROM 	EMPLOYEES e
WHERE 	LAST_NAME = 'Kochhar'
;

SELECT 	EMPLOYEE_ID , FIRST_NAME , JOB_ID , SALARY 
FROM 	EMPLOYEES e 
WHERE 	SALARY > (SELECT 	SALARY 
		  FROM 		EMPLOYEES e
		  WHERE 	LAST_NAME = 'Kochhar')
;


-- 4. employees 테이블에서 급여의 평균보다 적은 사원의 
-- 사원번호, 이름, 담당업무, 급여, 부서번호를 출력하라
SELECT 	AVG(SALARY) 
FROM 	EMPLOYEES e 
;

SELECT 	EMPLOYEE_ID , FIRST_NAME , JOB_ID , SALARY , DEPARTMENT_ID 
FROM 	EMPLOYEES e 
WHERE 	SALARY < (SELECT 	AVG(SALARY) 
		  FROM 		EMPLOYEES e)
;