서브쿼리
- 다른 sql문에 삽입된 select문
- 서브쿼리는 괄호로 묶어야 한다.
- where절에서 비교를 위해서 사용하는 연산자 오른쪽에 정의
- 서브쿼리가 먼저 실행되고 그 결과를 메인쿼리에서 사용
- select절, from절,where,group by,having,order by, update문, delete문, insert문
바깥쪽쿼리 : 메인쿼리
안에 삽입된 쿼리 : 서브쿼리
SQL> select *
2 from emp
3 where sal>(select avg(sal) from emp);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
7566 JONES MANAGER 7839 81/04/02 2975 20
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7788 SCOTT ANALYST 7566 82/12/09 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7902 FORD ANALYST 7566 81/12/03 3000 20
6 rows selected.서브쿼리 종류
1) 단일행 서브쿼리
- 서브쿼리의 실행결과가 1행 1열인 쿼리
- =,>,>=,<,<=,!=연산자를 이용해서 작업
7566번 사원의 업무와 같은 업무를 하는 사원의 목록을 조회
SQL> select *
2 from emp
3 where job = (select job
4 from emp
5 where empno=7566);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
7566 JONES MANAGER 7839 81/04/02 2975 20
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10미션
1. 20번 부서의 최고 급여보다 급여가 많은 사원들의 사원명, 부서코드, 급여 목록 출력하기
select ename,deptno, sal
from emp
where sal>(select max(sal)
from emp
where deptno=20);2. 1982년에 입사한 직원의 평균 급여보다 급여가 높은 사원들의 사원명, 입사일, 급여의 목록을 출력하기
select ename,hiredate,sal
from emp
where sal>(select avg(sal)
from emp
where substr(hiredate,1,2)=82);where substr(hiredate,1,2)=82대신
where hiredate like '82%'도 가능
2) 다중행 서브쿼리
- 서브쿼리의 결과가 다중행인경우
- 다중행이므로 비교연산자를 사용할수없다(비교연산자를 사용하는 경우 any나 all과 같은 연산자를 이용)
- in, any, all
컬럼 in(값1, 값2,...)
미션. 이름에 'T'를 포함하고 있는 사원과 같은 부서에서 근무하는 사원의 이름과 사원번호 출력
select ename, empno, deptno
from emp
where deptno in (select deptno
from emp
where ename like'%T%'); -> 동일한 컬럼에 값이 여러 개 나와서 in연산자 씀
- 컬럼 < any =>최대값보다 작은값을 조회
select ename,sal
from emp
where sal<any(800,1250,1600);- 컬럼 > any =>최소값보다 큰값을 조회
select ename,sal
from emp
where sal>any(800,1250,1600);- 컬럼 < all =>최소값보다 작은값을 조회
select ename,sal
from emp
where sal<all (900,1250,1600);- 컬럼 > all =>최대값보다 큰값을 조회
select ename,sal
from emp
where sal>all (900,1250,1600);각 부서에서 최소급여를 받는 사용자의 사원번호, 부서번호, 급여를 출력
select empno,deptno,sal
from emp
where sal in(select min(sal)
from emp
where deptno is not null
group by deptno);3) 다중컬럼 서브쿼리
:두개 이상의 컬럼을 리턴하는 서브쿼리
SQL> select * from emp where deptno=20;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80/12/17 800 20
7566 JONES MANAGER 7839 81/04/02 2975 20
7788 SCOTT ANALYST 7566 82/12/09 3000 20
7876 ADAMS CLERK 7788 83/01/12 1100 20
7902 FORD ANALYST 7566 81/12/03 3000 20SQL> update emp
2 set sal = 1300
3 where empno = 7876;
1 row updated.
SQL> select * from emp where deptno=20;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80/12/17 800 20
7566 JONES MANAGER 7839 81/04/02 2975 20
7788 SCOTT ANALYST 7566 82/12/09 3000 20
7876 ADAMS CLERK 7788 83/01/12 1300 20
7902 FORD ANALYST 7566 81/12/03 3000 20
SQL> commit;
Commit complete.각 부서에서 최소급여를 받는 사용자의 사원번호, 부서번호, 급여를 출력
SQL> select empno,deptno,sal
2 from emp
3 where sal in(select min(sal)
4 from emp
5 where deptno is not null
6 group by deptno);
EMPNO DEPTNO SAL
---------- ---------- ----------
7369 20 800
7876 20 1300
7900 30 950
7934 10 1300SQL> select empno,deptno,sal
2 from emp
3 where (deptno,sal) in(select deptno,min(sal)
4 from emp
5 where deptno is not null
6 group by deptno);
EMPNO DEPTNO SAL
---------- ---------- ----------
7900 30 950
7369 20 800
7934 10 1300in연산자는 한개라도 일치하면 나오는거고, deptno로 그룹화해서 최솟값을 구하고, 이후 그 최소값만으로 비교를해서 다른 그룹의 최솟값과 일치해도 출력됨. 그래서 다중컬럼 서브쿼리에서 최솟값과 그룹값 두개를 다 비교해서 둘다 일치하는 값만 출력
4) 상관형 서브쿼리(상호 연관 서브쿼리 - Correlated Subquery), 260p
-> 성능낮음, 상관형 서브쿼리로 만든 코드 업데이트하는 업무 맡을 수 있으니 알아야 함
- 메인쿼리의 한 row(행)에 대해서 서브쿼리가 한번씩 실행된다
- 서브쿼리를 실행할 때 메인쿼리 테이블의 컬럼(열)의 값을 참조해서 서브쿼리를 실행
- 서브쿼리의 비교값이 계속 바뀌는 경우 메인쿼리의 값을 참조
->메인쿼리에서 비교하는 값에 따라 서브쿼리의 결과가 달라지는 경우 - 메인쿼리의 테이블에서 alias를 추가하고 서브쿼리에서 참조할 수 있도록 작업
- 메인쿼리의 한 row에 대해서 서브쿼리가 한번씩 실행
- 서브쿼리에서는 메인쿼리의 컬럼을 사용해서 결과를 계산
[실행흐름]
1. 메인쿼리에서 참조할 값을 받아서 서브쿼리를 실행
<2. 서브쿼리의 실행결과를 컬럼이 한 개 row가 한개의 결과를 반환>
3. 메인쿼리에서 서브쿼리의 결과를 받아서 메인쿼리 실행
4. 1번에서 3번까지를 테이블의 레코드 갯수만큼 반복해서 실행
SQL> select *
2 from emp
3 where sal > (select avg(sal)
4 from emp
5 where job='ANALYST');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
7839 KING PRESIDENT 81/11/17 5000 10SQL> select *
2 from emp
3 where sal > (select avg(sal)
4 from emp
5 where deptno=20);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
7566 JONES MANAGER 7839 81/04/02 2975 20
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7788 SCOTT ANALYST 7566 82/12/09 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7902 FORD ANALYST 7566 81/12/03 3000 20
6 rows selected.소속부서의 급여의 평균보다 높은급여를 받는 사원들의 정보 조회하기
-> 레코드 갯수만큼 서브쿼리가 실행되므로 성능저하의 원인이 될수있다.
SQL> select *
2 from emp e
3 where sal>(select avg(sal)
4 from emp
5 where deptno=e.deptno);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7566 JONES MANAGER 7839 81/04/02 2975 20
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7788 SCOTT ANALYST 7566 82/12/09 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7902 FORD ANALYST 7566 81/12/03 3000 20
6 rows selected.5) from절에 서브쿼리(inline view)
- from절에 서브쿼리를 추가할수있다
- 내부에서 사용하기편한 데이터를 가공해서 가상의 테이블을 만들고 작업하는방식
소속부서의 급여의 평균보다 높은급여를 받는 사원들의 정보 조회하기
SQL> select e.*
2 from emp e,(select deptno, avg(sal) avgsal
3 from emp
4 group by deptno) s
5 where e.deptno = s.deptno and
6 e.sal>s.avgsal;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7566 JONES MANAGER 7839 81/04/02 2975 20
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7788 SCOTT ANALYST 7566 82/12/09 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7902 FORD ANALYST 7566 81/12/03 3000 20
6 rows selected.-> 리소스 훨씬 적고 성능높음
단일행서브쿼리 미션
- Employees 테이블에서 입사일자(hire_date)에 따라 2005년 입사한 직원들 가운데 first_name이 'Lisa'인 직원보다 빨리 입사한 직원의 사번(employee_id), 이름(first_name), 성(last_name), 입사일자(hire_date)를 조회하는 SQL 문장을 작성하시오.
단, <실행 결과>처럼 first_name과 하나의 공백(space bar), last_name 은 name 이라는 컬럼명으로 출력되도록 합니다.
select employee_id, concat(concat(first_name,' '),last_name ) as name, hire_date
from employees
where hire_date<(select hire_date
from employees
where first_name='Lisa')
and hire_date like '05%';- Sales’부서에 속한 직원의 이름(first_name), 급여(salary), 부서이름(department_name)을 조회하시오. 단, 급여는 100번 부서의 평균보다 적게 받는 직원 정보만 출력되어야 합니다.
select first_name,salary,department_name
from employees e, departments d
where e.department_id=d.department_id and
department_name='Sales' and
salary<(select avg(salary)
from employees
where department_id=100);- De Haan 사원의 관리자 사원번호, 이름(last_name), 입사일 및 급여를 표시하는 SQL문을 작성하십시오.alias를 포함하여 실행결과와 동일하게 출력되어야 합니다.
select employee_id,last_name,hire_date,salary
from employees
where employee_id = (select manager_id
from employees
where last_name='De Haan');https://blog.naver.com/heaves1/223410651869
서브쿼리 미션
[조인]
- 자신의 매니저 보다 연봉을 높게 받는 직원의 FIRST_NAME과 SALARY를 출력하세요
select e.first_name, e.salary
from employees e, employees m
where e.manager_id=m.employee_id
and e.salary>m.salary이건 그냥 hr계정으로 연습해본거
select e.employee_id,e.first_name, m.manager_id,m.first_name
from employees e, employees m
where e.manager_id=m.employee_id
select m.first_name 관리자명, count(e.employee_id) 인원수
from employees e, employees m
where e.manager_id=m.employee_id
group by m.first_name;[서브쿼리]
- 부서 이름(department_name) 별 직원들의 평균연봉(salary) 을 조회하시오.단,'30번’ 부서의 직원 평균 연봉보다 평균 연봉이 이하인 부서 정보만 출력되어야 합니다.
select department_name, avg(salary)
from departments d, employees e
where d.department_id=e.department_id
group by department_name
having avg(salary)<=(select avg(salary)
from employees
where department_id=30);- 각 부서(department_id)별로 최고 연봉(salary)를 받는 사원의 사번(employee_id), 성(last_name)과 연봉(salary)을 조회하시오. 단 조회결과는 연봉의 내림차순으로 정렬되어 나타나야 합니다.
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;- 업무명(job_title)이 ‘Sales Representative’인 직원 중에서 연봉(salary)이 9,000 이상, 10,000 이하인 직원들의 이름(first_name), 성(last_name)과 연봉(salary)을 출력하시오
select first_name, last_name, salary
from (select *
from jobs j, employees e
where j.job_id=e.job_id
and job_title ='Sales Representative'
and salary between 9000 and 10000);top n쿼리
: 데이터를 특정 기준에 따라 정렬하고 정렬된 데이터를 상위 N개만 조회하기 위해 적용
- from절에 서브쿼리를 이용해서 원하는 데이터의 유형을 만드고 작업
- 오라클은 rownum을 활용할 수 있다.
rownum : 데이터 일련번호 붙이기
SQL> select rownum, ename, sal
2 from emp;
ROWNUM ENAME SAL
---------- -------------------- ----------
1 SMITH 800
2 ALLEN 1600
3 WARD 1250
4 JONES 2975
5 MARTIN 1250
6 BLAKE 2850
7 CLARK 2450
8 SCOTT 3000
9 KING 5000
10 TURNER 1500
11 ADAMS 1300
12 JAMES 950
13 FORD 3000
14 MILLER 1300
15 RM 500
16 슈가 500
16 rows selected.SQL> select rownum, ename, sal
2 from emp
3 where rownum<4;
ROWNUM ENAME SAL
---------- -------------------- ----------
1 SMITH 800
2 ALLEN 1600
3 WARD 1250이렇게 하면 일련번호가 뒤죽박죽이 됨
SQL> select rownum, ename, sal
2 from emp
3 where rownum<4
4 order by sal desc;
ROWNUM ENAME SAL
---------- -------------------- ----------
2 ALLEN 1600
3 WARD 1250
1 SMITH 800서브쿼리안에서 미리 정렬 후 뽑으면 일련번호 순서대로 나옴
SQL> select rownum,ename,sal
2 from(select *
3 from emp
4 order by sal desc);
ROWNUM ENAME SAL
---------- -------------------- ----------
1 KING 5000
2 FORD 3000
3 SCOTT 3000
4 JONES 2975
5 BLAKE 2850
6 CLARK 2450
7 ALLEN 1600
8 TURNER 1500
9 MILLER 1300
10 ADAMS 1300
11 WARD 1250
12 MARTIN 1250
13 JAMES 950
14 SMITH 800
15 슈가 500
16 RM 500
16 rows selected.서브쿼리만 보면 이런모양
select *
from emp
order by sal desc
;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------ -------------------- ------------------ ---------- -------- ---------- ---------- ----------
7839 KING PRESIDENT 81/11/17 5000 10
7902 FORD ANALYST 7566 81/12/03 3000 20
7788 SCOTT ANALYST 7566 82/12/09 3000 20
7566 JONES MANAGER 7839 81/04/02 2975 20
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7934 MILLER CLERK 7782 82/01/23 1300 10
7876 ADAMS CLERK 7788 83/01/12 1300 20
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7900 JAMES CLERK 7698 81/12/03 950 30
7369 SMITH CLERK 7902 80/12/17 800 20
8888 슈가 24/04/09 500
7777 RM 24/04/09 500조건 넣어주면 원하는 대로 나옴
SQL> select rownum,ename,sal
2 from(select *
3 from emp
4 order by sal desc)
5 where rownum <4;
ROWNUM ENAME SAL
---------- -------------------- ----------
1 KING 5000
2 SCOTT 3000
3 FORD 3000top-N쿼리 연습
1. 월 별 입사자 수를 조회하되 입사자수가 가장 많은 상위 3개의 달만 출력되록 하시오.
select mon as 월, cnt as 입사자
from(select to_char(hire_date,'MM') as mon, count(employee_id) as cnt
from employees
group by to_char(hire_date,'MM')
order by cnt desc) emp
where rownum<=3
order by 월;강사님이 질문 답변해주신거
from절의 alias의 용도
from절에서 만들어지는 컬럼은 원래 물리적으로 하드디스크에 저장되어 있는 컬럼이 아니라 계산으로 만들어지는 컬럼이므로 컬럼명이 없습니다. 컬럼명이 없으면 오라클에서 연산에 사용할 수 없으므로 이 to_char(hire_date,'MM')와 같이 만들어진 컬럼을 메인 쿼리에서 액세스 하려면 반드시 컬럼명이 있어야 합니다.
메인쿼리 order by 용도
메인쿼리에 오더바이가 지금 이 예제에는 없어도 결과가 나오는데 top-n쿼리를 만들때는 필수로 넣어주신다고 생각하시면 됩니다. 정렬이 되어있는 상태에서 rownum을 활용하는 것이 top-n쿼리이므로 그걸 강조하기 위해 넣은 것이고 테이블 alias또한 지금은 한 테이블만 가지고 작업하므로 없어도 결과가 나오지만 두 개 이상의 테이블을 이용해서 조인하는 경우 테이블명이 없으면 컬럼들을 액세스 할 수 없으므로 테이블 alias를 넣었습니다. 이예제는 둘 다 없어도 실행되는 예제예요
밑은 내가 푼 거
select mon 월, num 입사자
from (select substr(hire_date,4,2) mon, count(employee_id) num
from employees
group by substr(hire_date,4,2)
order by substr(hire_date,4,2) )
where rownum<4;2. 'IT' 부서에 속한 직원들을 대상으로 급여(Salary)가 가장 많은 직원 상위 3명의 이름(first_name), 급여(salary), 부서명(department_name)을 조회하시오.
select first_name, salary, dname
from (select e.*, d.department_name dname
from employees e, departments d
where e.department_id=d.department_id
order by salary desc)
where dname='IT' and rownum<=3;내가푼거
select first_name, salary, department_name
from (select first_name, salary, department_name
from departments d, employees e
where d.department_id=e.department_id
and department_name ='IT'
order by salary desc)
where rownum<4;본 포스팅은 멀티캠퍼스의 멀티잇 백엔드 개발(Java)의 교육을 수강하고 작성되었습니다.
