[SQL] 오라클 sql 정리5 - 서브쿼리
9. 서브쿼리(subquery) : 여러개의 select 문장을 하나로 합쳐있는것
1) 서브쿼리
- select문에 삽입된 select문, sql문에 삽입된 다른 sql문
- select문에만 사용되지않고 insert, update, delete, select문 모두 사용가능
- 바깥쪽의 쿼리를 메인쿼리 or 기본쿼리라 한다.
- sql문에 삽입된 쿼리를 서브쿼리라 하며 반드시 괄호로 묶어 주어야한다.
- 서브쿼리가 메인쿼리 실행전에 한번 싱행된다.
- 서브쿼리의 결과가 메인쿼리에서 실행된다.
- select문에서는 select, from, where, having절 모두에서 삽입가능.
2) 서브쿼리의 종류
① 단일행 서브쿼리 : 서브쿼리의 실행결과가 행하나, 열 하나로 리턴되는 것, 메인쿼리에서 비교할 때 = 연산자를
이용하는 것이 가능
select ename, sal, hiredate, deptno
from emp
where sal>=(select max(sal)
from emp
where deptno=20);select *
from emp
where job=(select job
from emp
where ename='SMITH');select ename, deptno, sal
from emp
where sal>(select max(sal)
from emp
where deptno=20);select employee_id, first_name||' '||last_name name, hire_date
from employees
where hire_date like '05%'
and hire_date < (select hire_date
from employees
where first_name = 'Lisa');
or
where to(hire_date,'YYYY')='2005'select e.first_name, e.salary, d.department_name
from employees e, departments d
where e.department_id = d.department_id
and d.department_name = 'Sales'
and salary < (select avg(salary)
from employees
where department_id=100
group by department_id);select m.employee_id, m.last_name, m.hire_date, m.salary
from employees e, employees m
where e.last_name = 'De Haan'
and e.manager_id = m.employee_id;
select employee_id, last_name, hire_date, salary
from employees
where e.last_name = (select manager_id
from employees
where last_name = 'De Haan');select d.department_name, avg(e.salary)
from employees e, departments d
where e.department_id = d.department_id
group by d.department_name
having avg(e.salary) <= (select avg(salary)
from employees
where department_id = 30
group by department_id);
② 다중행 서브쿼리 : 서브쿼리의 실행결과가 열은 하나지만 행이 여러개 반환되는 것. = 연산자를 이용할 수 없다.
1) in 연산자
select ename, sal, deptno
from emp
where sal in(select sal
from emp
where job='MANAGER');select *
from emp
where deptno in (select deptno
from emp
where job='MANAGER');select ename, empno
from emp
where deptno in (select deptno
from emp
where ename like '%T%');select *
from emp
where sal in (select min(sal)
from emp
group by deptno); 2) all 연산자 : 모두 만족해야한다.
>all(서브쿼리) : 서브쿼리의 모든 값보다 커야함. 서브쿼리의 최대값보다 큰값.
select ename, sal, deptno
from emp
where sal >all(select sal
from emp
where job='MANAGER')
order by sal;<all(서브쿼리) : 서브쿼리의 최소값보다 작은 값.
select ename, sal, deptno
from emp
where sal <all(select sal
from emp
where job='MANAGER')
order by sal;
3) any : 결과 값 중 한개만 만족하면 됨.
>any(서브쿼리) : 서브쿼리의 어느값중 하나만 만족하면 됨. 서브쿼리의 최소값보다 큰 값.
select ename, sal, deptno
from emp
where sal >any (select sal
from emp
where job='MANAGER')
order by sal;
<any(서브쿼리) : 서브쿼리의 최대값보다 작은 값.
select ename, sal, deptno
from emp
where sal <any (select sal
from emp
where job='MANAGER')
order by sal;
③ 다중 열(컬럼) 서브쿼리 : 서브쿼리의 칼럼을 다수로 가져가 비교
select *
from emp
where (deptno, sal) in (select deptno, min(sal)
from emp
group by deptno);select employee_id, last_name, salary, department_id
from employees
where (department_id, salary) in (select department_id, max(salary)
from employees
group by department_id)
order by salary desc=> deptno도 같아야 할 경우
=> 칼럼 수가 하나면 deptno가 달라도 조회된다.
④ 상호연관 서브쿼리
- 메인쿼리의 칼럼을 서브쿼리에서 사용해야하는 경우.
- 메인쿼리의 row가 어떤 칼럼값을 가지고 있느냐에 따라서 서브쿼리의 결과가 달라진다.
[실행순서]
1) 메인쿼리에서 현재 작업중인 row의 칼럼값을 가져온다.
2) 메인쿼리에서 가져온 값을 이용해서 서브쿼리를 진행.
3) 서브쿼리에서 실행한 결과를 다시 메인쿼리로 전달 후 메인쿼리를 실행.
4) 메인쿼리에서 사용하는 테이블의 전체 row에 대해서 1~3번까지의 작업을 반복수행.
select ename, sal, deptno
from emp main
where sal > (select avg(sal)
from emp e
where e.deptno = main.deptno);⑤ View
- view생성 권한부여
conn system/manager
grant create view to scott;- view생성
create view myempview
as
select d.dname, e.ename, e.sal, e.hiredate
from emp e, dept d
where e.deptno= d.deptno;
- view생성시에는 컬럼에 alias가 꼭 필요.
create view maxsaldata
as
select deptno, max(sal) maxsal
from emp
group by deptno;create view avgsal
as
select deptno, avg(sal) 평균급여
from emp
group by deptno;
- view 사용
select e.deptno, e.empno, e.ename, e.sal, s.평균급여
from emp e, avgsal s
where e.deptno=s.deptno(+);
⑥ inline view
- from절에 정의하고 사용하는 서브쿼리.
- 서브쿼리를 이용해서 from절에서 사용할 가상의 테이블을 정의할 수 있는 데, 이를 inline view라 한다.
- 하나의 테이블에 데이터가 많이 있는 경우 from정에 정의하면 비효율적일 수 있으므로 필요한 행과 열만
from절에서 사용할 수 있도록 한다.
[정의방법]
select
from (select
from
where
group by
having )aliasselect e.deptno, e.empno, e.ename, e.sal, avgt.avgsal
from emp e, (select deptno, avg(sal) avgsal
from emp
group by deptno) avgt
where e.deptno = avgt.deptno and
e.sal>avgt.avgsal;=> from절에 삽입되는 서브쿼리를 사용하는 경우 반드시 서브쿼리 뒤에 alias를 정의해야한다.(테이블명으로 사용)
서브쿼리안에서도 계산의 결과로 만들어지는 칼럼은 반드시 alias를 정의해야한다.(칼럼명으로 사용)
⑦ ton-n 서브쿼리
- row num을 활용해서 순위와 연관있는 서브쿼리.
- rownum사용
select rownum, ename, sal
from emp;select 월, 입사자수
from (select substr(hire_date,4,2) 월, count(*) 입사자수
from employees
group by substr(hire_date,4,2)
order by 입사자수 desc)
where rownum<4
order by 월;
select to_char(hire_date,'MM') 월, count(employee_id) 입사자수
from employees
group by to_char(hire_date,'MM')
order by 입사자수 descselect first_name, salary, department_name
from (select e.first_name, e.salary, d.department_name
from employees e, departments d
where department_name = 'IT'
and e.department_id = d.department_id
order by salary desc)
where rownum<4;select rownum, ename, sal
from emp
where rownum<4
order by sal desc;->원하는 결과 X
select rownum, ename, sal
from (select *
from emp
order by sal desc)
where rownum <4;->원하는 결과 O
1) row_number() over (order by '순위 기준 칼럼' desc)
-> 동률자 순차적 순번
select row_number() over (order by sal desc) 순위, ename, sal, deptno
from emp; 순위 ENAME SAL DEPTNO
---------- -------------------- ---------- ----------
1 KING 5000 10
2 FORD 3000 20
3 SCOTT 3000 20
4 JONES 2975 20
5 BLAKE 2850 30
6 CLARK 2450 10
7 ALLEN 1600 30
8 TURNER 1500 30
9 MILLER 1300 10
10 ADAMS 1300 20
11 WARD 1250 30
12 MARTIN 1250 30
13 JAMES 950 30
14 SMITH 800 20
15 율곡 500 90
16 이이이 500
2) rank() over (order by '순위 기준 칼럼' desc)
-> 동률자 같은 순번 + 다음 순서 동률자 계산O
select rank() over (order by sal desc) 순위, ename, sal, deptno
from emp; 순위 ENAME SAL DEPTNO
--------- -------------------- ---------- ----------
1 KING 5000 10
2 FORD 3000 20
2 SCOTT 3000 20
4 JONES 2975 20
5 BLAKE 2850 30
6 CLARK 2450 10
7 ALLEN 1600 30
8 TURNER 1500 30
9 MILLER 1300 10
9 ADAMS 1300 20
11 WARD 1250 30
11 MARTIN 1250 30
13 JAMES 950 30
14 SMITH 800 20
15 율곡 500 90
15 이이이 500
3) dense_rank() over (order by '순위 기준 칼럼' desc)
-> 동률자 같은 순번 + 다음 순서 동률자 계산
select dense_rank() over (order by sal desc) 순위, ename, sal, deptno
from emp; 순위 ENAME SAL DEPTNO
---------- -------------------- ---------- ----------
1 KING 5000 10
2 FORD 3000 20
2 SCOTT 3000 20
3 JONES 2975 20
4 BLAKE 2850 30
5 CLARK 2450 10
6 ALLEN 1600 30
7 TURNER 1500 30
8 MILLER 1300 10
8 ADAMS 1300 20
9 WARD 1250 30
9 MARTIN 1250 30
10 JAMES 950 30
11 SMITH 800 20
12 율곡 500 90
12 이이이 500
4) ntile('그룹 갯수') over (order by '순위 기준 칼럼' desc)
-> 그룹갯수의 수로 균등한 숫자로 순위 줌. 전체 레코드/그룹개수
-> 나머지는 위에 부터 순차적으로 들어감.
select ntile(3) over (order by sal desc) 순위, ename, sal, deptno
from emp; 순위 ENAME SAL DEPTNO
---------- -------------------- ---------- ----------
1 KING 5000 10
1 FORD 3000 20
1 SCOTT 3000 20
1 JONES 2975 20
1 BLAKE 2850 30
1 CLARK 2450 10
2 ALLEN 1600 30
2 TURNER 1500 30
2 MILLER 1300 10
2 ADAMS 1300 20
2 WARD 1250 30
3 MARTIN 1250 30
3 JAMES 950 30
3 SMITH 800 20
3 율곡 500 90
3 이이이 500
5) 위에 합수 over 구문에 partition by '그룹핑' 하여 그 그룹안에서 각각 순위를 정한다.
select rank() over( partition by deptno order by sal desc) 순위, ename, sal, deptno
from emp; 순위 ENAME SAL DEPTNO
---------- -------------------- ---------- ----------
1 KING 5000 10
2 CLARK 2450 10
3 MILLER 1300 10
1 SCOTT 3000 20
1 FORD 3000 20
3 JONES 2975 20
4 ADAMS 1300 20
5 SMITH 800 20
1 BLAKE 2850 30
2 ALLEN 1600 30
3 TURNER 1500 30
4 MARTIN 1250 30
4 WARD 1250 30
6 JAMES 950 30
1 율곡 500 90
1 이이이 500