--SubQuery
절차, 일의 순서 가지고 있음.
SELECT SQL 구문에 SELECT 구문이 또 들어가는것
--위치에 따른 분류
(1) SubQuery : WHERE절에서 사용
(2) INLINE VIEW : FROM절에서 사용하는 서브쿼리
(3) Scalar Query : SELECT절에서 사용
--SubQuery 결과행 수에 따른 분류
(1)Single Row SubQuery : main query/OUTER쿼리와 비교 시 일반비교연산자 사용가능(=, <>)
(서브쿼리 결과가 한개 행만 반환하는 경우)
SELECT department_id, MAX(salary), employee_id, first_name
FROM employees
WHERE salary = (SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
);
--여기서 SELECT department_id, MAX(salary), employee_id, first_name
--FROM employees WHERE salary = 부분이 메인쿼리--SubQuery 결과행 수에 따른 분류
(2)Multi Row SubQuery : main query와 비교시 특수비교연산자 사용(IN, ANY>, ANY<, ALL>, ALL<)
(서브쿼리 결과가 여러 행 반환하는 경우)
SELECT department_id, MAX(salary), employee_id, first_name
FROM employees
WHERE salary IN (SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
);
--이거도 에러. 메인쿼리 서브쿼리 컬럼의 갯수가 동일해야함SELECT department_id, employee_id, first_name, salary
FROM employees
WHERE (department_id, salary) IN (SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
);--최대급여자의 사번, 이름, 급여를 출력하시오
--1) 최대급여를 계산한다
SELECT MAX(salary)
FROM employees;
--얘가 한개 행만 반환해서 Single Row SubQuery
--2) 1)과 같은 급여를 받는 사원의 사번, 이름, 급여를 출력하시오
SELECT employee_id, first_name, salary
FROM employees
WHERE salary = (SELECT MAX(salary)
FROM employees
);--사원들의 평균급여보다 많은 급여를 받는 급여자의 사번, 이름, 급여를 출력하시오
--1)사원들의 평균급여 계산한다.
SELECT AVG(salary)
FROM employees;
--2) 1)보다 많은 급여를 받는 급여자의 사번, 이름, 급여를 출력하시오
SELECT employee_id, first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary)
FROM employees
);JOIN도 가능하고 SubQuery도 가능한 상황이면 SubQuery를 사용해라
--부서별 최대급여를 받는 사원의 부서번호, 사번, 이름, 급여를 출력하시오
SELECT department_id, MAX(salary), employee_id, first_name
FROM employees
GROUP BY department_id;--(X)집계함수는 GROUP BY절에서 사용한 컬럼과 함께 SELECT절에서 사용가능
GROUP BY 없으면 실행안됨
SELECT department_id, MAX(salary), employee_id, first_name
FROM employees
GROUP BY department_id, employee_id, first_name; --(X)실행은 되나 옳지않은 결과, 사원별 최대 금액만 나옴
--Multi Row SubQuery : IN는 = ANY 어떤 것이라도 같음. (OR 연산자와 같은 의미로 판단)
> ANY 최소값보다 크다. any보다 크다
< ANY 최대값보다 작다. any보다 작다
> ALL 최대값보다 크다. 든것보다 크다. ALL보다 크다
< ALL 최대값보다 작다. 모든것보다 작다. ALL보다 작다
sub 결과가 10, 20, 40있을 때 10과 같거나 20과 같거나 40과 같거나, 이것들보다 커야함. 이것들보다 작아야함.
NOT IN은 or의 반대값
--부서별 최대급여를 받는 사원의 부서번호, 사번, 이름, 급여를 출력하시오
--1) 부서별 최대급여를 계산한다
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id;
--2) 1)과 같은 급여를 받는 사원의 부서번호, 사번, 이름, 급여를 출력하시오
SELECT department_id, MAX(salary), employee_id, first_name
FROM employees
WHERE salary = (SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
);NOT IN 예제
--부하직원이 있는 관리자의 관리자번호, 관리자 이름 출력하시오.
SELECT employee_id, first_name
FROM employees
WHERE employee_id IN (SELECT manager_id
FROM employees);
SELECT employee_id, first_name
FROM employees
WHERE employee_id IN (null, 100, 102, 103, 101, 108 …);--employee_id = null or employee_id = 100 or employee_id = 102 or employee_id = 103 or ....
→ null은 못함. 첫번째는 안됨. 나머지만 조건 추출
--OR의 반대, NOT IN 연산자
employee_id <> null and employee_id <> 100 and employee_id <> 102 ....
employee_id <> null null값은 비교대상이 되지 못하니 결과가 null
and라서 결과 조건 맞는 연산자 없음. NOT IN 연산자 위험하다. 결과 행수가 0건이 됨.
NOT IN 연산자 쓸땐 서브쿼리 null 값 있는지 없는지 확인.
--부하직원이 없는 모든 사원의 사번, 이름을 출력하시오.
SELECT employee_id, first_name
FROM employees
WHERE employee_id NOT IN (SELECT manager_id
FROM employees);
SELECT employee_id, first_name
FROM employees
WHERE employee_id NOT IN (null, 100, 102, 103, 101, 108 …);
SELECT employee_id, first_name
FROM employees
WHERE employee_id NOT IN (SELECT NVL(manager_id, -1)
FROM employees);--89명
--위와 동일하게 이 방법도 가능
SELECT employee_id, first_name
FROM employees
WHERE employee_id NOT IN (SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL);--89명NOT IN 예제 2
SELECT name
FROM Customer
MINUS
SELECT name
FROM Customer
WHERE custId IN (SELECT custId FROM Orders);
-- NOT IN 연산자로 변경
SELECT name
FROM customer
WHERE custid NOT IN (SELECT custid FROM orders);
