서브쿼리의 종류는 메인쿼리와의 상호작용 방식에 따라 단일행 서브쿼리(single-row-subquery)와 다중행 서브쿼리()로 나뉜다. 그 외에도 다중열 서브쿼리, from절에서 사용하는 서브쿼리도 있다.
ex) 20번 부서에 속한 사원 중 전체 사원의 평균급여보다 높은 급여를 받는 사원의 정보와 소속부서 조회하기
select e.empno, e.ename, e.job, e.sal, d.deptno, d.dname from emp e, dept d where e.deptno=d.deptno and d.deptno=20 and e.sal > (select avg(sal) from emp);
in
column in (값1, 값2.. ) => or 개념
any
: 서브쿼리가 반환한 값 중 하나라도 참이면 메인쿼리 조건식 true
column < any(값1, 값2..) : 최대값보다 작은 값을 조회
select ename, sal
from emp
where sal <any(800,1250,1600)column > any : 최소값보다 큰 값을 조회
select ename, sal
from emp
where sal > any(900,1250,1600)
all
: 서브쿼리가 반환한 값 모두 참이여야 메인쿼리 조건식 true
column > all : 최대값보다 큰 값을 조회
select ename, sal
from emp
where sal > all(900,1250,1600)column < all : 최소값보다 큰 값을 조회
select ename, sal
from emp
where sal < all(900,1250,1600)
where (컬럼1, 컬럼2…) in (서브쿼리의 실행결과 값1, 값2…)
ex: 각 부서에서 최소급여를 받는 사용자의 사원번호 ,부서번호, 급여를 중복값 없이 출력하기
select empno, deptno, sal from emp where (deptno,sal) in (select deptno, min(sal) from emp group by deptno);
[실행흐름]
1. 메인쿼리에서 참조할 값을 이용해서 서브쿼리를 실행
2. 서브쿼리에서 실행 결과를 이용해서 메인쿼리를 실행
3. 1번과2번을 레코드 갯수 만큼 반복해서 실행
ex) sal이 본인 부서의 평균 급여보다 많은 사원의 사원번호, 사원명, 부서번호, sal을 구할때
select empno, ename, deptno, sal 2 from emp main 3 where sal> (select avg(sal) from emp where deptno = main.deptno); EMPNO ENAME DEPTNO SAL ---------- ---------- ---------- ---------- 7499 ALLEN 30 1600 7566 JONES 20 2975 7698 BLAKE 30 2850 7788 SCOTT 20 3000 7839 KING 10 5000 7902 FORD 20 3000
ex) 사원번호, 사원명, sal, 부서의 평균 급여 출력하기
select empno, ename, sal, (select avg(sal) from emp where deptno=main.deptno) from emp main; EMPNO ENAME SAL (SELECTAVG(SAL)FROMEMPWHEREDEPTNO=MAIN.DEPTNO) ---------- ---------- ---------- ---------------------------------------------- 7369 SMITH 800 2175 7566 JONES 2975 2175 7788 SCOTT 3000 2175 7876 ADAMS 1100 2175 7902 FORD 3000 2175 7499 ALLEN 1600 1575 7521 WARD 1300 1575 7654 MARTIN 1250 1575 7698 BLAKE 2850 1575 7844 TURNER 1500 1575 7900 JAMES 950 1575 EMPNO ENAME SAL (SELECTAVG(SAL)FROMEMPWHEREDEPTNO=MAIN.DEPTNO) ---------- ---------- ---------- ---------------------------------------------- 7782 CLARK 2450 2916.66667 7839 KING 5000 2916.66667 7934 MILLER 1300 2916.66667 7777 지연
- view : 하나 이상의 테이블을 조회하는 select문(실행한 결과)을 저장할 수 있는 객체 => 자주 사용하는 코드에 이름을 주고 저장해놓는 것 !
[생성방법]
create view avgempview 2 as 3 select deptno,avg(sal) avg 4 from emp 5 group by deptno; View created.
[조회하기]
select * from avgempview; DEPTNO AVG ---------- ---------- 20 2175 30 1575 10 2916.66667
전제조건 : (시스템 계정에서) grant create view to scott; - 뷰 권한주기
프롬절에 사용하는 서브쿼리 = 인라인뷰
ex) 사원번호, 사원명, sal, 부서의 평균 급여 출력하기
select e.empno, e.ename, e.deptno, e.sal, a.avgsal from emp e, (select deptno,avg(sal) avgsal from emp group by deptno)a 3 where e.deptno=a.deptno //기본키와 외래키(조인조건) 4 and e.sal>a.avgsal; EMPNO ENAME DEPTNO SAL AVGSAL ---------- ---------- ---------- ---------- ---------- 7499 ALLEN 30 1600 1575 7566 JONES 20 2975 2175 7698 BLAKE 30 2850 1575 7788 SCOTT 20 3000 2175 7839 KING 10 5000 2916.66667 7902 FORD 20 3000 2175
ex) emp테이블 사원 중 급여를 많이 받는 상위 3명의 이름, 급여, 부서번호를 조회
select rownum, ename, sal from (select * from emp order by sal desc) where rownum<4 and sal is not null; ROWNUM ENAME SAL ---------- ---------- ---------- 1 KING 5000 2 SCOTT 3000 3 FORD 3000
더 정확히 랭크를 알기 위해서는 rank() over() 를 쓴다. 👇🏻
select ename, sal, rank() over(order by sal desc) as 순위 from emp where sal is not null; ENAME SAL 순위 ---------- ---------- ---------- KING 5000 1 FORD 3000 2 SCOTT 3000 2 JONES 2975 4 BLAKE 2850 5 CLARK 2450 6 ALLEN 1600 7 TURNER 1500 8 MILLER 1300 9 WARD 1300 9 MARTIN 1250 11 ENAME SAL 순위 ---------- ---------- ---------- ADAMS 1100 12 JAMES 950 13 SMITH 800 14