📅 공부 기간 : 07. 25(목)

⭐️ 단일행 서브쿼리

• 서브쿼리문의 값이 하나여야 함

-- [연습] 사원번호가 162번인 사원의 급여와 같은 급여를 받는 직원의 이름, 급여, 부서번호
-- 1) 찾은 값 : 10,500
SELECT salary
FROM hr.employees e 
WHERE employee_id = 162;

-- 2) 찾은 값을 가지고 다시 조회
SELECT first_name, salary, department_id 
FROM hr.employees e 
WHERE salary = 10500;

-- 3) 단일행 서브쿼리로 합치기
SELECT first_name, salary, department_id 
FROM hr.employees e 
WHERE salary = (SELECT salary
				FROM hr.employees e 
				WHERE employee_id = 162);

⭐️ 복수행(다중행) 서브쿼리

• 서브쿼리문의 값이 여러 개
• = 연산자 대신 IN 연산자 & ANY 연산자 & ALL 연산자로 비교

1. IN 연산자

/* [연습] 30번 부서의 직급들과 동일한 직급이
다른 팀에도 있는지 조사하는 서브쿼리를 작성하시오	*/
SELECT * FROM hr.employees e 
WHERE job_id IN (SELECT DISTINCT job_id
				 FROM hr.employees e 
				 WHERE department_id = 30);

2. ANY 연산자

• 전체 데이터 중 가장 큰 값보다 작은 값 → < any
• 전체 데이터 중 가장 작은 값보다 큰 값 → > any
• 전체 데이터 중 한 값과 같은 값 → = any

-- [연습] hr데이터베이스에 존재하는 employees 테이블에 'ST_MAN'이라는 직군이 있다.
-- 'ST_MAN'이라는 직군이 받는 급여보다 적은 급여를 받는 직원의 정보를 조회
			
-- 전체 데이터 중 가장 큰 값보다 작은 값
SELECT salary
FROM hr.employees e 
WHERE job_id = 'ST_MAN'
ORDER BY 1;
-- [정답]
SELECT first_name, salary
FROM hr.employees e
WHERE salary > ANY (SELECT salary
					FROM hr.employees e 
					WHERE job_id = 'ST_MAN'
					ORDER BY 1
					)
ORDER BY 2;

/* [연습2] job_title이 'Manager'인 직원과 동일한 급여를 받는 직원의
사번, 이름, job_id, 급여의 정보를 급여순으로 조회하시오 */
-- [정답]
SELECT employee_id, first_name, job_id, salary
FROM hr.employees e
WHERE salary = ANY (SELECT salary
					FROM hr.employees e INNER JOIN hr.jobs j
					USING (job_id)
					WHERE job_title LIKE '%Manager')
ORDER BY 4;

3. ALL 연산자

• 전체 데이터에서 제일 작은 값보다 작은 값 → < all
• 전체 데이터에서 제일 큰 값보다 큰 값 → > all

SELECT first_name, salary
FROM hr.employees e
WHERE salary < ALL (SELECT salary
					FROM hr.employees e 
					WHERE job_id = 'ST_MAN'
					ORDER BY salary
					)
ORDER BY 2;

다중 컬럼 서브쿼리

• 서브쿼리문이 뱉는 컬럼이 여러 개

-- [연습] 각 부서별 최고 금액을 수령하는 직원의 정보(사원번호, 이름, 급여, 부서명, 직급명)
-- 컬럼이 2개 조회됨
SELECT department_id, max(salary), min(salary) -- 그룹핑할 수 있는 정보들만 select절에 올 수 있음
FROM hr.employees e
GROUP BY department_id;

-- 서브쿼리 사용하기
SELECT e.employee_id, e.first_name, e.salary, d.department_name, j.job_title
FROM hr.employees e
INNER JOIN hr.departments d 
on e.department_id = d.department_id
INNER JOIN hr.jobs j
ON  e.job_id = j.job_id
WHERE (e.department_id, e.salary) IN(SELECT department_id, max(salary)
								FROM hr.employees e
								GROUP BY department_id
								); 

Scalar 서브쿼리

• SELECT절 의 컬럼 위치에 사용되는 서브쿼리

  성능 문제 때문에 Scalar 서브쿼리보다 JOIN을 사용하는 것이 좋음

SELECT
	(SELECT last_name FROM hr.employees e2 WHERE first_name='Bruce') AS "Bruce의 성",
	(SELECT last_name FROM hr.employees e2 WHERE first_name='Daniel') AS "Daniel의 성"
FROM hr.employees e 
WHERE first_name = 'David';

상호연관 서브쿼리

상호연관 서브쿼리에서 똑같은 절차를 계속 반복하는 것은 비효율적 => Inline View를 사용

-- [연습] 각 부서별로 해당 부서의 급여 평균 미만의 급여를 수령하는 직원 명단 조회
-- (부서번호, 사원번호, 이름, 급여, 부서별 평균급여)
SELECT department_id, employee_id, first_name, salary
FROM hr.employees e
WHERE salary <
			(SELECT avg(salary)
			FROM hr.employees e 
			WHERE department_id = e.department_id);
ORDER BY 1,2;

⭐️ Inline View

많이 사용됨

• 서브쿼리가 From절에 기술
• 서브쿼리로 조회되는 결과를 테이블로 사용함
• 서브쿼리에서 조회되는 컬럼에는 별칭을 사용해야 함

-- 상호연관 서브쿼리 코드를 인라인뷰로 수정
SELECT e.department_id, e.employee_id, e.first_name, e.salary, round(급여평균)
FROM (SELECT department_id "부서번호", avg(salary) AS "급여평균"
	  FROM hr.employees e 
	  GROUP BY department_id) tbl, hr.employees e -- INNER JOIN한 것 
WHERE tbl.부서번호 = e.department_id
AND e.salary < tbl.급여평균
ORDER BY 1,2;