[Oracle] 서브쿼리 Part1

◈ 단일행(Sing-Row) 서브쿼리 : SELECT문장으로부터 오직 하나의 행만을 검색하는 질의입니다

◈ 다중행(Multiple-Row) 서브쿼리 : SELECT문장으로부터 하나 이상의 행을 검색하는 질의입니다

◈ 다중열(Multiple-Column) 서브쿼리 : SELECT문장으로부터 하나 이상의 컬럼을 검색하는 질의입니다

◈ FROM절상의서브쿼리(INLINE VIEW) : FROM절상에 오는 서브쿼리로 VIEW처럼 작용합니다.

◈ 상관관계서브쿼리 : 바깥쪽 쿼리의 컬럼중의 하나가 안쪽 서브쿼리의 조건에 이용되는 처리방식입니다

 

 

 

--단일행(Single-Row)
--예제 : Sales Manager인 사원의 ID, 이름, JOB ID, 급여, 부서명출력

SELECT employee_id, first_name, job_id, salary, department_id
  FROM employees
  WHERE job_id = (SELECT job_id FROM jobs WHERE job_title = 'Sales Manager');

 

 

 

 

 

--다중행(Multiple-Row)

◈ 복수행 연산자(IN, NOT IN, ANY, ALL, EXISTS)를 사용할 수 있습니다.

 

 

--1. IN

--부서별로 가장 급여를 많이 받는 사원의 정보를 출력하는 예제입니다.
--예제 : 사원의 ID, 이름, JOB ID, 급여, 부서명출력

SELECT employee_id, first_name, job_id, salary, department_id
  FROM employees
  WHERE salary IN (SELECT MAX(salary) FROM employees GROUP BY department_id)
  ORDER BY department_id;

 

 

 

하지만, 다중행을 리턴하고 각 부서마다 최고 급여액은 다 다르다.

그 이유로 IN 을 사용했기때문에 동일부서의 여러사람이 나오게 된다.

이 해결방법은 뒤의 다중열 서브쿼리(Multiple-Column)에서 해결하겠다.

 

 

 

--2. ANY
--ANY 연산자는 서브 쿼리의 결과값중 어느 하나의 값이라도 만족이 되면 결과값을 반환합니다.
--예제 : 부서 ID 20의 어떤 사원 중 한명이라도 보다 더 급여가 높은사람

 

SELECT employee_id, first_name, job_id, salary, department_id
  FROM employees
  WHERE salary > ANY (SELECT salary FROM employees WHERE department_id = 20)
    AND department_id != 20
  ORDER BY department_id;

 

 

[department_id 가 20인 사람의 급여]

 

 

 

[department_id 가 20인 어떤 사람의 급여보다 높은 사람의 명단]

 

 

13000, 6000 중 하나라도 높으면 결과는 참이된다.

 

 

 

--3. ALL
--ALL 연산자는 서브 쿼리의 결과값 중 모든 결과값이 만족되야만 결과값을 반환합니다.
--예제 : 부서 ID 20의 사원의 급여보다 높은사람

SELECT employee_id, first_name, job_id, salary, department_id
  FROM employees
  WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 20)
    AND department_id != 20
  ORDER BY department_id;

 

 

 

 

 

--4. EXISTS
--EXISTS 연산자로 서브 쿼리의 데이터가 존재하는가의 여부를 먼저 따져 존재하는 값들만을 반환해줍니다.
--SUBQUERY에서 적어도 1개의 행을 RETURN하면 논리식은 참이고 그렇지 않으면 거짓입니다.

 

SELECT department_id
  FROM departments d
  WHERE EXISTS (SELECT * FROM employees e WHERE d.department_id = e.department_id);

 

 

 

 

 

Oracle® Database SQL Reference 10g Release 2 (10.2)에 명시되어있는 다중행 서브쿼리 (Multiple Subquery) 연사자의 예

 

 

 

 

 

--다중열 서브쿼리(Multiple-Column)
--다중열 서브쿼리란 서브쿼리의 결과값이 두개 이상의 컬럼을 반환하는 서브쿼리입니다

 

 

--1. Pairwise(쌍비교) Subquery
--서브쿼리가 한번 실행되면서 모든 조건을 검색해서 주쿼리로 넘겨줍니다.

 

--예제 : 부서별로 가장 급여를 많이 받는 사원의 정보를 출력하는 예제입니다.
SELECT e.employee_id, e.first_name, e.job_id, e.salary, d.department_name
  FROM employees e, departments d
  WHERE (e.department_id, e.salary) IN (SELECT department_id, MAX(salary) FROM employees GROUP BY department_id)
    AND e.department_id = d.department_id
  ORDER BY e.department_id;

 

 

 

 

--2. Nonpairwise(비쌍비교) Subquery
--서브쿼리가 여러 조건별로 사용되어서 결과값을 주쿼리로 넘겨줍니다.

 

SELECT e.employee_id, e.first_name, e.job_id, e.salary, d.department_name
  FROM employees e, departments d
  WHERE e.department_id IN (SELECT department_id FROM employees GROUP BY department_id)
    AND e.salary IN (SELECT  MAX(salary) FROM employees GROUP BY department_id)
    AND e.department_id = d.department_id
  ORDER BY e.department_id;