justgo_developer

서브쿼리, any, all 본문

IT/Oracle

서브쿼리, any, all

다날92 2018. 10. 13. 19:29
728x90
반응형

서브쿼리(SubQuery)란?

- Main Query에 반대되는 개념으로 이름을 붙인 것.

- 메인쿼리를 구성하는 소단위 쿼리

- select, insert, delete, update절에서 모두 사용 가능

- 서브쿼리의 결과 집합을 메인쿼리가 중간 결과값으로 사용

- 서브쿼리 자체는 일반 쿼리와 다를 바가 없음.



/*서브쿼리*/

① select Round(avg(salary))

    from employees;

    -> 결과값 6462


② select employee_id, first_name, last_name

    from employees

    where salary < 6462;


select employee_id, first_name, last_name

from EMPLOYEES

where salary < Round(avg(salary)); /*where 절에서는 집계함수 사용불가*/

-> 에러


서브쿼리문 사용한 ①과 ② 

select employee_id, first_name, last_name

from EMPLOYEES

 where salary < ( select Round(avg(salary))

                        from employees );



select location_id 

from locations

where state_province is null;

-> state_province 속성값이 null인 경우에 location_ID값 가져오기


select *

from departments

where location_id in ( select location_id 

from locations

where state_province is null) ; 


select *

from departments

where location_id in ( select location_id 

                          from locations

                          where country_id ='US' );

-> 서브 쿼리 결과값이 다중 값으로 존재할떄 사용



/*월급이 가장 적은 사원*/ 

select emp.first_name, emp.last_name, job.job_title

from employees emp , jobs job

where emp.salary = ( select min(salary) from employees )

and emp.job_id = job.job_id ; 



/*평균 급여보다 많이 받는 사원들의 명단 조회 */

select emp.first_name, emp.last_name, job.job_title

from employees emp , jobs job

where emp.salary > ( select avg(salary) from employees )

and emp.job_id = job.job_id ;



<any, all>

다중 값을 얻어왔을때


select employees_id, department_id, salary

from employees 

where salary  > any ( select salary

from employees

where department_id = 20 );


= select employees_id, department_id, salary

   from employees 

   where salary  > ( select min(salary)

from employees

where department_id = 20 );



select employees_id, department_id, salary

from employees 

where salary  > all ( select salary

from employees

where department_id = 20 );

= select employees_id, department_id, salary

   from employees 

   where salary  >(  select max(salary)

from employees

where department_id = 20 );








728x90
반응형