[Oracle] 인라인 뷰 / 고급 서브쿼리(단일, 다중)

서브쿼리 작성 가능한 곳

- GROUP BY 절을 제외한 SELECT 구문 전체에 작성 가능(FROM, WHERE, HAVING, ORDER BY)
- DML, DDL 구문에도 작성 가능

 

인라인 뷰

FROM 절에 서브쿼리가 작성된 경우를 말한다.

 

자신이 소속된 부서의 평균 급여보다 본인의 급여가 평균 이상인 사원을 출력

SELECT a.last_name, a.salary, a.department_id, b.salavg
FROM employees a JOIN(SELECT department_id,
			AVG(salary) salavg
			FROM employees
			GROUP BY department_id)
ON a.department_id = b.department_id
WHERE a.salary > b.salavg;

서브쿼리를 먼저 실행한 결과가 메인쿼리에서는 마치 하나의 테이블 역할을 해라!

- 이 쿼리문 안에서만 유효한 가상의 뷰로 테이블 역할을 하는데 FROM 뒤에는 테이블 명이 와야하니 alias를 사용한다.

- 부서에서 평균 이상의 급여를 받고 있는 사람을 출력하고 싶은데 부서의 평균 급여를 모르니까 서브쿼리로 작성

 

< employees a 테이블 >

nm	e_id	salary	d_id
A	100	3000	10
B	101	2000	20
C	102	5000	10
D	103	4000	20

 

< b 테이블>

d_id	salavg
10	4000
20	2250

A의 부서는 10, 10번 부서는 평균 급여가 4000이다. A의 급여는 3000이기 때문에 출력되지 않는다.

D의 부서는 20, 20번 부서는 평균 급여가 2250이다. D의 급여는 4000이기 때문에 출력된다.

 

 

---

단일 행 서브쿼리

서브쿼리에서 메인쿼리로 값이 하나만 넘어오는 경우 사용한다.

 

우변에 비교할 값이 하나만 올 수 있는 단일행 연산자

=	같음	<	보다 작음
>	보다 큼	<=	보다 작거나 같음
>=	보다 크거나 같음	<>	같지 않음

 

Bull과 담당 업무가 같되 급여는 Bull 보다 많이 받는 사원 출력

(단일 행 서브쿼리이면서 단일 컬럼 서브쿼리)

SELECT last_name, job_id, salary
FROM employees
WHERE job_id = (SELECT job_id
		FROM employees
                WHERE last_name = 'Bull')
AND   salary > (SELECT salary
		FROM employees
                WHERE last_name = 'Bull');

 

부서별 최소 급여를 받는 사원의 정보를 출력

SELET employee_id, last_name
FROM employees
WHERE salary = (SELECT MIN(salary)
		FROM employees
		GROUP BY department_id);

메인쿼리로 넘어가는 값이 여러 개인데(부서의 수만큼) 단일 행 연산자가 쓰였기 때문에 오류가 난다!

 

(다중 행 서브쿼리이면서 단일 컬럼 서브쿼리)

SELET employee_id, last_name
FROM employees
WHERE salary IN (SELECT MIN(salary)
		FROM employees
		GROUP BY department_id);

=> 다중 행 서브쿼리로 바꿀 것!

 

job_id가 값이 없어서 메인쿼리로 null이 넘어간 상태

값이 있었다면 본인 값이 나왔을 것!

SELECT last_name, job_id
  FROM employyes
 WHERE job_id = (SELECT job_id
		FROM employees
		WHERE last_name = 'Who');

 

다중 행 서브쿼리

서브쿼리에서 메인쿼리로 값이 여러 개 넘어오는 경우 사용한다.

 

우변에 값 리스트가 올 수 있는 다중행 비교 연산자

IN	= , OR
NOT IN	< > , AND
ANY	OR
ALL	AND

 

any, all은 비교연산자의 성질이 없어서 비교 연산자를 조합으로 쓴다

 [ =, >, >=, <=, <> ]  + ANY / ALL

 

(다중행 서브쿼리이면서 단일컬럼 서브쿼리)

SELECT employee_id, last_name, job_id, salary
  FROM employees
 WHERE salary < ANY
		(SELECT salary
		FROM employees
		WHERE job_id = 'IT_PROG')
   AND job_id <> 'IT_PROG';

SELECT employee_id, last_name, job_id, salary
  FROM employees
 WHERE salary < ALL
		(SELECT salary
		FROM employees
		WHERE job_id = 'IT_PROG')
   AND job_id <> 'IT_PROG';

 

서브쿼리에서 Null 값이 미치는 영향

자기 employee_id가 매니저리스트에 없는 사원만 출력 

= 자기 자신이 매니저가 아닌 사원들을 출력

SELECT last_name
  FROM employees
 WHERE employee_id NOT IN
		(SELECT manager_id
		FROM employees);

 

트리구조라 말단직원이 분명 있을 텐데 안 나오는 이유 : 각 리스트에 null 값도 포함이 되어있다!

 

단일 행 서브쿼리 : 서브쿼리로부터 null값이 반환되는 경우, 메인쿼리 결과는 null이다.

다중 행 서브쿼리 : 서브쿼리로 반환되는 값 리스트 중 null값이 포함된 경우 메인쿼리에서 AND 성격을 가지는 비교연산자(not it, < >all)를  사용하면 결과는 null이다.

- OR의 성격(IN, ANY)을 가진 비교연산자를 사용하는 건 하나만 만족시키면 되므로 상관 없다.

SELECT last_name
FROM employees
WHERE employee_id NOT IN
		(SELECT manager_id
		FROM employees
		WHERE manager_id is not null);

혹시 모를 매니저 아이디가 null이 아닌 경우의 조건을 추가한다!

 

 

서브쿼리 유형

단일 행 서브쿼리	다중 행 서브쿼리	단일 컬럼 서브쿼리	다중 컬럼 서브쿼리
단일 행 비교연산자	다중 행 비교연산자	비쌍 비교	쌍 비교 좌우변 컬럼 수가 같다

 

SELECT employee_id, manager_id, department_id
FROM employees
WHERE manager_id IN
		(SELECT manager_id
		FROM employees
		WHERE employee_id IN (174, 141))
AND department_id IN
		(SELECT department_id
		FROM employees
		WHERE employee_id IN (174, 141))
AND employee_id NOT IN(174, 141);

 

 

(다중행 서브쿼리이면서 다중컬럼 서브쿼리)

메인쿼리의 좌변에도 (서브쿼리의 항목처럼) 컬럼의 짝을 맞춰줘야한다.

WHERE (manager_id, department_id) IN
				(SELECT manager_id,department_id
				FROM Employees
				WHERE employee_id IN (174, 199))
AND employee_id NOT IN (174, 199);

 

부서별 최소급여가 메인쿼리에 넘어간다.

= 부서에서 최소급여 받는 사람만 출력

SELECT first_name, department_id, salary
FROM employees
WHERE (salary, department_id) IN
	(SELECT min(salary), department_id
	FROM employees
	GROUP BY department)id)
ORDER BY department_id;

부서별로 나옴