[SQL] 8. Subquery

# Subquery

• 하나의 쿼리가 다른 쿼리에 포함되는 구조
• 다른 쿼리에 포함된 내부 쿼리는 외부쿼리에 사용될 값을 변환하는 역할
• SELECT, FROM, WHERE, HAVING에 사용된다.

• 단일행 비교연산자 : =, >, >=, <, <=, <>
• 다중행 비교연산자 : IN, ANY, ALL

---

# EX1)

// '나승원'과 직급이 같으며 '나승원'보다 급여를 많이 받는 사원 출력
SELECT	EMP_NAME
		, JOB_ID
		, SALARY
FROM	EMPLOYEE
WHERE	JOB_ID = (SELECT JOB_ID
				FROM	EMPLOYEE
				WHERE	EMP_NAME = '나승원')
AND		SALARY > (SELECT SALARY
				FROM	EMPLOYEE
				WHERE	EMP_NAME = '나승원');

 

# EX2)

//최소 급여를 받는 사람들 출력. 단일 행 비교연산자
SELECT	EMP_NAME
		, JOB_ID
		, SALARY
FROM	EMPLOYEE
WHERE	SALARY = (SELECT MIN(SALARY)
				FROM	EMPLOYEE) ;

 

# EX3)

SELECT	DEPT_NAME
		, SUM(SALARY)
FROM	EMPLOYEE
		LEFT JOIN DEPARTMENT USING(DEPT_ID)
GROUP BY DEPT_NAME
HAVING	SUM(SALARY) = (SELECT	MAX(SUM(SALARY))
						FROM	EMPLOYEE
						GROUP BY	DEPT_ID);
// 해외영업3팀       18100000

 

# EX4)

SELECT	EMP_ID
		, EMP_NAME
		, DEPT_ID
		, SALARY
FROM	EMPLOYEE
WHERE	SALARY IN (SELECT	MIN(SALARY)
					FROM	EMPLOYEE
					GROUP BY	DEPT_ID);
//잘못된 쿼리문

//********************************

//부서도 같이 넘겨줘야 한다.
SELECT	EMP_ID
		, EMP_NAME
		, DEPT_ID
		, SALARY
FROM	EMPLOYEE
WHERE	(SALARY, DEPT_ID) IN (SELECT	MIN(SALARY), DEPT_ID
					FROM	EMPLOYEE
					GROUP BY	DEPT_ID);
//다중 행이자 다중 열이 된다.

잘못된 출력

수정 후 출력

 

# EX5) - IN

SELECT	EMP_ID
		, EMP_NAME
		, '관리자' AS 구분
FROM	EMPLOYEE
WHERE	EMP_ID IN (SELECT MGR_ID FROM EMPLOYEE)
UNION
SELECT	EMP_ID
		, EMP_NAME
		, '직원'
FROM	EMPLOYEE
WHERE	EMP_ID NOT IN (SELECT MGR_ID FROM	EMPLOYEE
						WHERE	MGR_ID IS NOT NULL);

 

# EX6) - ANY(박스 안에서 움직인다.), ALL(박스 밖에서 움직인다.)

SELECT SALARY
FROM	EMPLOYEE
		JOIN JOB USING(JOB_ID)
WHERE	JOB_TITLE = '과장';					
//210, 230, 260

//*******************************
//4가지 경우의수
// >ANY, <ANY, >ALL, <ALL
SELECT	EMP_NAME
		, SALARY
FROM	EMPLOYEE
		JOIN JOB USING(JOB_ID)
WHERE	JOB_TITLE = '대리'
		AND SALARY > ALL (SELECT SALARY
						FROM	EMPLOYEE
						JOIN	JOB USING(JOB_ID)
						WHERE	JOB_TITLE = '과장')

 

# EX7)

일반적으로 INLINE VIEW가 성능이 더 좋다고 판단된다. 접근 횟수가 더 적다

//직급별 평균 급여를 조회하라. 
//WHERE에 서브쿼리가 부여
SELECT	EMP_NAME
		, JOB_TITLE
		, SALARY
FROM	EMPLOYEE
		JOIN JOB USING(JOB_ID)
WHERE	(JOB_ID, SALARY) IN (SELECT	JOB_ID
									, TRUNC(AVG(SALARY), -5) 
							FROM	EMPLOYEE
							GROUP BY JOB_ID);
							
//INLINE VIEW
//FROM에 서브쿼리가 부여
SELECT	E.EMP_NAME
		, J.JOB_TITLE
		, E.SALARY
FROM	(SELECT		JOB_ID
				,	TRUNC(AVG(SALARY), -5) JOBAVG
		 FROM		EMPLOYEE
		 GROUP BY 	JOB_ID) V
JOIN 	EMPLOYEE E ON( E.SALARY = V.JOBAVG AND E.JOB_ID = V.JOB_ID )
JOIN 	JOB J ON(J.JOB_ID = E.JOB_ID);

첫번째 쿼리 수행결과

두번째 쿼리 수행결과